Welcome to Our Community

Most of our features and services are available only to our members. So we encourage you to login or join us by registering a new account. Registration is free, fast, and simple. You can use your Facebook or Twitter social account or manualy register by providing a valid email address so we can minimize spammers.

Sign up

SQL How to Change the MySQL Timeout on a Server

Discussion in 'Programming and Web Design' started by steve, Aug 4, 2014.

Loading...
Thread Status:
You must be a logged-in, registered member of this site to view further posts in this thread.
  1. Offline

    steve -=Super Moderator=- Staff Member Super Moderator Active User

    Jul 12, 2014
    1,304
    182
    Black Pearl
    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. Rarely does the wait_timeout value cause the problem, and changing the value does not fix the problem. For cases where an application fails to close a connection it is no longer using, a low wait_timeout value can help to avoid hitting max_connections simply due to "sleeping" idle connections that are not in a transaction and will not be reused.

    Follow these steps to resolve the issue:

    1. Login to your server using SSH.

    2. Edit my.cnf (the MySQL configuration file).

      Code (Text):
      sudo vi /etc/my.cnf
    3. Locate the timeout configuration and adjust it to fit your server.

      • The interactive timeout does not affect any web application connections. A high interactive_timeoutbut a low wait_timeout is normal and is the best practice.

      • Choose a reasonable wait_timeout 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 adjustwait_timeout 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 wait_timeout value online. This does not require a MySQL restart, and thewait_timeout can be adjusted in the running server without incurring downtime. You would issue set global wait_timeout=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 wait_timeout 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=3600upon connecting.
    4. Save the changes and exit the editor.

    5. Restart MySQL to apply the changes as follows:

      Code (Text):
      sudo /etc/init.d/mysql restart
    Once the restart completes, the new changes are applied.
     
    #1
  2. Offline

    Nysle Senior Moderator Staff Member Senior Moderator Active User

    Jul 22, 2014
    1,009
    81
    Laguna
    mapag aralan na nga yung mga ganto :think: 
     
    #2
  3. Offline

    steve -=Super Moderator=- Staff Member Super Moderator Active User

    Jul 12, 2014
    1,304
    182
    Black Pearl
    hahaha, madali lng yan bro. this week post aq ng isang article about dyan na pwede paganahin sa mga android devices
     
    #3
  4. Offline

    Nysle Senior Moderator Staff Member Senior Moderator Active User

    Jul 22, 2014
    1,009
    81
    Laguna
    cge po sir pag may time pag aaralan ko yan para pag dating ko sa college alam ko na yan :happy: 
     
    #4
Thread Status:
You must be a logged-in, registered member of this site to view further posts in this thread.

Share This Page