Bug #79375 [Opn->Csd]: mysqli_store_result does not report error from lock wait timeout

Edit report at https://bugs.php.net/bug.php?id=79375&edit=1 ID: 79375 Updated by: nikic@php.net Reported by: brett dot cundal at iugome dot com Summary: mysqli_store_result does not report error from lock wait timeout -Status: Open +Status: Closed Type: Bug Package: MySQLi related Operating System: Ubuntu 18.04 PHP Version: 7.2.28 Block user comment: N Private report: N New Comment: Automatic comment on behalf of tekiela246@gmail.com Revision: http://git.php.net/?p=php-src.git;a=commit;h=b03776adb5bbb9b54731a44377632fcc94a59d2f Log: Fix bug #79375 Previous Comments: ------------------------------------------------------------------------ [2020-03-12 21:11:18] brett dot cundal at iugome dot com fixing typo in summary ------------------------------------------------------------------------ [2020-03-12 21:07:49] brett dot cundal at iugome dot com Description: ------------ We've run across a case where mysqli_store_result() returns an empty result set when it should set the error code and message. In this case it's happening with a prepared and executed statement which is trying to read a row/gap locked by another connection. Checking the return code from store_result() does not indicate the error, and I'm not able to find any way to get the error to be indicated. Doing the same flow using mysqli_get_result() does result in the error being reported correctly. Note that I'm actually testing with PHP 7.2.24 which is not the latest at this time (it's the latest on ubuntu 18.04), but I've checked the change log for the few versions since this, and I don't see anything likely to fix this between 7.2.24 and 7.2.28. Possibly the issue is more broad, e.g. any time the error would be returned by store_result() rather than immediately by execute() - but we haven't found any other confirmed cases where this happens. This seems similar to https://bugs.php.net/bug.php?id=66370 but I ran that script on php 7.2 and was not able to reproduce that problem using query(), store_result() or get_result(). The test case is as follows. Set up a table with a composite key: CREATE TABLE lock_test ( first int(10) unsigned NOT NULL, second int(10) unsigned NOT NULL, third int(10) unsigned NOT NULL, status int(10) unsigned NOT NULL, PRIMARY KEY (first,second,third) ) ENGINE=InnoDB; Insert a record: INSERT INTO lock_test (first, second, third, status) VALUES (1, 1, 1, 1); On one connection, select the record using part of the key, with "FOR UPDATE": (we didn't see this problem when specifying the full key and therefore locking only the one record) $mysqli->query("START TRANSACTION"); $query = "SELECT status FROM lock_test WHERE first = 1 AND second = 1 FOR UPDATE"; $stmt = $mysqli->prepare($query); $stmt->execute(); if(!$stmt->store_result()) throw new Exception("Store failed: {$mysqli->error}"); echo "Got {$stmt->num_rows} for $name\n"; This will successfully return one record. Leave that open, and do the same on another connection. The second connection will block until 'innodb_lock_wait_timeout' elapses, and then will successfully return with 0 records in the result set. Instead, it should return an error, either on execute() or on store_result(), and should set the mysqli error code. The attached script assumes the table described above is set up on localhost in a schema named 'test'. Adjust as needed otherwise. Test script: --------------- $firstConnection = getConnection(); selectForUpdateStore($firstConnection, 'first connection'); $secondConnection = getConnection(); selectForUpdateStore($secondConnection, 'second connection'); function getConnection() { $mysqli = mysqli_init(); $mysqli->real_connect('localhost', 'root', '', 'test'); return $mysqli; } function selectForUpdateStore(mysqli $mysqli, string $name) { $mysqli->query("SET innodb_lock_wait_timeout = 2"); $mysqli->query("START TRANSACTION"); $query = "SELECT status FROM lock_test WHERE first = 1 AND second = 1 FOR UPDATE"; echo "Running query on $name\n"; $stmt = $mysqli->prepare($query); $stmt->execute(); if(!$stmt->store_result()) throw new Exception("Store failed for $name: {$mysqli->error}"); echo "Got {$stmt->num_rows} for $name\n"; } Expected result: ---------------- Running query on first connection Got 1 for first connection Running query on second connection PHP Fatal error: Uncaught Exception: Store failed for second connection: Lock wait timeout exceeded; try restarting transaction [...] Actual result: -------------- Running query on first connection Got 1 for first connection Running query on second connection Got 0 for second connection ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=79375&edit=1