MySQLi statement execute ArgumentCountError ("number of parameters")

[iaibai]

Description

We've been having an issue where mysqli_stmt::execute is intermittently throwing an ArgumentCountError despite that we have successfully bound the correct number of parameters using bind_param. The error happens intermittently - one particular statement was failing about 50% of the time, a couple of others have failed once in hundreds or thousands of calls.

The following code:

<?php
$stmt = $dbConn->prepare("INSERT INTO `hot_availability`.`unit_availability`
    (`property_unittype_id`,`property_id`,`avail_date`,`num_avail`,
    `creation_time`,`username`,`active`)
    VALUES(?,?,?,?,UNIX_TIMESTAMP(),?,'Y')");
$stmt->bind_param('iisis',$propUnitId,$propertyId,$availDate,$numAvail,$username);
$stmt->execute(); // error here

Resulted in this output:

[2024-03-26 02:25:07] production.ERROR: The number of variables must match the number of parameters in the prepared statement {"exception":"[object] (ArgumentCountError(code: 0): The number of variables must match the number of parameters in the prepared statement at /var/app/current/class/channel/ChannelAvailability.class.php:1430)
[stacktrace]
#0 [internal function]: mysqli_stmt->bind_param()
#1 /var/app/current/class/channel/ChannelAvailability.class.php(1430): mysqli_stmt->execute()
#2 /var/app/current/class/channel/ChannelRateSync.class.php(415): ChannelAvailability::setRatesAndAvailability()
#3 /var/app/current/app/Channel/RateSyncWorker.php(35): ChannelRateSync::runRateSync()
#4 /var/app/current/app/Worker.php(89): App\\Channel\\RateSyncWorker->work()
#5 /var/app/current/auto/channel/rate_sync_worker.php(9): App\\Worker->daemon()
#6 {main}

But I expected the statement to execute correctly and return true. We also have "mysqli_report(MYSQLI_REPORT_OFF);", so I wouldn't have expected execute() to throw an Error.

The prepared statements that have errored have been part of our codebase for years, and thi only started when we moved from PHP 7.4 to PHP 8.2.

We managed to stop the one that was erroring 50% of the time simply by remove a space character that was at the start of the query, which made me think it was maybe some kind of caching? Removing a space character that was at the end of the query however did not help.

Although we use Laravel and its' query builder, a lot of our application is legacy code that uses MySQLi, in object-oriented style. We have not had any issues with the Laravel query builder queries - just MySQLi prepared statements.

I have not been able to find any report of a similar error, aside from people who have genuinely not set the correct number of parameters.

PHP Version

PHP 8.2.15

Operating System

Amazon Linux 2023

[iaibai]

This issue may be related: matomo-org/matomo#19413

[SakiTakamachi]

I tried imagining the missing information and supplementing it with local, but it doesn't reproduce even after 1000 loops.

Can you provide code that allows me to reproduce the problem by simply copying and running the code, including the process of creating a table and preparing dummy values ​​for variables?
Of course, the authentication information can be dummy.

[iaibai]

Thanks, it is proving difficult to replicate as it's very intermittent but I'll give it a go.

It has been happening on multiple different prepared statements and I've yet to see any sort of pattern. Since I made this issue it has happened only once - to a totally different query than the one I posted.

[iaibai]

I'm afraid we've not been able to reliably recreate this so far, but it is still happening on multiple different statements.

We're attempting to test if there's a possibility it happens when the server is under load as there was a bit of a burst of them last night, but so far our load testing hasn't produced any results.

The stack trace suggests the error is coming from a bind_param call inside execute, but we never provide any arguments to the execute() function (because we use bind_param() instead), so I'm not sure why it would ever be doing that.

[SakiTakamachi]

From what I've seen of the implementation, it looks like your code won't cause any errors...

Is the actual production code also written with SQL literals, like in your example?
Also, is the number of variables passed to bind_param fixed like in the code example?