| 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: | |
| 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 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.
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;