What are the advantages of using DBIx::Connector over simply setting mysql_auto_reconnect to 1?

Question

When dealing with persistant MySQL connections, the one problem is that they get dropped after a certain timeout (usually 28800 seconds). DBIx::Connector seems to do the job of automatically reconnecting to a dropped connection, but it adds more Perl code to each SQL statement which can get annoying. For example instead of:

    $dbh->do('DROP DATABASE stackoverflow');

One has to say:

    $conn->run(
        fixup => sub {
            my $dbh = shift;
            $dbh->do('DROP DATABASE stackoverflow');
        }
    );

Suppose one does not need transactions. Why would one want to use DBIx::Connector instead of passing $dbh->{mysql_auto_reconnect} = 1, which also works well?

Nerti

With DBIx::Connector I can write a web application to run within a framework such as Mojolicious, and not have to care whether the application will run on a pre-forking server such as Hypnotoad or not. It just takes the worry out of how the application may be deployed. – Collaborationist

Answer 1

1. DBIx::Connector's stated goal is to provide a fork- and thread-safe implementation of DBI's connect_cached(). So you're almost asking an apples/oranges question.

   However, DBIx::Connector does also reconnect if the connection is lost, when it is running in either its ping or fixup Connection Modes. Note that the default is the no_ping mode, which apparently does not attempt reconnection.

2. DBIx::Connector will work with any DB backend, not just MySQL.

All said... if you're using MySQL, and don't care about the other advantages of DBIx::Connector (because you never fork or use threads, for instance), then mysql_auto_reconnect is probably perfect for you.

Urinary

In addition to reconnecting, I also wanted to use DBIx::Connector for its savepoints. Unfortunately, the example at CPAN dies with an error for me, although MySQL using InnoDB supports savepoints. DBIx::Connector was recommended to me at #perl as the solution for the "Database has gone away" error. – Nerti

Minimalist—I suggest you post the code you tried and the error you got. See also t/svp_live.t for working savepoint code (I did test it with InnoDB when I wrote it, IIRC). – Figurative

@Theory: The MySQL code in t/svp_live.t was commented out. I uncommented it and (with a small modification) made it prove on my system. It is possible that I have not use it properly the first time. Thank you for your help. – Nerti