Fixing MySQL server has gone away

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Fixing MySQL server has gone away

Post by Eric! »

Just thought I'd share this solution. I was working on some old legacy mysql code and I found that on one host the MySQL server has gone away kept popping up after a script had been running other tasks.

Changing the mysql.connect_timeout value didn't work.
Changing the default_socket_timeout value didn't work.
Adding mysql_ping didn't work.
Adding mysql_connect didn't work either.

What worked? It wasn't until I did a mysql_close followed by another mysql_connect that the connection came back.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Fixing MySQL server has gone away

Post by mikosiko »

would be interesting to know what exactly do you have in the script causing the problem... executing stored procedure or trying to execute a multi-query using the mysql API are a couple of causes of that error message; also what value do you have for wait_timeout (number of second that the server will wait for activity over a connection before to close it) in your mysql configuration file?
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: Fixing MySQL server has gone away

Post by Eric! »

The script flows like this
-Access database
-Spool file (sometimes big files > 120 seconds depending on user's connection)
-Access database -- mysql server has gone away error

I wasn't aware there was yet another timeout parameter in mysql. Unless mysql.connect_timeout is the same.... Perhaps this is the root cause of the drop out.
[text]Variable_name Value
wait_timeout 30[/text]But msql_ping and/or reconnecting is supposed to work according to the docs, but I had to close it and connect again.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Fixing MySQL server has gone away

Post by mikosiko »

no, connection_timeout and wait_timeout are not the same... I will suggest to increase the value for wait_timeout (instead of close and re-open the connection).. start with some high value (10 minutes ... 600 seconds) and adjust gradually lowering it until you find the right value.. according to your flow seems to be the problem
Post Reply