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

P.S. I just noticed "then make the script sleep for 10 minutes"...why are you doing that? Possibly your connection times out. — ADyson
– ADyson, Commented Feb 19, 2019 at 17:29
@Mike What you posted is not exactly what I meant. I was just trying to verify that the timeout is 8h like you claimed. But it looks like you do not have the rights to execute this query... Do you have e. g. phpmyadmin where you can try to execute it? if it does not work, you could try SELECT @@wait_timeout and SELECT @@interactive_timeout ... However I would definitely consider using mysqli::ping to reconnect if necessary — Thomas Leu
– Thomas Leu, Commented Feb 19, 2019 at 19:18
"What do I need to do in my code to take advantage of the pooling" ...nothing, it all happens in the background, if it's enabled. Your code doesn't need to change at all. See the "connection pooling" section here. php.net/manual/en/mysqli.quickstart.connections.php — ADyson
– ADyson, Commented Feb 19, 2019 at 19:31
P.S. " from what I've read, keeping a connection open is the most efficient way to go"...hm, I'd say that was only true if you're actually using it. Opening and closing a connection has an overhead, yes, but it only becomes noticeable if you repeatedly open and close it many times within a few seconds. People advise you not to close and re-open when doing things in quick succession, e.g. when web apps make lots of queries to build a page. Then you'd notice it slowing down.... — ADyson
– ADyson, Commented Feb 19, 2019 at 21:12
....But if there's 10 minutes between the queries then you'll never notice any problem. In fact you could argue that holding onto a connection you know you won't be using is actually less efficient, because there is a limit on the number of concurrent connections allowed on any DB server, and so in theory you're potentially preventing another process from using that connection, and you're also keeping data in memory which is not being used.... — ADyson
– ADyson, Commented Feb 19, 2019 at 21:14

Thomas Leu · Accepted Answer · 2019-02-19 20:41:32Z

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
}

answered Feb 19, 2019 at 20:41

Thomas Leu

8554 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Over a year ago

Ok will try this and report back.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related