MySQLi statement execute ArgumentCountError ("number of parameters")

[iaibai]

Since upgrading to PHP 8.2, we've been getting frequent "ArgumentCountError: The number of variables must match the number of parameters in the prepared statement" errors when executing mysqli prepared statements. Although there are some really strange circumstances around this error, we can recreate it.

We are binding the correct number of variables using bind_param, but for some reason as you'll see from the stack trace the Error is thrown by execute().

This is preventing us upgrading to PHP 8.2.

Description

This Error is thrown regularly on certain statements (about a dozen) in our codebase, and as you'll see from the recreation steps there are quite a few factors that seem to affect whether a statement will error.

A full stack trace of the error looks like the following:

Fatal error: Uncaught ArgumentCountError: The number of variables must match the number of parameters in the prepared statement in /test.php:29
Stack trace:
#0 [internal function]: mysqli_stmt->bind_param('s', '2017-04-10 00:0...')
#1 /test.php(29): mysqli_stmt->execute()
#2 {main}
  thrown in /test.php on line 29

We first experienced this on Amazon Linux 2023 PHP 8.2 (installed newrelic using the tar archive), but we have recreated this on Amazon Linux 2 PHP 8.1 (installing through rpm), and locally in a docker instance based on php:8.1-cli (again using tar).

We have recreated the Error on 10.16.0.5, 10.19.0.9, and 10.10.0.1.

The errors stop when we set newrelic.enabled = false, or when we remove our license key from newrelic.ini. And of course, the error does not happen when we haven't installed newrelic PHP agent.

Steps to Reproduce

In summary, install PHP >=8.1, with mysqli, enable New Relic and then prepare two statements that have different number of arguments.

Here is our current test script:

<?php 
 
$dbConn = new mysqli( 
    "db", 
    "root", 
    "changeme", 
    "dbtest" 
); 
 
justPrepare($dbConn); 
 
$ts = strtotime("today - 7 years"); 
$stringTs = date('Y-m-d H:i:s', $ts); 
 
$stmt = $dbConn->prepare(" SELECT q.id 
FROM query_test_table q 
CROSS JOIN query_test_table AS q2 
WHERE MD5(`q`.`key`) = MD5(?) 
; 
"); 
if (! $stmt) { 
    throw new Exception("Prepare Failed : " . $dbConn->error); 
} 
$stmt->bind_param('s', $stringTs); 
if (! $stmt->execute()) { 
    throw new Exception("Execute Failed : " . $stmt->error); 
} 
$stmt->bind_result($someId); 
$stmt->fetch(); 
$stmt->close(); 
 
echo "If you see this message, it didn't crash\n"; 
 
function justPrepare($dbConn) 
{ 
    // note: we don't even execute this
    $stmt2 = $dbConn->prepare("SELECT ?, ?"); 
    $stmt2->close(); 
    $stmt2 = null; 
}

Some conditions seem to apply:

• The second statement when executed seems to need to take at least half a second or the Error doesn't happen.
• If you don't call justPrepare() then the Error doesn't happen.
• If you do call justPrepare() but change $stmt2 in it to have the same number of placeholders as $stmt, then the error doesn't happen.
• Minor changes to the query in $stmt sometimes stop the error. For example, removing the space before SELECT, or the semi colon after the query. However, most of the queries that have failed haven't had extra characters like this. This was just the easiest one to recreate.

Locally, we recreated the issue using docker php:8.1-cli image and mysql:8.0.36. We seeded the test table with 2 million rows with the following sql:

CREATE TABLE `query_test_table` (
                                    `id` int NOT NULL AUTO_INCREMENT,
                                    `key` varchar(45) DEFAULT NULL,
                                    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DELIMITER $$
CREATE PROCEDURE InsertRandomRows()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE randomKey VARCHAR(45);

  WHILE i < 2200000 DO
    SET randomKey = SUBSTRING(MD5(RAND()) FROM 1 FOR 45);
INSERT INTO query_test_table (`key`) VALUES (randomKey);
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;

CALL InsertRandomRows();

Expected Behavior

$stmt->execute() should not throw an ArgumentCountError when we have bound the correct number of parameters with bind_param. And it shouldn't be affected by the previously prepared query.

Your Environment

We've recreated this on:

• Amazon Linux 2023, PHP 8.2 (Elastic Beanstalk)
• Amazon Linux 2, PHP 8.1 (Elastic Beanstalk)
• Docker, on php:8.1-cli

This may be related to this change in PHP 8.1, php/php-src#6271

[mfulb]

Thank you for the detailed report - we will investigate this issue.

[mfulb]

Thank you again for the detailed report - I was able to reproduce the behavior you are reporting and will be investigating this further.

[mfulb]

I believe it is related to the agent attempting to "explain" the slower query and the issue reported is triggered. As a temporary workaround you could try setting newrelic.transaction_tracer.explain_enabled=false - this would of course remove the explanation of slow SQL queries but would remove the warnings you are experiencing. See the docs for more info on this setting. Our goal is to remove the need for this workaround with a fix of course.

[iaibai]

Thanks! That's really helpful. As long as we know which queries are slow (and how slow they are) we can probably do without the explain for now.

[mfulb]

Thanks! That's really helpful. As long as we know which queries are slow (and how slow they are) we can probably do without the explain for now.

I believe the duration is still available.

[mfulb]

I made some good progress understanding this issue. You noticed the space at the beginning of the SQL for $stmt might be related - and it is. But also the spaces AFTER the ";". Either of those can cause a case where you will see this warning. If you remove the leading and trailing spaces then your test runs just fine. Just wanted to pass this along if it helped you in the time before we are able to ship a proper solution for this case.

Thank you again for this report - it was well documented and made my job of troubleshooting it much more efficient!

[iaibai]

Thanks, sorry I think we knew removing the semi-colon also helped this particular query, but we were also experiencing the issue on some queries that didn't have spaces at the start or semi-colons at the end (in fact most of them don't). We just didn't manage to make a re-creatable example of one like that.

We couldn't find any clear pattern in the queries that failed. 😔

[mfulb]

Your report with all the details you provided was excellent and extremely useful in determining this pattern.

[curtisblack2004]

I am running into the same issue on PHP 8.1.28, when will the new version of Newrelic be released? I have 10.19.0.9 installed.

[mfulb]

This issue has been fixed in the 10.20.0.10 release. Thank you again for the thorough bug report!

[iaibai]

Thank you!

[mfulb]

I wanted to check back and see if the new release has addressed this report.

[curtisblack2004]

Yes, I am not longer getting the errors I did before. Thank you! Curtis Black Senior Web developer / Team Lead [cid:0e962f48-b401-4b61-a246-a066e0df9a21] Office +1 (385) 209 1900, x1<tel:+13852091900,1> Mobile +1 (702) 715 2202<tel:+17027152202> ***@***.******@***.***> www.descartes.com<https://www.descartes.com/>

…

________________________________ From: Michael Fulbright ***@***.***> Sent: Tuesday, May 14, 2024 1:22 PM To: newrelic/newrelic-php-agent ***@***.***> Cc: curtisblack2004 ***@***.***>; Comment ***@***.***> Subject: Re: [newrelic/newrelic-php-agent] MySQLi statement execute ArgumentCountError ("number of parameters") (Issue #875) WARNING - EXTERNAL EMAIL: Do not click links or open attachments unless you know the content is safe. I wanted to check back and see if the new release has addressed this report. — Reply to this email directly, view it on GitHub<#875 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ACDZWF62VWZ5PN64BZ7RVQLZCJP6VAVCNFSM6AAAAABGAYOG7GVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMJQHE4DGMRVGE>. You are receiving this because you commented.Message ID: ***@***.***>