MySQL Bugs: #2845: client fails to reconnect if using TCP/IP

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	CPU Architecture:	Any

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

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.

Hi Miguel,
Do you know what was used by the Windows test: named pipes? TCP/IP? other?
Guilhem

Guilhem: TCP/IP with the named pipes disabled.

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.

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

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

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.

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

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

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

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.

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

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

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.

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.