There are several parameters to set a timeout on MySQL :
But I would like to focus on wait_timeout (or interactive_timeout depending on how you connect)
This timeout allows MySQL to automatically close a connection in case of non-activity during the time defined by this parameter (default value is 28000 seconds).
The problem I wish to explain here may happen when this timeout is set to a low value (about 10 to 30 seconds).
Indeed, in this case, this timeout may have serious consequences, look at that :
[The wait_timeout parameter is set to 10 seconds in this case]
You are connected through the standard MySQL client and you need to run a delete query (bypass autocommit) :
- mysql> start transaction;
- [ You wait more than 10s because a friend calls your attention about the latest news on iphone 5 ! ]
- mysql> delete from most_valuable_table;
Your problem starts here because you forgot the where clause !
After the delete command, MySQL said :
ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect... Connection id: 98 Current database: masterQuery OK, 33698541 rows affected (0.01 sec)
This simply means that the connection was lost and the last order re-submitted.
Then, you try to cancel your incomplete “delete” with a rollback command (your heart beats faster now…) :
- mysql> rollback;
- mysql> select * from most_valuable_table;
This select returns 0 rows, cruel !
It isn’t a mistake, the “start transaction” order was lost when MySQL has gone away.
The rollback is no longer possible. Bye bye to your 33.000.000 of rows !
It’s time to go to vacation…