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?

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.