Bug 14600 - MySQL server has gone away
Summary: MySQL server has gone away
Status: RESOLVED DUPLICATE of bug 14778
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Galen Charlton
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-07-24 13:56 UTC by Olli-Antti Kivilahti
Modified: 2017-10-23 16:53 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 14600 - MySQL server has gone away (2.97 KB, patch)
2015-07-24 13:57 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 14600 - MySQL server has gone away (2.97 KB, patch)
2015-07-31 10:31 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 14600 - MySQL server has gone away - Vol2 (1.03 KB, patch)
2017-10-23 16:28 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Olli-Antti Kivilahti 2015-07-24 13:56:00 UTC
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();

<<SNIP
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"};
}
SNAP>>

#This prevents the $dbh->ping() from ever getting called:
if ( defined($context->{db_driver}) && $context->{db_driver} eq 'mysql' && $context->{"dbh"} ) {
#because $context->{dbh} is true even if the connection is dead.
#Obviously this relies on the "mysql_auto_reconnect"-parameter which doesn't
#work.

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.
Comment 1 Olli-Antti Kivilahti 2015-07-24 13:57:26 UTC Comment hidden (obsolete)
Comment 2 Fridolin SOMERS 2015-07-29 13:32:06 UTC
We had those logs workings with perl persistence.

Please see Bug 14375.
Comment 3 Olli-Antti Kivilahti 2015-07-31 10:31:58 UTC
Created attachment 41248 [details] [review]
Bug 14600 - MySQL server has gone away
Comment 4 Jonathan Druart 2015-11-11 13:08:44 UTC
Olli, with bug 14778, this issue should be fixed.
Please reopen if it persists after 3.22.

*** This bug has been marked as a duplicate of bug 14778 ***
Comment 5 Olli-Antti Kivilahti 2017-10-23 16:28:27 UTC
Created attachment 68424 [details] [review]
Bug 14600 - MySQL server has gone away - Vol2

This bug never ceases to relent.
When running REST API tests under debugger.

    perl -d t/db_dependent/api/v1/holds.t

MySQL server is getting away.

This fix removed by the community in

    Buug 14778: Make sure the dbh returned is checked by DBIC

needed to be brought back to life.
Comment 6 Jonathan Druart 2017-10-23 16:53:47 UTC
(In reply to Olli-Antti Kivilahti from comment #5)
> Created attachment 68424 [details] [review] [review]
> Bug 14600 - MySQL server has gone away - Vol2
> 
> This bug never ceases to relent.
> When running REST API tests under debugger.
> 
>     perl -d t/db_dependent/api/v1/holds.t
> 
> MySQL server is getting away.
> 
> This fix removed by the community in
> 
>     Buug 14778: Make sure the dbh returned is checked by DBIC
> 
> needed to be brought back to life.

Hi Olli,
Did you benchmark this change? See bug 14778 comment 9.