Bug 10611: Use mysql_auto_reconnect instead of ping

DBD::Mysql provides a mysql_auto_reconnect flag. Using it avoids
the time required to do a $dbh->ping().

Benchmarks:

use Modern::Perl;
use C4::Context;
for ( 1 .. 1000 ) {
    $dbh = C4::Context->dbh;
}

* without this patch on a local DB:
perl t.pl  0,49s user 0,02s system 98% cpu 0,525 total
* without this patch on a remote DB:
perl t.pl  0,52s user 0,05s system 1% cpu 37,358 total
* with this patch on a local DB:
perl t.pl  0,46s user 0,04s system 99% cpu 0,509 total
* with this patch on a remote DB:
perl t.pl  0,49s user 0,02s system 56% cpu 0,892 total

Testing the auto reconnect:
use Modern::Perl;
use C4::Context;
my $ping = $dbh->ping;
say $ping;
$dbh->disconnect;
$ping = $dbh->ping;
say $ping;

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
Comment: Real improvement. No koha-qa errors

prove t/db_dependent/Circulation_issuingrules.t produces no error
prove t/db_dependent/Context.t produces no error

Test
1) dumped Koha DB, load it on a non-local server
2) run sample script whit and without patch, local and remote

use Modern::Perl;
use C4::Context;
for ( 1 .. 100000 ) {
    my $dbh = C4::Context->dbh;
}

Main difference I note is with remote server
a) without patch
real    0m16.357s
user    0m2.592s
sys     0m2.132s

b) with patch
real    0m0.259s
user    0m0.240s
sys     0m0.012s

I think this could be good for DBs placed on
remote servers

Bug 10611: add a "new" parameter to C4::Context->dbh

When dbh->disconnect is called and the mysql_auto_reconnect flag is set,
the dbh is not recreated: the old one is used.

Adding a new flag, we can now force the C4::Context->dbh method to
return a new dbh.

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>

Bug 10611: Followup: remove useless calls to dbh->disconnect

These 3 calls to disconnect are done at the end of the script, they are
useless.

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
Signed-off-by: Paul Poulain <paul.poulain@biblibre.com>
Signed-off-by: Galen Charlton <gmc@esilibrary.com>

diff --git a/C4/Context.pm b/C4/Context.pm
index bf87efa..c3b5685 100644 (file)
--- a/C4/Context.pm
+++ b/C4/Context.pm
@@ -793,11 +793,12 @@ sub _new_Zconn {
 # Internal helper function (not a method!). This creates a new
 # database connection from the data given in the current context, and
 # returns it.
+our $db_driver = 'mysql';
 sub _new_dbh
 {
 
     ## $context
-    ## correct name for db_schme        
+    ## correct name for db_scheme
     my $db_driver = db_scheme2dbi($context->config("db_scheme"));
 
     my $db_name   = $context->config("database");
@@ -823,6 +824,10 @@ sub _new_dbh
         $dbh->{RaiseError} = 0;
     }
 
+    if ( $db_driver eq 'mysql' ) {
+        $dbh->{mysql_auto_reconnect} = 1;
+    }
+
        my $tz = $ENV{TZ};
     if ( $db_driver eq 'mysql' ) { 
         # Koha 3.0 is utf-8, so force utf8 communication between mySQL and koha, whatever the mysql default config.
@@ -857,10 +862,15 @@ possibly C<&set_dbh>.
 sub dbh
 {
     my $self = shift;
+    my $params = shift;
     my $sth;
 
-    if (defined($context->{"dbh"}) && $context->{"dbh"}->ping()) {
-       return $context->{"dbh"};
+    unless ( $params->{new} ) {
+        if ( defined $db_driver && $db_driver eq 'mysql' && $context->{"dbh"} ) {
+            return $context->{"dbh"};
+        } elsif ( defined $db_driver && defined($context->{"dbh"}) && $context->{"dbh"}->ping()) {
+            return $context->{"dbh"};
+        }
     }
 
     # No database handle or it died . Create one.

diff --git a/installer/install.pl b/installer/install.pl
index 39ba0fe..c9c1361 100755 (executable)
--- a/installer/install.pl
+++ b/installer/install.pl
@@ -382,8 +382,6 @@ elsif ( $step && $step == 3 ) {
                                 );
             }
         }
-
-        $dbh->disconnect;
     }
 }
 else {

diff --git a/misc/cronjobs/j2a.pl b/misc/cronjobs/j2a.pl
index 87edaa4..75d4da1 100755 (executable)
--- a/misc/cronjobs/j2a.pl
+++ b/misc/cronjobs/j2a.pl
@@ -234,4 +234,3 @@ if ( not $noaction) {
        }
        $sth->finish(  );
 }
-$dbh->disconnect();

diff --git a/sms/sms_listen.pl b/sms/sms_listen.pl
index b36b25d..a7420d9 100755 (executable)
--- a/sms/sms_listen.pl
+++ b/sms/sms_listen.pl
@@ -108,7 +108,6 @@ foreach my $user(@phones){
        }
        print $reply;
 }
-$dbh->disconnect;
 
 sub send_message {
        my ($mes,$message,$smsid)=@_;

diff --git a/t/db_dependent/Context.t b/t/db_dependent/Context.t
index d473854..d9f6f32 100755 (executable)
--- a/t/db_dependent/Context.t
+++ b/t/db_dependent/Context.t
@@ -44,9 +44,6 @@ ok($config = $koha->{config}, 'Getting $koha->{config} ');
 
 diag "Testing syspref caching.";
 
-my $dbh = C4::Context->dbh;
-$dbh->disconnect;
-
 my $module = new Test::MockModule('C4::Context');
 $module->mock(
     '_new_dbh',
@@ -58,7 +55,7 @@ $module->mock(
 );
 
 my $history;
-$dbh = C4::Context->dbh;
+$dbh = C4::Context->dbh({ new => 1 });
 
 $dbh->{mock_add_resultset} = [ ['value'], ['thing1'] ];
 $dbh->{mock_add_resultset} = [ ['value'], ['thing2'] ];