Archive

Posts Tagged ‘wait_timeout’

Fatal timeout !

July 7th, 2011 1 comment

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…

Categories: Admin, Tips