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:
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:
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]
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:
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:
Connection Timeout with MySQL Database
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.