The management of connection timeout is one of the most important aspects when working in client-server architecture in MySQL. A connection timeout can be defined as the duration of time for which a client waits expecting a response from the server before a connection is considered ‘unsuccessful‘.
When working with MySQL it becomes crucial to manage this setting. The client may keep facing the connection timeout issue which will lead to bad performance, unnecessary resource consumption & instability. To prevent this, we have to re-configure & increase the ‘wait_timeout’ or ‘interactive_timeout‘ settings in the MySQL Database.
Types of Connection Settings
There are 2 types of connection settings:
- GLOBAL Level: The settings will apply to every client connected with your MySQL Server instance. Its scope is global. Global settings affect the performance, stability & security of the overall server.
- SESSION Level: The session-level settings apply only to the current session. Every client application can have its current session. The changes will only affect for however long the connection lasts.
System Variable for Connection Timeout Management
MySQL provides us with mainly two of these timeout settings – “wait_timeout” & “interactive_timeout”. These are two very important system variables that define the idle connection duration between the client and the server –
1. wait_timeout: It specifies the maximum amount of time a connection can stay idle (inactive).
USE-CASE: It is primarily used for scripts & applications that initiate the connection with the MySQL Server.
2. interactive_timeout: It is similar to wait_timeout but it’s mainly designed for an interactive environment if we have multiple clients interacting with our MySQL database and none of them does anything for a specified amount of time then the connection will be terminated.
USE-CASE: It is primarily used for clients that initiate the connection with the MySQL Server & create an interactive environment.
NOTE: Default_VALUE for both is: 28800 seconds (8-hours).
Check Current Timeout Settings
1. At Session-Level
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';
2. At Global-Level
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';
Output:
Default Values
Above are the default value output in MySQL.
How to Deal with a Connection-Timeout Error?
If the client application keeps facing connection-timeout errors or, if the Server has a working connection without interaction for prolonged hours then you can increase or decrease the duration which can be done by executing simple queries. But there are further 2 cases –
1. You need to increase the timeout only for current session and keep the global settings as it is.
2. You need to modify the overall GLOBAL Setting so that any client connected to your MySQL Server can have a prolonged timeout duration.
1. At Session-level
Used if the change in timeout is targetted only to the currently connected clients
SET SESSION wait_timeout = 3600;
SET SESSION interactive_timeout = 4200;
Output:
Session-level vicinities configuration
2. At Global Level
If you want the change to apply globally to all the clients connected with the server.
SET GLOBAL wait_timeout = 4800;
SET GLOBAL interactive_timeout = 5200;
Output:
Global-level settings configuration
Note: Both of the above settings are volatile, and the values will be reset to their defaults if the MySQL Server is restarted. But if you want the change to persist. You might need to modify the configuration file which is usually stored in the following places in specific OS:-
1. Windows: C:\ProgramData\MySQL\MySQL Server X.Y\my.ini (where X & Y are +ve integers denoting the version number)
2. Linux: /etc/my.cnf
3. MaxOS: /usr/local/mysql/my.cnf
Follow the below steps to modify the my.ini File on Windows Operating System:-
Step 1: Open Notepad with administrator privileges
Step 2: Then open the file on the location – C:\ProgramData\MySQL\MySQL Server X.Y\my.ini
Step 3: Scroll down & search for the section – [mysqld]
Find – [mysqld]
Add the below [mysqld]:
wait_timeout = 3600
interactive_timeout = 5200
(You can change it as per your requirements)
Note: The values provided are in seconds.
Step 4:
Modified
Step 5: Now restart your MySQL Service from the service manager on Windows.
Go to Search > Type - 'Services' > Open Services > Look for something like : MySQL80 > Right Click > Restart
Step 6: Now again go to the client and Check for the System Variables. Output should be as the values you gave during modification:
Output after changing the configuration file
Conclusion
In Conclusion, a “Connection Timeout” error in MySQL may occur due to a variety of reasons, such as network issues, server overload, and misconfigurations. Therefore, it is important to identify the source of the error, follow proper troubleshooting procedures, and take preventative measures to maintain good database connections and reduce downtime.