Catch and handle timeout error in MySQL and PHP-CodePudding

Looking to catch and handle a MySQL timeout error. Existing code:

$conn = mysqli_connect("localhost", "user", "pass", "db");

$conn->query('SET SESSION MAX_EXECUTION_TIME = 10'); // time in milliseconds

$sql = "SELECT COUNT(1) FROM table WHERE..."

$results = $conn->query($sql);

Something like the following?

if ($results == false){    
if ($conn->errno == 3024){        
echo "too slow";    
}else{
echo "unkown error";
}else{ \\carry on trucking

How do I wrap the $conn->query($sql); in an IF statement and still access the $results variable?

CodePudding user response：

According to https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-execution-time, the max execution time is in milliseconds, not seconds.

If the query times out, it returns an error:

mysql> select /*  MAX_EXECUTION_TIME(1000) */ * from bugs where sleep(1)=0;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

You have to check the result of your call to query(). It should return false to PHP if there was an error, then you check $conn->errno to see what error it was. That should be 3024.

Re your comment:

Here's a test that I got to work with PHP 8.1.6.

<?php

$conn = mysqli_connect("localhost", "root", "...", "test2");

Notice the order of arguments is different than what your example shows. Refer to the documentation.

$conn->query('SET SESSION MAX_EXECUTION_TIME = 1000'); // time in milliseconds

$sql = "SELECT COUNT(1) FROM mytable WHERE SLEEP(1) = 0";

$results = $conn->query($sql);
if ($results == false) {
  if ($conn->errno == 3024) {
    die("query timed out");
  }
  die("Other error: $conn->errno");
}

// no error, so it's safe to fetch results now.
print_r($results->fetchall());

Output:

Fatal error: Uncaught mysqli_sql_exception: Query execution was interrupted, maximum statement execution time exceeded in p.php:9

die() isn't the right way to handle errors in production code, but for this test, it's sufficient.

Note that if your query returns an error, then the query returns no results. A query that times out does not hurry up and produce the results faster. It aborts, and returns no results.