Catch and handle timeout error in MySQL and PHP

Question

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?

Answer 1

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 exception thrown by mysqli, and then check $e->getCode() 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

try {
    $results = $conn->query("SELECT COUNT(1) FROM dual WHERE SLEEP(2) = 0");
    print_r($results->fetch_all());
} catch (mysqli_sql_exception $e) {
    if ($e->getCode() == 3024) {
        echo "query timed out"; // here is your handling code
    } else {
        throw $e; // Other error
    }
}

Output:

query timed out

Notice that since PHP 8.1 mysqli throws exceptions by default, which makes your code clear and consistent. In case your PHP version is lower, you have to add the error mode explicitly:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect("localhost", "user", "pass", "db");
...