When running query against a database the following error can be generated:
ERROR 2006 (HY000) at line NNN: MySQL server has gone away
Where "NNN" is the line number of the script currently being run where the error occurred.
Possible Causes and Resolution
This is a general error which can have a number of possible causes. The one certainty is that the MySQL database is no longer listening on the connection - but this does not necessarily mean that MySQL is no longer available.
Possible causes:
MySQL has crashed.
Check that MySQL is still running on the server. If it is not running and this was not a planned shutdown then it is reasonable to conclude that MySQL has crashed. I personally have never experienced this, but if it happens then investigate whether you can upgrade to a newer version.
MySQL has been shutdown.
Check that MySQL is still running, if it is not and it was shut down (accidentally or intentionally) then that is the cause of the problem. Restart MySQL.
Network connectivity has failed.
Even if the database is still up, is it possible to contact the server? If you can ping the server (or MySQL is running no the localhost) then basic network connectivity is not the problem.
MySQL networking has failed
The problem may be the MySQL connection. If the query was trying to transfer a large block of data then the MySQL packet-size may be set too small.
Try increasing the value of max_allowed_packet and restart the MySQL database service. In MySQL Administrator select "Startup Variables" andon the "Advanced Networking" tab thsi is shown under "Data / Memory size" and is labeled as "Max. packet size." The default is 1MB and one of my systems now runs at 16MB.
The database connection has timed out and thus the SQL has failed.
Try increasing the wait_timeout, or reconnecting just before firing the query.
Modified after copying from http://www.cryer.co.uk/brian/mysql/trouble_mysql_gone_away.htm
Showing posts with label wait_timeout. Show all posts
Showing posts with label wait_timeout. Show all posts
Monday, August 23, 2010
Thursday, July 19, 2007
mysql wait_timeout
There is an issue with mysql if you are using mysql_pconnect.
What happens is that if you have configured http or apache for 150 max connections and mysql for 100 max connections and you are using mysql_pconnect then each http thread (supposing you are running php scripts only) creates a connection with mysql. And maintains the connections. So what happens, the first 100 http take up the 100 mysql connections and then even if the query is not over, dont free them. And the 101th http gives an error Mysql:Too many connections.
The way to handle this is
Dont use mysql_pconnect for connecting with the mysql database from php scripts. Use mysql_connect.
I have come across cases when even when mysql_pconnect is not being used, still the mysql connections are used up and though mysql does not show any query in its processlist, still all the threads of mysql are still live and are in sleep state. Now that's strange.
I had a server which was performing perfectly fine. And suddenly one day i start getting "too many connections" in the server. And on checking the processlist i found that all threads of mysql are in sleep state. That's strange. Suddenly one day the server starts misbehaving. And even on restarting the server, it does not behave properly. As soon as the server is restarted, the number of connections get maxed out.
There is one variable in mysql which can save the day. The "wait_timeout". By default it is set to 28840 (or something like that). And it defines the number of seconds a mysql thread waits in idle state before mysql preempts and kills it. So all you have got to do is to change this variable to something like 30 or so. For that you will have to run a "show processlist" and check the maximum time of a thread in sleep state and what time ought to suit your environment.
simply run
set global wait_timeout = 30
in mysql prompt and your task is done. But better edit the my.cnf and save it there, so that it is incorporated when mysql is restarted. With this done, no thread of mysql will remain active if it is idle for over 30 seconds. And a query running for over 30 seconds would be allowed to execute since that is not an idle thread.
What happens is that if you have configured http or apache for 150 max connections and mysql for 100 max connections and you are using mysql_pconnect then each http thread (supposing you are running php scripts only) creates a connection with mysql. And maintains the connections. So what happens, the first 100 http take up the 100 mysql connections and then even if the query is not over, dont free them. And the 101th http gives an error Mysql:Too many connections.
The way to handle this is
Dont use mysql_pconnect for connecting with the mysql database from php scripts. Use mysql_connect.
I have come across cases when even when mysql_pconnect is not being used, still the mysql connections are used up and though mysql does not show any query in its processlist, still all the threads of mysql are still live and are in sleep state. Now that's strange.
I had a server which was performing perfectly fine. And suddenly one day i start getting "too many connections" in the server. And on checking the processlist i found that all threads of mysql are in sleep state. That's strange. Suddenly one day the server starts misbehaving. And even on restarting the server, it does not behave properly. As soon as the server is restarted, the number of connections get maxed out.
There is one variable in mysql which can save the day. The "wait_timeout". By default it is set to 28840 (or something like that). And it defines the number of seconds a mysql thread waits in idle state before mysql preempts and kills it. So all you have got to do is to change this variable to something like 30 or so. For that you will have to run a "show processlist" and check the maximum time of a thread in sleep state and what time ought to suit your environment.
simply run
set global wait_timeout = 30
in mysql prompt and your task is done. But better edit the my.cnf and save it there, so that it is incorporated when mysql is restarted. With this done, no thread of mysql will remain active if it is idle for over 30 seconds. And a query running for over 30 seconds would be allowed to execute since that is not an idle thread.
Subscribe to:
Posts (Atom)