Increase MySql database time wait
For example in a rare condition we have an application which running socket connection between 2 server and communicates each other's data. But communication times are inconsistent and it can happen once in 1 hour or 2 hour once or even after 8 hours once. Lets assume in that case we having a open MySql database connection and whenever request comes it needs to preform its appropriate database activity.
In that case if time wait goes more than 8 hours then MySql will disconnect all those database connections. So we need of more time wait from 8 hours to 10 or 15 hours or 1 days etc.,
So lets see how to change default MySql time wait option.
Before changing default time wait in MySql lets query for the current time wait which set in server.
mysql> show variables LIKE '%timeout%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+----------------------------------+-------+
By above query we can get current MySql "wait_timeout" and "interactive_timeout" values as 28800(seconds equal to 8 hrs). Next we will see about changing those values from 8 hrs to 24 hrs.
Under /etc edit my.cnf file with below details
# vi /etc/my.cnf
[mysqld]
wait_timeout=86400
interactive_timeout=86400
NOTE: Suppose if my.cnf file not exist please create it.
Once changed please restart MySql and check for the parameters has been updated or not.
mysql> show variables LIKE '%timeout%';
+------------------------------------+--------+
| Variable_name | Value |
+------------------------------------+--------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 86400 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 86400 |
+------------------------------------+--------+
From above table we can see timeout value got changed from 8 hrs to 24 hrs.