Page 1 of 1
Fixing MySQL server has gone away
Posted: Tue Feb 14, 2012 11:59 am
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.
Re: Fixing MySQL server has gone away
Posted: Tue Feb 14, 2012 12:26 pm
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?
Re: Fixing MySQL server has gone away
Posted: Tue Feb 14, 2012 12:42 pm
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.
Re: Fixing MySQL server has gone away
Posted: Tue Feb 14, 2012 1:23 pm
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