php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #79375 mysqli_store_result does not report error from lock wait timeout
Submitted: 2020-03-12 21:07 UTC Modified: 2020-03-12 21:11 UTC
From: brett dot cundal at iugome dot com Assigned:
Status: Closed Package: MySQLi related
PHP Version: 7.2.28 OS: Ubuntu 18.04
Private report: No CVE-ID: None
 [2020-03-12 21:07 UTC] 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


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-03-12 21:11 UTC] brett dot cundal at iugome dot com
-Summary: mysqli_store_result does not report error from lock wait timout +Summary: mysqli_store_result does not report error from lock wait timeout
 [2020-03-12 21:11 UTC] brett dot cundal at iugome dot com
fixing typo in summary
 [2020-10-28 10:04 UTC] nikic@php.net
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
 [2020-10-28 10:04 UTC] nikic@php.net
-Status: Open +Status: Closed
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Oct 15 09:01:26 2024 UTC