AutoCommit isn't restored after reconnect when starting a transaction [rt.cpan.org #105382]

[mbeijen]

Migrated from rt.cpan.org#105382 (status was 'open')

Requestors:

• j.raspass@cv-library.co.uk

From jraspass@gmail.com on 2015-06-19 16:02:42:

If the first command on a disconnected handle with auto_reconnect set is a begin_work it will be die like so:

perl -MDBI -e 'my$d=DBI->connect("DBI:mysql:host=xxx","xxx","xxx",{mysql_auto_reconnect=>1});$d->disconnect;$d->begin_work;$d->commit'
DBD::mysql::db begin_work failed: Turning off AutoCommit failed at -e line 1.

However if you do anything else before starting the transaction then all is well:

perl -MDBI -e 'my$d=DBI->connect("DBI:mysql:host=master.db.cv-library.co.uk","root","defiant1764",{mysql_auto_reconnect=>1});$d->disconnect;$d->do("SELECT 1");$d->begin_work;$d->commit'

Tested on perl 5.14.2 with DBD::mysql 4.031

From jraspass@gmail.com on 2015-06-19 17:21:20:

Ah yes, accidental leakage of the test DB's credentials in that bug report, those credentials have now been retired. Whoops :-P

From pjcj@cpan.org on 2017-08-03 14:43:24:

Do we have any progress on this?

The problem still exists in 4.041.  It doesn't look like there was in fix in 4.042, but can anyone confirm that?

I appreciate that this may not be the most important development problem right now but I can confirm, fortunately, that the simple workaround seems to work well.  Thanks, James, for including it.

From capttofu@cpan.org on 2017-08-14 18:08:40:

Yes, thank you for reminding me. I'll be looking at this.

[nuxwin]

@CaptTofu

Such a big issue as this one not addressed yet? How can it be possible? Does doing something like:

eval {
    local $dbh->{'AutoCommit'} = 0;
};
...

could workaround the issue?

[nuxwin]

@CaptTofu

My previous workaround doesn't work. The following monkey patch work, even through that not the best to do...

    {
        no warnings qw/ once redefine  /;

        *DBD::_::db::begin_work = sub {
            my $dbh = shift;
            return $dbh->set_err($DBI::stderr, "Already in a transaction")
              unless $dbh->FETCH('AutoCommit');
            $dbh->ping(); # Make sure that connection is alive (mysql_auto_reconnect)
            $dbh->STORE('AutoCommit', 0); # will croak if driver doesn't support it
            $dbh->STORE('BegunWork',  1); # trigger post commit/rollback action
            return 1;
        };
    }

[pali]

Can you check if this problem is still present in DBD::MariaDB? https://metacpan.org/release/PALI/DBD-MariaDB-0.90_01

[nuxwin]

@pali

Of course, I'll install it and let you know.