MySQL Server keeps "going away" in PHP script

Question

I have a script that is attempting to log some data (usually around 200-300kb at a time). It has been running just fine for quite some time. I do not know of any configuration changes made to either the MySQL server or the PHP on the processing server. Yesterday the following code block started misbehaving. Two days ago the "$sqlBucket" query worked just fine. Now, it fails and the mysql_error returns "MySQL server has gone away". I added the debugging in the first if block and it outputs:

The Link Is Down
Link still down

The code:

if (!mysql_ping($conn))  {
   echo "The Link Is Down!\n";
   mysql_close($conn);
   if (!($conn = mysql_connect($hostname,$username,$password))) echo "Fail " . $mysql_error() . "\n";
   mysql_select_db($db,$conn);
   if (!mysql_ping($conn)) echo "Link still down\n";
}
if (!mysql_query($sqlBucket, $conn)) {
    echo "Could not execute: {$sqlBucket}" . mysql_error() . "\n";
}

If anyone has any thoughts on something to check, debug, reconfigure, etc I have run out of ideas. Thanks.

EDIT: Extra info

The script executes and fails in ~3 seconds.

The wait_timeout value on the MySQL server is 28800.

I am able to send the queries successfully through the command line.

I added a check on the mysql_connect function and it is reporting success (at least I am not getting a "Fail" in the output).

how long (secounds) is it taking? had that issue with slow\long scripts and a temperamental db server — user557846
– user557846, Commented Jan 24, 2013 at 4:05

HorusKol · Accepted Answer · 2013-01-24 04:29:20Z

1

Look at the table structures and indexes - as the table grows, it can take longer to write data. 200-300 kB sounds a fair bit of data, and that will accumulate quickly (1 MB every 3-4 updates).

Also, enable your MySQL slow query log to see exactly where the slowness is entering your system.

answered Jan 24, 2013 at 4:29

HorusKol

8,73610 gold badges58 silver badges96 bronze badges

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

1 Comment

Brian Over a year ago

I should have been more specific, the table that this is initially failing on is a lookup table that only is less than 1kb per row and is only ~500Mb in size currently. I do have some 100+Gb tables but I'm not even getting to those before the errors kick in. I do have the slow query log running and it is not showing anything connected with this script.

Brian · Accepted Answer · 2013-01-28 16:24:00Z

Post-Mortem:

Apparently the solution to this one had very little to do with the error code I was receiving. As it turned out I had, in an attempt to free up some room, removed a superfluous table. Well, what I didn't realise is that another developer had a trigger that was populating this table. The trigger was called for inserts on the table that I was involved in the query in a block below the code block I referenced in the original question. What I noticed is that the code executed fine the first time and then was failing after that. What was happening was that the trigger error was somehow killing the connection to the server. After dropping the trigger the script started running just fine.

I am still unsure why redoing the connection wasn't allowing it to proceed and act like it would during the first iteration.

Collectives™ on Stack Overflow

MySQL Server keeps "going away" in PHP script

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related