title: mysql 5.7. GET_LOCK not working any more
body: <p>After upgrading to mysql 5.7 <code>GET_LOCK</code> stopped working as it used on mysql 5.5 and as I expected to work. I am aware of the changes regarding the <code>GET_LOCK</code> in 5.7. as described <a href="https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock" rel="noreferrer">here</a>.</p>
  
  <p>When I execute the same script from the cmd line twice – with small pause in between –  it works as expected: First one acquires the lock and second one doesn't.</p>
  
  <p>When I execute same php script via browser twice – with small pause in between – both return that they successfully acquired the lock. This result is not what I expected and it is different from version 5.5 and from my understanding of the <code>GET_LOCK</code> as described in 5.7 documentation.</p>
  
  <ul>
  <li>PHP is running as module (phpinfo() shows Server API: Apache 2.0
  Handler ). </li>
  <li>PHP version is: 7.0.20 </li>
  <li>Mysql version is: 5.7.18 OS is</li>
  <li>CentOS 7.</li>
  </ul>
  
  <p>This is the example script <code>locktest.php</code>:</p>
  
  <pre><code>&lt;?php
  
  
  $host = 'localhost';
  $db   = 'enter_your_db';
  $user = 'enter_your_username';
  $pass = 'enter_your_password';
  $charset = 'utf8mb4';
  
  
  $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
  $opt = [
      PDO::ATTR_ERRMODE            =&gt; PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE =&gt; PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES   =&gt; false,
      PDO::ATTR_PERSISTENT =&gt; false
  ];
  $pdo = new PDO($dsn, $user, $pass, $opt);
  
  echo "pid=(".getmypid().")\n";
  
  $stmt = $pdo-&gt;query('SELECT connection_id() as connid');
  $row = $stmt-&gt;fetch();
  
  echo "mysql connection id =(".$row['connid'].")\n";
  
  
  $stmt = $pdo-&gt;query('SELECT GET_LOCK("foobar", 2)');
  $row = $stmt-&gt;fetch();
  
  
  var_dump($row);
  echo "\n\n";
  sleep(10);
  </code></pre>
  
  <p>When this script is run from cmd line – I get what I expect:
  Run php -q locktest.php from one terminal. Then immediately after from another terminal window.</p>
  
  <p>The first one will return:</p>
  
  <pre><code>pid=(18378)
  mysql connection id =(71)
  array(1) {
    ["GET_LOCK("foobar", 2)"]=&gt;
    int(1)
  }
  </code></pre>
  
  <p>(please note that <code>GET_LOCK</code> result is 1)</p>
  
  <p>The second one will return (started while the first one was still running):</p>
  
  <pre><code>pid=(18393)
  mysql connection id =(73)
  array(1) {
    ["GET_LOCK("foobar", 2)"]=&gt;
    int(0)
  }
  </code></pre>
  
  <p>(please note that <code>GET_LOCK</code> result is 0 – as expected, and different pid and mysql connection id).</p>
  
  <p>When the same script is started twice from the browser – it reports that both scripts successfully obtained the lock.
  First returns:</p>
  
  <pre>
  pid=(11913) mysql connection id =(74) array(1) { ["GET_LOCK("foobar", 2)"]=> int(1) } 
  </pre>
  
  <p>Second returns (while first one is still running):</p>
  
  <pre>
  pid=(11913) mysql connection id =(75) array(1) { ["GET_LOCK("foobar", 2)"]=> int(1) } 
  </pre>
  
  <p>Please note that pids are the same, but mysql connection id is different, and <code>GET_LOCK</code> result is not as expected since both returned 1.</p>
  
  <p>Now I am confused. Different mysql connections (as returned by <a href="https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_connection-id" rel="noreferrer">CONNECTION_ID()</a>) is used and this suggests different mysql sessions. And according to the mysql documentation it is possible to obtain more locks with same name from <em>SAME</em> session, but here I have different mysql sessions, right?</p>
  
  <p>I even put <code>PDO::ATTR_PERSISTENT =&gt; false</code> although that is a default value.</p>
  
  <p>The only difference between output  from cmd line and browser are the pids (Different pids from two executed php scripts from cmd line, and same pids from two executed php scripts from the browser).</p>
  
  <p>Any thoughts what is happening? For now, to me, it seems like a major issue since locking quietly stopped working. :(</p>
  
  <p>Thanks.</p>