Bug #27613 auto_reconnect not matching CR_SERVER_LOST (only CR_SERVER_GONE_ERROR)

Submitted: 3 Apr 2007 12:59 Modified: 20 Feb 2013 22:47
Reporter: Wouter de Jong Email Updates:
Status: Unsupported Impact on me:
None 
Category:Connectors: DBD::mysql ( Perl ) Severity:S1 (Critical)
Version:3.002 - 4.007 OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Tags: Contribution

[3 Apr 2007 12:59] Wouter de Jong
Description:
mysql_auto_reconnect is not working for me in DBI 3.002 through 4.004 when the server is sending a CR_SERVER_LOST (error 2013). This happens in my case when the 'low' wait-timeout on the server has been reached.

Reason is that in dbdimp.c : mysql_db_reconnect() is only checking against CR_SERVER_GONE_ERROR (error 2006).

  if (mysql_errno(&imp_dbh->mysql) != CR_SERVER_GONE_ERROR)
    /* Other error */
    return FALSE;

My idea is that when the wait_timeout is hit, the connection should also be restored automatically. Eg. I don't see a big difference in CR_SERVER_GONE_ERROR and CR_SERVER_LOST.

How to repeat:
What I do to test this :

my $dbh = DBI->connect("DBI:mysql:database=test;host=$h",$u,$p) || die("mysql_connect() failed\n");
printf("AMOUNT: %d\n",run_q());
sleep(90);
printf("AMOUNT: %d\n",run_q());
$dbh->disconnect();

sub run_q {
        my $_sth        = $dbh->prepare("SELECT 100 AS amount");
        $_sth->execute();
        my $_amount     = $_sth->fetchrow;

        return($_amount);
}

The 2nd run_q fails since the wait_timeout on the MySQL-server has been exceeded, and the server has closed the connection and sent error 2013.

You could opt for doing a disconnect before the sleep() and then make a connection after the sleep again, but the real code is way more complex and dynamic in time runs (eg. I/O operation). That would make more and more connect/disconnect operations that aren't usually necessary (wait_timeout is only seldom reached).

Suggested fix:
Add a check for CR_SERVER_LOST (error 2013).

--- dbdimp.c    Tue Apr  3 14:54:56 2007
+++ dbdimp.c-lost       Tue Apr  3 14:47:49 2007
@@ -4434,7 +4434,7 @@
   else
     imp_dbh= (imp_dbh_t*) imp_xxh;

-  if (mysql_errno(&imp_dbh->mysql) != CR_SERVER_GONE_ERROR)
+  if (mysql_errno(&imp_dbh->mysql) != CR_SERVER_GONE_ERROR && mysql_errno(&imp_dbh->mysql) != CR_SERVER_LOST)
     /* Other error */
     return FALSE;
[3 Apr 2007 16:58] Valeriy Kravchuk
Thank you for a bug report. I've got the following results:

mysql> show global variables like 'wait%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 30    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> perl 27613.pl
AMOUNT: 100
DBD::mysql::st execute failed: MySQL server has gone away at 27613.pl line 12.
DBD::mysql::st fetchrow failed: fetch() without execute() at 27613.pl line 13.
AMOUNT: 0
openxs@suse:~/dbs/5.0> cat 27613.pl
use DBI;

my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost",'root','') ||
die("mysql_connect() failed\n");
printf("AMOUNT: %d\n",run_q());
sleep(90);
printf("AMOUNT: %d\n",run_q());
$dbh->disconnect();

sub run_q {
        my $_sth        = $dbh->prepare("SELECT 100 AS amount");
        $_sth->execute();
        my $_amount     = $_sth->fetchrow;

        return($_amount);
}

But what results you had expected? Why do you think it is a bug?
[3 Apr 2007 19:34] Wouter de Jong
Hi,

I have the following result :

AMOUNT: 100
DBD::mysql::st execute failed: Lost connection to MySQL server during query at ./test.pl line 38.
DBD::mysql::st fetchrow failed: fetch() without execute() at ./test.pl line 39.
Use of uninitialized value in printf at ./test.pl line 26.
AMOUNT: 0

You notice that I have a 'Lost connection to MySQL server during query' instead of 'MySQL server has gone away' ?

However, the script I posted is wrong. It should have the line :

$dbh->{'mysql_auto_reconnect'} = 1;

before the first printf()
If you'd add this line, you'd probably see the following result, and that's the result I expected :

AMOUNT: 100
AMOUNT: 100

Instead, I get the results from above : 

AMOUNT: 100
DBD::mysql::st execute failed: Lost connection to MySQL server during query at ./test.pl line 38.
DBD::mysql::st fetchrow failed: fetch() without execute() at ./test.pl line 39.
Use of uninitialized value in printf at ./test.pl line 26.
AMOUNT: 0

This because the mysql_db_reconnect function in dbdimp.c is returning FALSE since the SERVER_LOST error is not valid. Only the SERVER_GONE error is valid.

And in my opinion ... this is a 'bug' in the sense of a 'missing feature' :)
Classified as S4 (Feature request) now.

However, would be nice to have this 'fixed' soon (for some projects coming up).

Also, I found a big difference (it seems for now) : when connecting to 'localhost', server returns SERVER_GONE.
When connecting to a remote server (not on the same host), server returns SERVER_LOST. This is tested on Fedora Core 6 - MySQL 5.0.27 and source-compiled MySQL 5.0.15

So could you test it also on a 'remote' (non-localhost) server, with the $dbh->{'mysql_auto_reconnect'} = 1 ?
[4 Apr 2007 7:33] Wouter de Jong
Re-reading my own words ... this is not a missing feature, but a bug. Either in MySQL because it is not returning consistent error codes (local vs. remote) or DBD::mysql in not checking both error codes.
[27 Aug 2008 6:54] Valeriy Kravchuk
Verified by code review with 4.007 also. I still think it is more like a feature request.
[8 Jun 2009 22:55] liz drachnik
Hello Wouter - 

In order for us to continue the process of reviewing your contribution to MySQL - We need you to review and sign the Sun|MySQL contributor agreement (the "SCA")

The process is explained here: 
http://forge.mysql.com/wiki/Sun_Contributor_Agreement

Getting a signed/approved SCA on file will help us facilitate your contribution-- this one, and others in the future.

Thank you ! 

Liz Drachnik  - Program Manager - MySQL
[2 Oct 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[4 Nov 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[20 Feb 2013 22:47] Sveta Smirnova
Thank you for the report.

We don't work on DBD::mysql bugs anymore. All its bugs should go to CPAN: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql

I re-submitted your report to https://rt.cpan.org/Public/Bug/Display.html?id=83495
Please subscribe to the new report on CPAN and work with DBD::mysql developers in case if they need additional details.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.045 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.