Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Getting PHP Warning: Error while sending QUERY packet for long running script

I have a script which runs for long hours say like 10-15 hours.
The script performs well thorughout the process but there is a mysql table creation query in the script where PHP throws warning "PHP Warning: Error while sending QUERY packet…". and the script terminates. Exception handling is also implemented but the script terminates before it catches any exception.

Important to note that the script runs fine and executes everything successfully if the loop completes in a in short time.

Here is a code demo:

<?php
ini_set('max_execution_time', 0);
ini_set('memory_limit', -1);
ini_set('max_input_vars', 10000);

try{
    //***** ****/
    //loop running and doing some long resource intensive operation involving php and mysql
    //***** ****/

    $tableCreationQuery = "CREATE TABLE `some_table` ( 
        id INT NOT NULL AUTO_INCREMENT,
        some_id INT NULL DEFAULT NULL,
        some_text TEXT DEFAULT NULL,
        PRIMARY KEY (id),
        INDEX (some_id),
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8";
    //script executes till here properly
    //PHP notice is thrown on the below line and the script execution terminates
    $tableCreationQueryResult = mysqli_query($db, $tableCreationQuery);
    if (!$tableCreationQueryResult) {
        throw new Exception("Connections table creation query failed: " . $db->error);
    }
}catch (Exception $e){
    $status = "error";
    $message = $e->getMessage();
}

?>

Tags:

2

Answers


  1. 0

    Just remove ‘,’ from last line

    $tableCreationQuery = "CREATE TABLE `some_table` ( 
    id INT NOT NULL AUTO_INCREMENT,
    some_id INT NULL DEFAULT NULL,
    some_text TEXT DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX (some_id),     <---Here
) ENGINE = InnoDB DEFAULT CHARSET = utf8";
    Login or Signup to reply.
  2. 0

    The message itself usually implies that the connection with the database went stale.

    Do you by any chance have multiple database connections in your script, one of which is used often (in every loop iteration) and another (which the create database statement goes through) is only used once in a while or just a single connection that just hangs for the time that exceeds MySQL’s wait_timeout? If so, you likely need to connect to the database again when such error occurs. It can be detected by a specific errno. Alternatively, you can increase wait_timeout for your session; however, this error can be caused by other reasons, too, so handling it and trying to reconnect is going to be a better option. You can reproduce it in a test script by creating a connection, then putting sleep for a time that exceeds the wait_timeout, and then executing a statement using that stale connection; it’s likely going to be 32 but it’s worth double checking.

    However, without actual code it’s barely possible to say why the script terminates, if this is the issue you’re aiming to solve.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search