MySQL PDO: `PDOStatement::execute` fails and returns `false` without neither throwing an exception nor setting `PDOStatement::errorCode` if many (~1000) query parameters are bound

[nagmat84]

Summary

If a query binds many (i.e. around 1000) parameters, the method PDOStatement::execute() fails and returns false. Even though PDO::ERRMODE_EXCEPTION is set as part of the PDO options no exception is thrown. PDOStatement::execute() neither sets PDOStatement::errorCode(). However, a subsequent fetchAll returns an empty array with zero query results.

Note, that the bug can be worked around, if the binding are resolved manually. This mean if all placeholders '?' inside the query string are replaced by their actual value before the query is passed to PDOStatement::prepare(), then PDOStatement::execute() does not fail and a subsequent PDOStatement::fetchAll() returns the expected number of results.

The issue has been reported by a couple of our users of https://github.com/LycheeOrg/Lychee. We have tracked it down to the PDO layer, i.e. it is not a bug in our code nor in the Laravel framework. A MWE which triggers the bug is located in https://github.com/LycheeOrg/MySQL1000Bug. The bug only occurs for a couple of users not everyone. But if the bug occurs for a specific setup, then the bug occurs deterministically. So far, we have seen bug reports from users with PHP 8.1.2 and 8.1.5 – 8.1.7. We haven't seen any bug reports for 8.0.x, but this doesn't mean that the bug might not exist there as well. Among the core developer team @qwerty287 has been able to reproduce the faulty behaviour on his development machine.

As the bug only occurs for many query parameters and only for specific setups it smells like a memory corruption bug in the MySQL PDO code which is responsible for parameter binding.

Steps to reproduce the problem

1. Checkout the repository https://github.com/LycheeOrg/MySQL1000Bug
2. Create a new MySQL/Maria DB
3. Load the included MySQL dump into the DB.
4. Make a copy config.php of the provided config.php.example and adopt it to your DB settings.
5. Run the test script ./mysql_1000_bug.

Output if the bug occurs

Test #1 - Query without bindings
bind_values()       : bound 0 values in total
select_query()      : $statement->fetchAll returned 7380 results
check_db_result()   : Everything seems fine :-(

Test #2 - Query with bindings
bind_values()       : binding value __OcYDO3i7Bm9MaCx5SWJj_D as string to placeholder 1 succeeded
...
bind_values()       : binding value Zzik4C_-V-WhyyYcn2QLlaEG as string to placeholder 1074 succeeded
bind_values()       : bound 1074 values in total
select_query()      : $statement->execute() failed without exception, oh, oh
select_query()      : $statement->execute() returned error: 00000
select_query()      : $statement->execute() returned error info:
array(3) {
  [0]=>
  string(5) "00000"
  [1]=>
  NULL
  [2]=>
  NULL
}
select_query()      : $statement->fetchAll returned 0 results
check_db_result     : fetched wrong number of size variants; got 0, expected 7380

Expected output if the bug does not occur

Test #1 - Query without bindings
bind_values()       : bound 0 values in total
select_query()      : $statement->fetchAll returned 7380 results
check_db_result()   : Everything seems fine :-(

Test #2 - Query with bindings
bind_values()       : binding value __OcYDO3i7Bm9MaCx5SWJj_D as string to placeholder 1 succeeded
...
bind_values()       : binding value Zzik4C_-V-WhyyYcn2QLlaEG as string to placeholder 1074 succeeded
bind_values()       : bound 1074 values in total
select_query()      : $statement->fetchAll returned 7380 results
check_db_result()   : Everything seems fine :-(

Further information

PHP version for which the bug has been observed:	8.1.2 and 8.1.5 – 8.1.7
Downstream issue:	LycheeOrg/Lychee#1326
MWE repo:	https://github.com/LycheeOrg/MySQL1000Bug

[cmb69]

I wonder if that is related to emulated prepares (PDO::ATTR_EMULATE_PREPARES).

[cmb69]

I have not been able to reproduce the issue, but finally found https://bugs.launchpad.net/ubuntu/+source/mariadb-10.3/+bug/1964622.

cc @craigfrancis

[craigfrancis]

As a temporary work around, I found this worked:

SET in_predicate_conversion_threshold = 5000;

As a slightly better work-around, while still not ideal, I'm updating my queries to work in 1000 record chunks:

foreach (array_chunk($ids, 1000) as $ids_chunk) {
}

---

Running your mysql_1000_bug.php script, I assume it's been updated because I got the error with Test 3:

# php mysql_1000_bug.php 

print_diagnostic()  : PDO driver name: mysql
print_diagnostic()  : PDO error mode: exception
print_diagnostic()  : PDO buffer size: undefined
print_diagnostic()  : PDO direct query: 0
print_diagnostic()  : PDO network compression: undefined
print_diagnostic()  : PDO multi statements: undefined


Test #1 - Query without bindings
bind_values()       : bound 0 values in total
select_query()      : $statement->fetchAll returned 7380 results
check_db_result()   : Everything seems fine :-(


Test #2 - Query with bindings and ATTR_EMULATE_PREPARES == true
bind_values()       : binding value __OcYDO3i7Bm9MaCx5SWJj_D as string to placeholder 1 succeeded
...
bind_values()       : binding value Zzik4C_-V-WhyyYcn2QLlaEG as string to placeholder 1074 succeeded
bind_values()       : bound 1074 values in total
select_query()      : $statement->fetchAll returned 7380 results
check_db_result()   : Everything seems fine :-(


Test #3 - Query with bindings and ATTR_EMULATE_PREPARES == false
bind_values()       : binding value __OcYDO3i7Bm9MaCx5SWJj_D as string to placeholder 1 succeeded
...
bind_values()       : binding value Zzik4C_-V-WhyyYcn2QLlaEG as string to placeholder 1074 succeeded
bind_values()       : bound 1074 values in total
select_query()      : $statement->execute() failed without exception, oh, oh
select_query()      : $statement->execute() returned error: 00000
select_query()      : $statement->execute() returned error info:
array(3) {
  [0]=>
  string(5) "00000"
  [1]=>
  NULL
  [2]=>
  NULL
}
select_query()      : $statement->fetchAll returned 0 results
check_db_result     : fetched wrong number of size variants; got 0, expected 7380

---

In my case, I experienced something similar, but it's with mysqli (rather than PDO), where I got true returned from $statement->execute() (implying it was successful), but a "Commands out of sync" exception thrown by $statement->get_result()... unfortunately I've not had the time to dig any further into the issue yet.

I got this is on MariaDB 10.7.3 (installed via homebrew on MacOS 12.4); and MariaDB 10.3.34 (on Ubuntu 20.04).

And the test script I'm using, queries a simple user table, which only contains 1 record...

<?php

$count = 999;
$count = 1000;

$mysqli = new mysqli('hostname', 'username', 'password', 'database');

$mysqli->query('SET in_predicate_conversion_threshold = 1000'); // Not needed, it defaults to 1000.

$params = implode(',', array_fill(0, $count, '?'));
$values = range(1, $count);

$sql = 'SELECT * FROM user WHERE id IN (' . $params . ')';

$statement = $mysqli->prepare($sql);
$statement->bind_param(str_repeat('i', $count), ...$values);
$statement->execute();
$result = $statement->get_result();
if ($row = $result->fetch_assoc()) {
  print_r($row);
}

?>

---

As an aside, and I don't think it's relevant in your case... when using $statement->bind_param(), my example worked if the $types parameter was set to 's' (I normally use 'i' because I have an array of integers).

But in your example script, while you have is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR, you're only testing with string values.

This suggestion was from Kamil Tekiela, on the 23rd Mar:

The only difference I can see is that [it] binds values as integers instead of strings. MariaDB optimizer might be choking on this depending on the actual SQL query and its data types. Try binding using strings and see if that helps.

[nagmat84]

As a temporary work around, I found this worked:

SET in_predicate_conversion_threshold = 5000;

I will try that and see whether our users who reported the bug downstream are happy with this. What are the drawbacks of using a high value?

As a slightly better work-around, while still not ideal, I'm updating my queries to work in 1000 record chunks:

foreach (array_chunk($ids, 1000) as $ids_chunk) {
}

That is unfortunately nothing we (LycheeOrg) can do. The query is deep inside the Laravel framework which we cannot influence easily. Such a chunking would have to be implemented by Laravel. The provided test script does not contain code which can be directly found in our project. The test script is the stripped down result of a lot of step debugging through our code and the Laravel framework in order to figure out where the bug originates.

Running your mysql_1000_bug.php script, I assume it's been updated because I got the error with Test 3:

Yes correct, I duplicated the 2nd test because @cmb69 raised the idea that the bug might be related to PDO::ATTR_EMULATE_PREPARES. Hence the former 2nd test now is executed once with PDO::ATTR_EMULATE_PREPARES explicitly set to true (new 2nd test) and to false (new 3rd test).

Interestingly, the bug does not appear if PDO::ATTR_EMULATE_PREPARES is set to true. So this might be another work-around. What do you suggest to be the better work-around?

• Setting PDO::ATTR_EMULATE_PREPARES to true
• Increasing in_predicate_conversion_threshold (or setting it to 0 which means infinite)

As an aside, and I don't think it's relevant in your case... when using $statement->bind_param(), my example worked if the $types parameter was set to 's' (I normally use 'i' because I have an array of integers).

But in your example script, while you have is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR, you're only testing with string values.

This suggestion was from Kamil Tekiela, on the 23rd Mar:

The only difference I can see is that [it] binds values as integers instead of strings. MariaDB optimizer might be choking on this depending on the actual SQL query and its data types. Try binding using strings and see if that helps.

So this is not helping us, because we are already using strings.

[cmb69]

Isn't this issue https://jira.mariadb.org/browse/MDEV-27937? That ticket is linked from the ticket @craigfrancis filed, and it states that it is fixed in MariaDB 10.3.35 and 10.7.4 (but Craig reproduced the problem with 10.3.34 and 10.7.3). That ticket also states that the last known good version is 10.5.13, which is incidentially the version I tested with (and where I couldn't reproduce). And being an upstream bug which has been fixed in the meantime would also explain why @ildyria could no longer reproduce the problem after dist-upgrade. And the fact that there are no issues with emulated prepares also hint at that (because emulated prepares do not send any placeholders/values to the server at all).

So, if this is caused by an upstream bug which already has been fixed, I don't think it makes much sense to work around the issue (regardless of how); instead users should update to a MariaDB version which is no longer affected.

[craigfrancis]

Thank you @cmb69

I hadn't realised it had been patched... just running a quick Homebrew update, I'm now on 10.8.3-MariaDB, and that fixed the issue (both mine and the mysql_1000_bug.php work correctly now).

[cmb69]

That are good news. Thanks for checking!