Bug #12745 Graceful Reconnection handling
Submitted: 23 Aug 2005 4:13 Modified: 14 Jun 2013 16:57
Reporter: Oliver Smith Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S4 (Feature request)
Version:4.1.13 OS:Any (All)
Assigned to: Matthew Lord CPU Architecture:Any

[23 Aug 2005 4:13] Oliver Smith
If a MySQL command is interrupted by a connection loss it returns CR_SERVER_LOST. However in many cases, e.g. mysql_real_query, the next invocation will cause the client to transparently and gracefully reconnect.

This causes MySQL clients to generally be very unreliable, giving MySQL the *appearance* of being unreliable.

The "how to repeat" code closes with the lines:
"The second truncate was lost to the server restart"
"Third truncate has recovered tho"

Suggestion is in "suggested fix"

How to repeat:

  if (mysql_query(conn, "create temporary table tmp_foo (bar int)") ) throw std::runtime_error("failed creating tmp_foo");
  if (mysql_query(conn, "truncate tmp_foo")) throw std::runtime_error("first conn failed, something very wrong");
  system("ssh $SERVER service mysql restart");
  if (mysql_query(conn, "truncate tmp_foo"))
    if (mysql_errno(conn) != CR_SERVER_LOST)
      throw std::runtime_error("We got some other error");
    cout << "The second truncate was lost to the server restart" << endl;
  if (mysql_query(conn, "truncate tmp_foo"))
    cout << "Server isn't coming back" << endl;
    cout << "Third truncate has recovered tho" << endl;

Suggested fix:
Provide a parameter/option that will tell MySQL to perform the connection attempt automatically during the original command, so that from a client perspective the reconnect is entirely transparent -- only a complete failure to reconnect will return me CR_SERVER_GONE.


  mysql_set_server_option(conn, MYSQL_IMMEDIATE_RECONNECT_ATTEMPTS);
  mysql_query(conn, "create temporary table tmp_foo (bar int))");
  mysql_query(conn, "truncate tmp_foo");
  system("ssh $SERVER service mysql restart");
  if(mysql_query(conn, "truncate tmp_foo"))
    throw std::runtime_error("Server is not comin' back");
    throw std::runtime_error("All 3 truncates were processed without end-user intervention");
[23 Aug 2005 4:15] Oliver Smith
The suggested code snippet isn't as clear as I meant it to be. In short - instead of needing to call mysql_query twice, after the server has been restarted the next mysql_query I perform won't tell me CR_SERVER_LOST instead it will try to reconnect and issue the query after reconnecting before returning to me.

Obviously it would be nice to be able to specify how long to try reconnecting for too. :)
[14 Jun 2013 16:57] Matthew Lord
Hi Oliver,

Thank you for the feature request, and for your help in making MySQL even better!

I'm closing this old feature request for now, as the auto-reconnect feature has been disabled by default since 5.1:

If you have a specific feature request that still applies to 5.6 and 5.7, please let me know.

Regarding "graceful" or transparent reconnect, this is not currently an option because all context is lost when re-connecting (user variables, session values, temp tables, etc.). Retaining the connection context may be possible in the future, and then we can implement something like this.

Thanks again!