DBI mysql_auto_reconnect does not work with mysql_server_prepare when queries are executed via statement handling
The test case below sets up a connection with mysql_auto_reconnect, prepares a statement handle, kills the connection, and tries to use the handle.
When it is executed normally, the handle works after auto-reconnect:
|
Normal protocol
|
RESULT: 2019-08-01 21:43:28.919411
|
DBD::mysql::db do failed: Connection was killed [for Statement "KILL CONNECTION_ID()"] at ./1.pl line 31.
|
RESULT: 2019-08-01 21:43:28.920190
|
But with --ps-protocol, it doesn't:
|
--ps-protocol
|
RESULT: 2019-08-01 21:43:23.424524
|
DBD::mysql::db do failed: Connection was killed [for Statement "KILL CONNECTION_ID()"] at ./1.pl line 31.
|
DBD::mysql::st execute failed: Lost connection to MySQL server during query [for Statement "SELECT NOW(6)"] at ./1.pl line 33.
|
DBD::mysql::st fetchrow_arrayref failed: fetch() without execute() [for Statement "SELECT NOW(6)"] at ./1.pl line 34.
|
RESULT:
|
Reproducible with all of 5.5-10.4 servers.
|
Test case
|
use DBI;
|
use Getopt::Long;
|
|
use strict;
|
|
my ($dbh, $sth, $r);
|
my $port= 3306;
|
my $user= 'root';
|
my $pass= "";
|
my $ps_protocol= 0;
|
|
my $opt_result = GetOptions(
|
'port=i' => \$port,
|
'pass=s' => \$pass,
|
'user=s' => \$user,
|
'ps-protocol' => \$ps_protocol,
|
);
|
|
$dbh= DBI->connect(
|
"dbi:mysql:host=127.0.0.1:port=$port:database=test",
|
$user,
|
$pass,
|
{ mysql_auto_reconnect => 1, ShowErrorStatement => 1, mysql_server_prepare => $ps_protocol }
|
);
|
|
$sth = $dbh->prepare('SELECT NOW(6)');
|
$sth->execute();
|
$r = $sth->fetchrow_arrayref();
|
print "RESULT: $r->[0]\n";
|
|
$dbh->do('KILL CONNECTION_ID()');
|
|
$sth->execute();
|
$r = $sth->fetchrow_arrayref();
|
print "RESULT: $r->[0]\n";
|
Different error code after killing connection in normal protocol vs PS protocol
The MTR test below produces 2006: MySQL server has gone away upon 2nd SELECT in normal mode, and 2013: Lost connection to MySQL server during query with --ps-protocol. The difference is only reproducible on 10.2+.
SELECT 1;
|
--error 1927,1317
|
KILL CONNECTION_ID();
|
SELECT 2;
|
|