From 17df160c2a269ed7777cf05851f9aaf4f3a463d7 Mon Sep 17 00:00:00 2001 From: Olli-Antti Kivilahti Date: Fri, 24 Jul 2015 11:15:24 +0300 Subject: [PATCH] Bug 14600 - MySQL server has gone away After introducing the Koha::Object and DBIx features: We have had serious issues with MySQL/MariaDB losing the DB connection, throwing just the cryptic "MySQL server has gone away" error, after ~5 minutes of connection inactivity. This seriously affects all daemonized Perl-processes we use with C4::Context, like the SIP2-server and Plack and the Mojolicious REST API. DBIx-layer of DB handle negotiation works fine detecting a stale connection, but the new connection generation is delegated to DBI (this is inefficient duplication). DBI-layer doesn't detect a stale connection and for us (LXC v1.03, Ubuntu 12/14.04) the "mysql_auto_reconnect"-parameter doesn't work. Our improvement targets the C4::Context->dbh(); <{db_driver}) && $context->{db_driver} eq 'mysql' && $context->{"dbh"} ) { return $context->{"dbh"}; } elsif ( defined($context->{"dbh"}) && $context->{"dbh"}->ping() ) { return $context->{"dbh"}; } SNAP>> if ( defined($context->{db_driver}) && $context->{db_driver} eq 'mysql' && $context->{"dbh"} ) { This patch adds a timer to ping every 4 minutes to see if the connection is still alive, regardless of the DB we use (MySQL/MariaDB vs ...). Pinging every 4 minutes shouldn't make a performance dent, but makes it possible for us to operate our SIP2-server without restarting it every 5 minutes. --- C4/Context.pm | 17 +++++++++++++---- 1 file changed, 13 insertions(+), 4 deletions(-) diff --git a/C4/Context.pm b/C4/Context.pm index 660a489..9a92d95 100644 --- a/C4/Context.pm +++ b/C4/Context.pm @@ -818,15 +818,24 @@ sub dbh my $sth; unless ( $params->{new} ) { - if ( defined($context->{db_driver}) && $context->{db_driver} eq 'mysql' && $context->{"dbh"} ) { - return $context->{"dbh"}; - } elsif ( defined($context->{"dbh"}) && $context->{"dbh"}->ping() ) { - return $context->{"dbh"}; + if (defined($context->{"dbh"})) { + #If there has been more than 4 minutes since we got a fresh and working DBI-connection, make sure it is still alive + #The mysql_auto_reconnect, parameter doesn't seem to work properly in all environments. + if ($context->{"dbhCreatedTimestamp"} < (time()-240)) { + if ($context->{"dbh"}->ping() eq '0') { + return $context->{"dbh"}; + $context->{"dbhCreatedTimestamp"} = time(); #I hope we now really have a working connection, since ping() is occasionally iffy. + } + } + else { + return $context->{"dbh"}; + } } } # No database handle or it died . Create one. $context->{"dbh"} = &_new_dbh(); + $context->{"dbhCreatedTimestamp"} = time(); return $context->{"dbh"}; } -- 1.9.1