We are currently using a maximum execution time query hint (
/*+ MAX_EXECUTION_TIME(1000) */) to limit the execution time of some queries.
It turned out that this query hint does not work as expected when using MySQLI. The test for this currently always fails with MySQLI. Using PDO/MySQL it works correctly.
This seems to be a general issue with prepared statements and mysqli. Tried to test that directly by using the native mysqli methods, which Zend Framework should also do somewhere in the code.
$mysqli = new mysqli("localhost", "matomo", "matomo", "matomo"); $mysqli->query("SELECT /*+ MAX_EXECUTION_TIME(1) */ SLEEP(5) FROM matomo_log_visit");
This results as expected in
PHP Warning: mysqli::query(): (HY000/3024): Query execution was interrupted, maximum statement execution time exceeded in /srv/matomo/mysqli.php on line 6
$mysqli = new mysqli("localhost", "matomo", "matomo", "matomo"); $stmt = $mysqli->prepare("SELECT /*+ MAX_EXECUTION_TIME(1) */ SLEEP(5) FROM matomo_log_visit"); $stmt->execute();
This runs through without any issues. Fetching the result of the query returns
1, which is the result of the SLEEP method. So it seems the query hint is ignored.
Running a prepared statement directly in mysql console like this:
PREPARE stmt FROM 'SELECT /*+ MAX_EXECUTION_TIME(1) */ SLEEP(5) FROM matomo_log_visit'; EXECUTE stmt;
SQL ERROR (3024): Query execution was interrupted, maximum statement execution time exceeded
So query hints in prepared statements generally should work.
Guess query hints is something that can't work with the Zend MYSQLI adapter as it seems to use prepared statements always.
We need to decide how to go on with this. Tried to find (bug) reports or questions on that topic, but wasn't able to find anything. Not sure if that is a bug in the MYSQLI extension for PHP or something else.
For now as a first step lets change the global.ini.php documentation for settings that add these hints and mention they won't work with Mysqli:
Then we create an FAQ say "How do I limit the max execution time for MySQL queries". There we could mention that this won't work for Mysqli.
In this FAQ we could additionally mention the config
[CustomReports]custom_reports_max_execution_time which is not in the
global.ini.php. This config allows you to limit the amount of time an archiving query from the CustomReports plugin can take.
Once this is done we remove the "Help wanted" label and but it into "Backlog" as it doesn't have too high of a priority as these features are rarely used/needed.
Since it probably never worked I reckon it might not be a regression?
There is a connection level option to a set read query timeout for MYSQLI
$mysqli = mysqli_init(); $mysqli->options(MYSQLI_OPT_READ_TIMEOUT, 1); $mysqli->real_connect("matomo", "matomo", "matomo", "matomo"); $stmt = $mysqli->prepare("SELECT SLEEP(5) FROM matomo_log_visit"); $result = $stmt->execute(); // Will abort after 1 second
Not sure if that helps at all.
@bx80, thanks for the hint. Might be something we will have a look into, once we start working on this issue.
Although the MYSQLI_OPT_READ_TIMEOUT connection option works in individual tests, it is per connection, not per query. So unless the max execution time was applied to all queries (which would likely cause problems) then using it would mean reconnecting and disconnecting for each query which requires a timeout, which doesn't seem like a viable option.
I found "SET STATEMENT max_statement_time=1 FOR [query]" which works nicely for both MySQLi and PDO/MySQL adaptors, but unfortunately this only appears to be supported by MariaDB.
As a partial solution, we could detect the database engine (or have a config hint: max_execution_method = mariadb ?) then DbHelper::addMaxExecutionTimeHintToQuery() could use the SET STATEMENT... option instead of the MAX_EXECUTION_TIME hint when the adaptor is MySQLi and the database is MariaDB. This would leave just the MySQLi + MySQL DB combination without a working max execution time.
Thanks for this @bx80 I think in that case we will want to maybe fall back to the initial idea of documenting this in an FAQ and also adjusting the
global.ini.php to mention eg for the
archiving_query_max_execution_time setting that it won't work when using Mysqli.
I've added a FAQ for review 'How can I automatically stop long running database queries?' which explains the two options and notes that they will not work if using the MySQLi extension.