@sgiehl opened this Issue on August 10th 2021 Member

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 Query

$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 Prepared Statement

$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.

MYSQL Prepared Statement

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;

results in SQL ERROR (3024): Query execution was interrupted, maximum statement execution time exceeded

So query hints in prepared statements generally should work.

Conclusion

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.

@tsteur commented on August 10th 2021 Member

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:

  • live_query_max_execution_time
  • archiving_query_max_execution_time

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?

@bx80 commented on August 12th 2021 Contributor

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.

@sgiehl commented on August 16th 2021 Member

@bx80, thanks for the hint. Might be something we will have a look into, once we start working on this issue.

@bx80 commented on September 15th 2021 Contributor

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.

@tsteur commented on September 15th 2021 Member

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.

@bx80 commented on September 16th 2021 Contributor

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.

This Issue was closed on September 17th 2021
Powered by GitHub Issue Mirror