MySQL

What is auto reconnect in MySQL?

The MySQL client library can perform an automatic reconnect to the server if it finds that the connection is down when you attempt to send a statement to the server to be executed. In this case, the library tries once to reconnect to the server and send the statement again.

Automatic reconnection can be convenient because you need not implement your own reconnect code, but if a reconnection does occur, several aspects of the connection state are reset and your application will not know about it. This is extremely useful if you long running persistent connections to the database. Also if you have too many sql queries / use connection pool etc. auto reconnect is a handy feature. Let's look at how to enable / disable auto reconnect in MySQL and what are the side-effects of enabling auto reconnect.

How to enable MySQL client auto reconnect

my_bool reconnect = 1;
mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect);

What are the side-effects of MySQL auto reconnect?

  • Any active transactions are rolled back and autocommit mode is reset.
  • All table locks are released.
  • All TEMPORARY tables are closed (and dropped).
  • Session variables are reinitialized to the values of the corresponding variables. This also affects variables that are set implicitly by statements such as SET NAMES.
  • User variable settings are lost.
  • Prepared statements are released.
  • HANDLER variables are closed.
  • The value of LAST_INSERT_ID() is reset to 0.
  • Locks acquired with GET_LOCK() are released.
  • mysql_ping() does not attempt a reconnection if the connection is down. It returns an error instead.

Source

How to disable MySQL client auto reconnect

In view of the side-effects you may want to disable auto reconnect. In MySQL version 5.1 and above auto reconnect is disabled by default. In any version you can disable auto reconnect with the following PHP code:
my_bool reconnect = 0;
mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect);