How to put an execution time limit for MySQL Temporary table creation

Question

We can use SET MAX_EXECUTION_TIME=10; statement to stop the execution of a SELECT statement, if it took more than 10 milliseconds to execute the query. But the same wont work for other statements like CREATE TEMPORARY TABLE and INSERT etc.

Reference: https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-execution-time

Currently my temporary table creation takes more than 50s to complete. I want to put a limit like 10s. Is it possible in MySQL?

warashi nguyen · Answer 1 · 2023-07-21T04:38:03.653

<?php
// Set the maximum execution time for the script to 10 seconds
set_time_limit(10);

try {
    // Connect to the MySQL server
    $pdo = new PDO('mysql:host=localhost;dbname=your_database', 'username', 'password');

    // Execute the temporary table creation statement
    $pdo->exec('CREATE TEMPORARY TABLE your_temp_table (...)');

    // Continue with the rest of your code

} catch (PDOException $e) {
    if ($e->getCode() == 2006) {
        // Handle the timeout error here
        echo "Timeout: Temporary table creation took too long.";
    } else {
        // Handle other PDO exceptions
        echo "Error: " . $e->getMessage();
    }
}
?>

set_time_limit(10);: This sets the maximum execution time for the PHP script to 10 seconds. This means that the entire script, including any queries or operations it performs, will be terminated if it exceeds this time limit.

The code uses PDO (PHP Data Objects) to connect to the MySQL server. The connection details are provided in the connection string: 'mysql:host=localhost;dbname=your_database', 'username', 'password'. Replace 'your_database', 'username', and 'password' with the appropriate values.

Inside the try block, the code attempts to execute the temporary table creation statement using $pdo->exec('CREATE TEMPORARY TABLE your_temp_table (...)');.

If the temporary table creation takes longer than the specified time limit (in this case, 10 seconds), MySQL may close the connection, resulting in a PDOException with error code 2006 (server has gone away).

The catch block checks if the caught PDOException has an error code of 2006, indicating a timeout. If it does, it prints the message "Timeout: Temporary table creation took too long.".

If the PDOException has a different error code, the code can handle other PDO exceptions appropriately.

How to put an execution time limit for MySQL Temporary table creation

1 Answers1