1

I am updating someone's old old code from using mysql() to using PDO. In one place they have some LOCK TABLES commands to prevent two users from accessing the same data at the same time. When running LOCK TABLES, PDO throws "General Error: 2014 Cannot execute queries while other unbuffered queries are active".

I made some test code to eliminate other variables. The system runs on Ubuntu 18 / PHP 7.2 / MySQL 5.7.27:

try {
    $_DB=new PDO("mysql:host=".DBHOST.";dbname=".DBNAME.";charset=utf8", DBUSER, DBPASS,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::ATTR_TIMEOUT => "5",  PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_DIRECT_QUERY=>false,PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>true ));      

    // This gives the 2014 error. Any queries following this get the 2014 error.
    // Any queries before this work fine.
    $_DB->query("LOCK TABLES Inspections WRITE");

    $_DB->query("UNLOCK TABLES");
}
catch (Exception $e) {
    logError("Error : ".$e->getMessage());           
    // per @dharman's comment 
    //exit();
    throw($e);
}

I also tried adding a $_DB->beginTransaction before the LOCK TABLES and $_DB->commit after the UNLOCK TABLES, but still I get the same error.

I have tried various combination of ATTR_EMULATE_PREPARES and MYSQL_ATTR_USE_BUFFERED_QUERY, but nothing seems to make any difference.

Ben Holness
  • 2,457
  • 3
  • 28
  • 49
  • Please do not catch the exception just to print the error message. Either leave the exceptions alone or rethrow it if you need. – Dharman Aug 17 '19 at 17:11
  • Thanks @Dharman - good to know. – Ben Holness Aug 17 '19 at 17:19
  • 1
    It would be good to know why does your code require the lock? There is probably a way to do the same without the lock. – Dharman Aug 17 '19 at 17:20
  • I think it is used to ensure that if two people try to edit an Inspection at the same time, there won't be a race condition between the checking to see if someone is editing it and updating to say that I am editing it. – Ben Holness Aug 17 '19 at 17:24

1 Answers1

1

You should use exec() instead query(). exec() does not expect any return values, which is exactly what LOCK TABLES needs.

$pdo->exec("LOCK TABLES Inspections WRITE");
Dharman
  • 30,962
  • 25
  • 85
  • 135