How To Enable/ Disable Auto Reconnect in MySQL

By Angsuman Chakraborty, Gaea News Network
Friday, July 20, 2007


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.


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);


December 30, 2009: 5:55 am

How can use Connected mode connection in with c# using Mysql Database.
I want to use only one Connection Like Classic ASP.


Elvis kim
August 18, 2009: 2:11 am

Hello. I have a question about reconnect. If automatic reconnection option is enable on my system, but the mysql server dose not work..then how many times will the system try to connect? Can I set the attempt count? And isn’t there any memory problem?(Is that sure mysql_close and open again?) I have some problem the option so I ask that. Thanks for reading.

May 23, 2008: 4:15 pm

There’s no mysql_options() command listed anywhere in PHP documentation that I can find (trying to use it gives an ‘undefined function’ error). my_bool is definitely not a PHP variable type. PHP variables also need $’s, of which you have none. I’m sorry, but this is NOT php code.

You provide useful explanation of auto-reconnection functionality, but please don’t mislead people by claiming it’s useful code for PHP developers.

October 18, 2007: 1:08 pm

It is PHP code. Check the fine manual :)

October 17, 2007: 1:25 pm

That’s not PHP code :-) C/C++ perhaps.

will not be displayed