MySQl Error while sending QUERY packet if Connection not closed and Reopened

Question

I'm getting the Warning Error while sending QUERY packet in PID= when I use a SELECT statement that only gets 5 rows of data from 2 columns, then make the script sleep for 10 minutes before waking up to continue an INSERT into the db.

If I close the connection after the SELECT statement and reopen the connection just before the INSERT, then everything works fine and no error is produced.

I can't figure out why this is happening. I'm on a public shared server.

Select

The values selected are: Seller which is 10 characters in length and Token which is 872 in length.

include('con.php');
if ($result = $con->query("SELECT Seller,Token FROM `Sellers`")) { 
    $sellers = $result->fetch_all(MYSQLI_ASSOC);
    $result->close();
}
$con->close();

Sleep

sleep(600);

Insert

All values being inserted are minimal in length of which 21 characters is the longest in the column jobType. Total of 5 rows being inserted from the foreach loop.

include('con.php');
foreach(...) {
    $insert = "INSERT INTO `jobStatus` (ack, jobId, RefId, Type, Status, error, percent, Seller, creationTime, completionTime) VALUES ('$ack', '$jid', '$frid', '$jtyp', '$jstat', '$errc', '$pcom', '$sid', '$crtime', '$cotime')";

    if($con->query($insert)) {
        echo "inserted into db successfully.\n";
    } else {
        echo "not inserted into db. Query Failed.\n";
    }
}
$con->close();

Above code works without an error because of closing and reopening the connection in between the statements.

I want it to work without the error when I keep the connection open after the SELECT and then closing the connection after the INSERT.

Can someone point out what I need to do in order to accomplish this?

Note: I already have the set_time_limit set to 0 in my script.

set_time_limit(0);

Here is the code which produces the error.

Code that causes error

Select

include('con.php');
if ($result = $con->query("SELECT Seller,Token FROM `Sellers`")) { 
    $sellers = $result->fetch_all(MYSQLI_ASSOC);
    $result->close();
}

Sleep

sleep(600);

Insert

foreach($sellers as $seller) {
    $insert = "INSERT INTO `jobStatus` (ack, jobId, RefId, Type, Status, error, percent, Seller, creationTime, completionTime) VALUES ('$ack', '$jid', '$frid', '$jtyp', '$jstat', '$errc', '$pcom', '$sid', '$crtime', '$cotime')";

    if($con->query($insert)) {
        echo "inserted into db successfully.\n";
    } else {
        echo "not inserted into db. Query Failed.\n";
    }
}
$con->close();

Update:

Here are the results of SHOW VARIABLES LIKE '%timeout'

$SESSION_VARIABLES = array(
    array(
        "Variable_name" => "connect_timeout",
        "Value" => "10",
    ),
    array(
        "Variable_name" => "delayed_insert_timeout",
        "Value" => "300",
    ),
    array(
        "Variable_name" => "innodb_flush_log_at_timeout",
        "Value" => "1",
    ),
    array(
        "Variable_name" => "innodb_lock_wait_timeout",
        "Value" => "50",
    ),
    array(
        "Variable_name" => "innodb_rollback_on_timeout",
        "Value" => "OFF",
    ),
    array(
        "Variable_name" => "interactive_timeout",
        "Value" => "30",
    ),
    array(
        "Variable_name" => "lock_wait_timeout",
        "Value" => "86400",
    ),
    array(
        "Variable_name" => "net_read_timeout",
        "Value" => "30",
    ),
    array(
        "Variable_name" => "net_write_timeout",
        "Value" => "60",
    ),
    array(
        "Variable_name" => "slave_net_timeout",
        "Value" => "60",
    ),
    array(
        "Variable_name" => "thread_pool_idle_timeout",
        "Value" => "60",
    ),
    array(
        "Variable_name" => "wait_timeout",
        "Value" => "30",
    ),
);

Answer 1

The mysql connection times out after 30 seconds, i. e., gets closed automatically after 30 seconds of inactivity and there is nothing you can do about it (except polling it every 29 seconds while sleeping). I suggest you use mysqli::ping after the sleep to reconnect if necessary:

if ($con->ping()) {
    // foreach... insert
}