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.

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()');

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()');