Bug #2845 client fails to reconnect if using TCP/IP
Submitted: 17 Feb 2004 13:57 Modified: 14 Mar 2006 17:12
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0 OS:Linux (Linux at least)
Assigned to: Magnus Blåudd

[17 Feb 2004 13:57] Guilhem Bichot
Description:
I reported 
BUG#2555 "Totally silent reconnection when using Unix sockets can fool applications" which was marked as a feature request since. But the fact that when the client is supposed to reconnect, it cannot reconnect if using TCP/IP, is a bug. Here's a testcase in Perl:
#**********************
use DBI;

# --- connect to the database ---
$dsn =              ($ARGV[0] =~ /tcp/i) ?
                    "dbi:mysql:;host=127.0.0.1;port=3306" :
                    "dbi:mysql:;host=localhost",

print "Using $dsn\n";

$dbh = DBI->connect(
                    $dsn,
                    "root","",
{
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
})
|| die $dbh->errstr;
$| = 1;
$dbh->do("drop table if exists test.t");
$dbh->do("create table test.t (a int)");

print "connected; shutdown mysqld and restart it please\n";
for ($i=1; $i<=10; $i++)
  {
    print "$i ";
    # imagine that this program is doing something more useful than sleep ;)
    sleep(1);
  }

$dbh->do("insert into test.t values(1)");
#*******************

If you call it like this "perl connect.pl" it uses Unix sockets to connect to the MySQL server, and if during the sleep of 10 seconds you shutdown/restart mysqld you get no error message from the client: reconnect worked.
If you call it like "perl connect.pl tcp" it will use TCP/IP and you will get instead:

Using dbi:mysql:;host=127.0.0.1;port=3306
connected; shutdown mysqld and restart it please
1 2 3 4 5 6 7 8 9 10 DBD::mysql::db do failed: Lost connection to MySQL server
during query at connect.pl line 33.

So it failed to reconnect, while it is expected to succeed (as mysql.reconnect==1).
I could also reproduce this with a C program.

How to repeat:
see description

Suggested fix:
This bug has been longly discussed in internal emails with subject
"Is it normal that: Connecting using Unix sockets => autoreconnection; using TCP/IP => no"
but now we need to fix it :)
[26 Feb 2004 16:26] Miguel Solorzano
Guilhem: Your test also happens on Windows:

C:\Temp>perl testi
Using dbi:mysql:;host=localhost
connected; shutdown mysqld and restart it please
1 2 3 4 5 6 7 8 9 10 DBD::mysql::db do failed: Lost connection to MySQL server during query at t
esti line 32.
[26 Feb 2004 16:37] Guilhem Bichot
Hi Miguel,
Do you know what was used by the Windows test: named pipes? TCP/IP? other?
Guilhem
[26 Feb 2004 17:19] Miguel Solorzano
Guilhem: TCP/IP with the named pipes disabled.
[5 Mar 2004 1:50] Michael Widenius
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is not a bug.

MySQL automaticly reconnects both for sockets and TCP/IP connections.

The difference between sockets and TCP/IP is that on a socket we get
an error on write, in which case we know that he server did not
receive the command and it's reasonable safe to re-issue the command.

On a TCP/IP socket one doesn't normally get an error when sending the
query, but instead one gets an error when we try to read the result.
As we don't know if the server got and executed the message it's
better to give an error to the user about this. If the user calls
mysql_query() again, MySQL will reconnect and re-issue the query.

Not much one can do about this, except document this issue.
[14 Feb 2006 16:52] Sergei Golubchik
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2362
[16 Feb 2006 11:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2714
[24 Feb 2006 8:06] Magnus Blåudd
Pushed a fix to 5.0.19 and 5.1.8 that will perform a select to see if there is anything to read from the server in "net_clear". If the server has disconnected there will be EOF to read and thus the diconnect can be detected before anything is sent to the server. That means the client will try to reconnect if "reconnect" flag was set.
[27 Feb 2006 10:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3176
[27 Feb 2006 14:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3191
[27 Feb 2006 15:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3198
[28 Feb 2006 16:55] Magnus Blåudd
The last patch didn't work well on windows. It will return from "net_data_is_ready" and perform a blocking read to empty the net.

But windows uses an array instead of a bitmask to store the sd's it should "select" from so it's safe, since the size of the array is set to 64 by default.

Since function returns -1 on windows, it tests the handling of that. It does  not work as expected as it should perform the reads on a non blocking socket.
[28 Feb 2006 17:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3254
[1 Mar 2006 14:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3325
[6 Mar 2006 10:28] Magnus Blåudd
Pushed a fix to 5.0.19 and 5.1.8 that will perform a select to see if there is
anything to read from the server in "net_clear". If the server has disconnected
there will be EOF to read and thus the diconnect can be detected before anything
is sent to the server. That means the client will try to reconnect if
"reconnect" flag was set.
[14 Mar 2006 17:12] Mike Hillyer
Documented in 5.0.19 and 5.1.8 changelogs:

     <listitem>
        <para>
          Client API will now attempt reconnect on TCP/IP if the
          <literal>reconnect</literal> flag is set, as is the case with
          sockets. (Bug #2845)
        </para>
      </listitem>

Also updated the server gone away section of the 5.0 and 5.1 refman to reflect that auto-reconnects work on TCP/IP.