DBD::mysql::st execute failed: MySQL server has gone away

[gtsafas]

Hello,

I used to get this issue on another server which was fixed by setting mysql_auto_reconnect => 0.

I am now getting this on an additional server. I tried this setting with no such luck.

Here is how I am using the code
https://gist.github.com/1219198

DBD::mysql is up to date (4.020).
DBIx::Connector is up to date (0.46).

I saw theres a 0.47 version here on github but I am using cpan to update.

Are there any suggestions on how I can fix this?

[theory]

I honestly have no idea, since I don't use MySQL. I think someone who knows MySQL well will have to provide some feedback…

[theory]

I asked Ask. He said:

isn't that just the usual connection timed out thing?

connection idle times out (or for any other reason), client tries to use it again and gets unpleasantly surprised.

Dunno if that helps. But someone more familiar with MySQL than I will have to diagnose this, since I don't use it. Happy to accept a patch to fix it if it's DBIx::Connector that needs to be fixed (or needs to work around some other "fix").

[theory]

No more complaints. Issue resolved?

[billforward-alex]

encountering this issue. I checked MySQL's processlist to find the connection that DBIx::Connector made, kill it, and then attempt to do a query using the killed DBIx connection (in fixup mode). Instead of auto-reconnecting, I encounter:

DBD::mysql::st execute failed: MySQL server has gone away

[theory]

Likely a MySQL (or DBD::mysql) expert will have to weigh in. I don't use it. :-(

[billforward-alex]

Worth knowing that:

• DBD::mysql provides a MySQL-specific connection option — mysql_auto_reconnect — which is enabled by default
• auto-reconnect is not implemented when connection is in AutoCommit mode
• MySQL will disconnect you if you have a timeout (for example if I leave for lunch [not sure whether this is caused by time, or by sleeping computer] or if I kill the connection server-side)

http://search.cpan.org/~michielb/DBD-mysql-4.043/lib/DBD/mysql.pm

mysql_auto_reconnect
This attribute determines whether DBD::mysql will automatically reconnect to mysql if the connection be lost. This feature defaults to off; however, if either the GATEWAY_INTERFACE or MOD_PERL environment variable is set, DBD::mysql will turn mysql_auto_reconnect on. Setting mysql_auto_reconnect to on is not advised if 'lock tables' is used because if DBD::mysql reconnect to mysql all table locks will be lost. This attribute is ignored when AutoCommit is turned off, and when AutoCommit is turned off, DBD::mysql will not automatically reconnect to the server.

DBD::mysql has a "reconnect" feature that handles the so-called MySQL "morning bug": If the server has disconnected, most probably due to a timeout, then by default the driver will reconnect and attempt to execute the same SQL statement again. However, this behaviour is disabled when AutoCommit is off: Otherwise the transaction state would be completely unpredictable after a reconnect.

The "reconnect" feature of DBD::mysql can be toggled by using the mysql_auto_reconnect attribute. This behaviour should be turned off in code that uses LOCK TABLE because if the database server time out and DBD::mysql reconnect, table locks will be lost without any indication of such loss.

[theory]

DBIx::Connector::Driver::mysql turns it off.

[billforward-alex]

I'm surprised by how this is handled though.

My script is something like this:

my $dsn = "DBI:mysql:database=$conf{'db_name'};host=$conf{'db_host'}";
my $conn = DBIx::Connector->new($dsn, $conf{'db_user'}, $conf{'db_passwd'}, {
  RaiseError => 1, # save having to check each method call
  AutoCommit => 0, # we'll handle transactions ourselves
  mysql_server_prepare => 1
});

$conn->txn(fixup => sub {
      my $dbh = shift;

      my $statement = $dbh->prepare(<<SQL);
SELECT *
FROM whatever
WHERE key = ?;
SQL
      
      # other stuff
      });

I've killed the connection, so we see the error as expected:

DBD::mysql::db prepare failed: MySQL server has gone away at <$dbh->prepare(…>.

But I expected that the behaviour of fixup would be that we would try to re-establish the connection, and run the block again.

Instead: it fails, and does not try again. Let's ignore the MySQL-specific part of the problem for now. Why is there no attempt at a retry? I thought one of the main things that DBIx::Connector did, was to attempt at least one retry upon encountering a possibly-recoverable error.

[theory]

It thinks it's still connected. This works:

use v5.20;
use warnings;
use utf8;
use DBIx::Connector;

my $conn = DBIx::Connector->new('dbi:SQLite:', '', '', {
    RaiseError => 1,
    AutoCommit => 1,
});

my $tries = 0;

$conn->run(fixup => sub {
    # Simulate disconnection.
    delete $conn->{_dbh};
    $tries++;
    say "Try $tries";
    die "oops";
});

Output:

Try 1
Try 2
oops at try line 20.

DBIx::Connector determines if a database has disconnected by looking at:

• Does it have a database handle?
• Has the thread ID changed?
• Has the process ID changed?
• Does $dbh->FETCH('Active') return false?
• Does $dbh->ping return false?

One of these tests must have DBIx::Connector thinking that the database has not been disconnected. Maybe see what $dbh->FETCH('Active') shows if you call it yourself?

[billforward-alex]

thanks for the explanation. I'll try and find the guilty check.

[billforward-alex]

Actually, it never seems to enter the connected function at all. I tried this whilst reproducing the error, and also tried it on complete happy path.

use IO::Handle;

sub connected {
    my $self = shift;
    STDOUT->printflush("Checking if seems connected\n");
    return unless $self->_seems_connected;
    STDOUT->printflush("Seems connected. Checking if dbh\n");
    my $dbh = $self->{_dbh} or return;
    STDOUT->printflush("Dbh available. Pinging\n");
    return $self->driver->ping($dbh);
}

None of those lines gets printed.

As a sanity-check: I confirmed that my tracing technique does work (this got printed):

sub _connect {
    my $self = shift;
    STDOUT->printflush("_connect\n");

[theory]

Huh. Try putting some printflush statements in appropriate places around _fixup_run(). It should call connected at line 179. Would be good to know why it doesn't.

[mschout]

Was this ever solved? We are still seeing this issue.

[theory]

Not to my knowledge. Can you try the printflush debugging?

[jlcooper]

Isn't this simply that the MySQL/MariaDB driver prints errors to STDERR by default? Try switching off the PrintError option - e.g.

my $dsn = "DBI:mysql:database=$conf{db_name};host=$conf{db_host}";
my $conn = DBIx::Connector->new( $dsn, $conf{db_user}, $conf{db_passwd}, {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
});