Seitenanfang

mySQL mysql_auto_reconnect: Timezone issues

Dieser Post wurde aus meiner alten WordPress-Installation importiert. Sollte es Darstellungsprobleme, falsche Links oder fehlende Bilder geben, bitte einfach hier einen Kommentar hinterlassen. Danke.


mySQL clients tend to loose their connections from time to time and DBD::mysql could reestablish the connection automatically if it's lost using the mysql_auto_reconnect DSN connection string parameter - creating other problems.

International projects usually provide services to different countries - in different timezones. There is a easy solution using SET time_zone, but any connection variable is lost once the connection is terminated because they're stored on the server for the running connection only.

Choose one: Either mySQL has gone away messages crashing your script (or breaking followup stuff) or a wrong timezone for DATETIME columns and NOW() calls.

I tried to get a DBI Callback on every automatic reconnect but failed - they're done somewhere within DBD::mySQL or the mySQL client libs and even DBI doesn't notice them (proofed using Devel::Trace: No Perl code is running while reconnecting). Another try was using private DBI variables - but they survive the reconnect.

Here is a short demonstration of the problem:

#!/usr/bin/perl -l

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=test;host=test;user=test;password=test");$dbh->{mysql_auto_reconnect} = 1;

$dbh->do("SET time_zone=\"Europe/Helsinki\""); # Given your default timezone isn't Helsinkiprint $dbh->selectrow_array('SELECT @@time_zone');print $dbh->selectrow_array('SELECT NOW()');

print $dbh->do("KILL connection_id()"); # Kill my own connectionprint $dbh->selectrow_array('SELECT @@time_zone'); # Including auto-reconnectprint $dbh->selectrow_array('SELECT NOW()');

The DBD::mysql has a hidden solution, it's not mentioned within the reconnect parts of the DBD::mySQL POD but on the same page: mysql_init_command.

DBD::mysql runs the mysql_init_command (part of the DSN string) just after connecting and on every automatic reconnect:

#!/usr/bin/perl -l

use DBI;

my $dbh = DBI->connect('DBI:mysql:database=test;host=test;user=test;password=test;mysql_init_command=SET time_zone="Europe/Helsinki"');$dbh->{mysql_auto_reconnect} = 1;

$dbh->do("SET time_zone=\"Europe/Helsinki\""); # Given your default timezone isn't Helsinkiprint $dbh->selectrow_array('SELECT @@time_zone');print $dbh->selectrow_array('SELECT NOW()');

print $dbh->do("KILL connection_id()"); # Kill my own connectionprint $dbh->selectrow_array('SELECT @@time_zone'); # Including auto-reconnectprint $dbh->selectrow_array('SELECT NOW()');

 

2 Kommentare. Schreib was dazu

  1. Peter (Stig) Edwards

    Good suggestion to mention mysql_init_command within the reconnect parts of the DBD::mysql POD. Patches/suggestions can be sent to https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql and/or the mailing list / maintainer https://github.com/CaptTofu/DBD-mysql/blob/master/README#L126

  2. Sebastian

    "Fixing" it now might break with many applications which assume the current behavior and I don't think that a new DBI callback would be that easy to set up, but I'll open a ticket for the issue.

Schreib was dazu

Die folgenden HTML-Tags sind erlaubt:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>