Mysql 5.7. GET_LOCK no longer works

After upgrading to mysql 5.7 it GET_LOCK

stopped working as it was used in mysql 5.5 and as I expected to work. I am aware of the changes GET_LOCK

in 5.7. as described here .

When I execute the same script from the cmd line twice - with a little pause in between - it works as expected: the first one gets the lock and the second one doesn't.

When I execute the same php script through the browser twice - with a little pause in between - both return that they have successfully acquired the lock. This result is not what I expected and is different from 5.5 and my understanding GET_LOCK

as described in 5.7 documentation.

• PHP works as a module (phpinfo () shows Server API: Apache 2.0 Handler).
• PHP version: 7.0.20
• Mysql version: 5.7.18 OS
• CentOS 7.

This is an example script locktest.php

:

<?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            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::ATTR_PERSISTENT => false
];
$pdo = new PDO($dsn, $user, $pass, $opt);

echo "pid=(".getmypid().")\n";

$stmt = $pdo->query('SELECT connection_id() as connid');
$row = $stmt->fetch();

echo "mysql connection id =(".$row['connid'].")\n";


$stmt = $pdo->query('SELECT GET_LOCK("foobar", 2)');
$row = $stmt->fetch();


var_dump($row);
echo "\n\n";
sleep(10);

      

        
        
        
      

    

When this script is run from the cmd line - I get what I expect: Run php -q locktest.php from one terminal. Then immediately after another terminal.

The first one returns:

pid=(18378)
mysql connection id =(71)
array(1) {
  ["GET_LOCK("foobar", 2)"]=>
  int(1)
}

      

        
        
        
      

    

(note the result GET_LOCK

is 1)

The second one will be returned (started while the first one is still running):

pid=(18393)
mysql connection id =(73)
array(1) {
  ["GET_LOCK("foobar", 2)"]=>
  int(0)
}

      

        
        
        
      

    

(note that the result GET_LOCK

is 0 - as expected and a different pid and mysql connection id).

When the same script is run twice from the browser, it reports that both scripts successfully acquired the lock. First it returns:

pid = (11913) mysql connection id = (74) array (1) {["GET_LOCK (" foobar ", 2)"] => int (1)} 

The second comes back (while the first is still running):

pid = (11913) mysql connection id = (75) array (1) {["GET_LOCK (" foobar ", 2)"] => int (1)} 

Note that the pids are the same, but the mysql connection id is different and the result is GET_LOCK

not as expected as both are returned.

Now I am confused. Different mysql connections are used (as returned by CONNECTION_ID () ) and this assumes different mysql sessions. And according to the mysql documentation, it is possible to get more locks with the same name from the SAME session, but here I have different mysql sessions, right?

I even put PDO::ATTR_PERSISTENT => false

though this is the default.

The only difference between the output from the cmd line and the browser is the pids (different pids from the two executable php scripts from the cmd line and the same pids from the two executed php scripts from the browser).

Any thoughts on what's going on? At the moment it seems to me that this is a serious problem as the blocking stopped working. :(

Thank.