How to set no timeout to mysql?

October 26, 2015 1k views

MySQL disconnects automatically after some time has passed. How can I remove the timeout?

1 Answer

A MySQL server timeout can occur for many reasons, but happens most often when a command is sent to MySQL over a closed connection. The connection could have been closed by the MySQL server because of an idle-timeout; however, in most cases it is caused by either an application bug, a network timeout issue (on a firewall, router, etc.), or due to the MySQL server restarting.
You can follow these steps to fix your issue:

  • Edit your my.cnf (MySQL config file)
sudo nano /etc/mysql/my.cnf
  • Add the timeout configuration and adjust it to fit your server.
wait_timeout = 28800
interactive_timeout = 28800
  • The interactive timeout does not affect any web application connections. A high interactivetimeout but a low waittimeout is normal and is the best practice.

  • Choose a reasonable waittimeout value. Stateless PHP environments do well with a 60 second timeout or less. Stateful applications that use a connection pool (Java, .NET, etc.) will need to adjust waittimeout to match their connection pool settings. The default 8 hours (wait_timeout = 28800) works well with properly configured connection pools.

  • Configure the wait_timeout to be slightly longer than the application connection pool's expected connection lifetime. This is a good safety check.

  • Consider changing the waittimeout value online. This does not require a MySQL restart, and the waittimeout can be adjusted in the running server without incurring downtime. You would issue set global waittimeout=60 and any new sessions created would inherit this value. Be sure to preserve the setting in my.cnf. Any existing connections will need to hit the old value of waittimeout if the application abandoned the connection. If you do have reporting jobs that will do longer local processing while in a transaction, you might consider having such jobs issue set session wait_timeout=3600 upon connecting.

  • Save the changes (CTRL + X , Y , ENTER)

  • Restart MySQL

    sudo service mysql restart

    Once it restarted, the new changes should be applied. Hope this will help you.

Have another answer? Share your knowledge.