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?

That if statement to wrap your query is called try..catch. That's how in the modern programming you are handling an expected error. — Your Common Sense
– Your Common Sense, Commented Jun 14, 2022 at 2:29
Timing out the SELECT COUNT(1) query deliberately. So that when user searches for something vague, which would return millions of results, they are told to be more specific after five seconds, rather than left waiting indefinitely. In production it will be 5000 milliseconds. — Ned Hulton
– Ned Hulton, Commented Jun 14, 2022 at 15:54
So "carry on trucking" doesn't actually mean to "carry on" but rather to abort - that's what I thought. By the way, limiting the time is quite peculiar method. As a rule, a search query is just gets optimized to be running fast, no matter what is searched for. — Your Common Sense
– Your Common Sense, Commented Jun 14, 2022 at 16:13
"too slow" means echo something to the user like "be more specific", and stop the script, die(), Whereas "keep on trucking" means execute the rest of the script and show the results. Definitely not a great solution, but I have six million rows of text data and can't get it to run quickly, so need to resort to these partial solutions. Here are some of my lamentations: stackoverflow.com/questions/72597151/… stackoverflow.com/questions/71774211/… — Ned Hulton
– Ned Hulton, Commented Jun 14, 2022 at 17:34

Dharman · Accepted Answer · 2022-06-14 10:54:55Z

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");
...

Collectives™ on Stack Overflow

Catch and handle timeout error in MySQL and PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related