| Subject: | fetch*() on lost connection wrongly reports "fetch() without execute()" instead of "MySQL server has gone away" |
If the perl program's connection to the MySQL server gets timed out and closed by the server then a subsequent prepare() + execute() + fetchrow_array() + finish() wrongly reports error 19 ("fetch() without execute()") instead of 2006 ("MySQL server has gone away"). (And, in fact, the error comes from the fetchrow_array() function, *after* it has (apparently) done a successful execute()!)
The problem can be easily reproduced with the attached perl program after setting "wait_timeout = 5" in the [mysqld] section of the appropriate my.ini configuration file (and restarting the MySQL server).
The program establishes a connection to the 'mysql' database, waits for 7 seconds to be sure the connection gets timed out, then prepares, excutes, fetches and finishes a simple statement ('SELECT 1'). I get the following output every time:
Connecting...
Sleeping...
Preparing...
Executing...
Fetching...
Error 19: fetch() without execute()
The other three fetch*() functions commented out in the program behave the same way, but strangely the three select*() functions in a separate block in the program apparently succeed with no error at all if you adjust the program to run through that block of code instead.
Note that I have previously had a similar issue with "fetch() without execute()" being wrongly reported instead of the expected lost connection error. For example, see the following mailing list archive thread from 11 years ago in which I reported the same problem:
http://grokbase.com/t/mysql/perl/0251jk3kpt/dbd-mysql-fetch-without-execute-error
That discussion resulted in a fix for the problem which I had then, and the fix which it put into dbdimp.c is indeed still there around line 3273. I wonder if there is a similar problem lurking somewhere else in that file?
| Subject: | dbtest.pl |
use strict;
use warnings;
use DBI;
my($dbh, $sth);
my $ok = eval {
print "Connecting...\n";
$dbh = DBI->connect('dbi:mysql:database=mysql;mysql_client_found_rows=1',
'root', undef, {PrintError => 0, RaiseError => 1, AutoCommit => 0});
print "Sleeping...\n";
sleep 7;
my $sql = 'SELECT 1';
if (1) {
print "Preparing...\n";
$sth = $dbh->prepare($sql);
print "Executing...\n";
$sth->execute();
print "Fetching...\n";
my @res = $sth->fetchrow_array();
# my $res = $sth->fetchrow_arrayref();
# my $res = $sth->fetchrow_hashref();
# my $res = $sth->fetchall_arrayref();
print "Finishing...\n";
$sth->finish();
$sth = undef;
}
else {
print "Selecting...\n";
my @res = $dbh->selectrow_array($sql);
# my $res = $dbh->selectall_arrayref($sql);
# my $res = $dbh->selectcol_arrayref($sql);
}
print "Disconnecting...\n";
$dbh->disconnect();
$dbh = undef;
1;
};
if (not $ok) {
printf "Error %d: %s\n", $DBI::err, $DBI::errstr;
eval { $sth->finish(); } if defined $sth;
eval { $dbh->disconnect(); } if defined $dbh;
}