NAME

DBIx::RetryOverDisconnects - DBI wrapper that helps to deal with databases connection problems

SYNOPSIS

    use DBIx::RetryOverDisconnects;
    my $dbh = DBIx::RetryOverDisconnects->connect($dsn, $user, $pass, {
        ReconnectRetries  => 5,
        ReconnectInterval => 1,
        ReconnectTimeout  => 3,
        TxnRetries        => 3,
    });

    #All of this 3 methods will be successfuly completed despite of
    #possible connection losses except for sql errors.
    $dbh->do("...");
    my $sth = $dbh->prepare("...");
    $sth->execute(...);

    #other functionality that DBI supports

    $dbh->begin_work;
    my $ok = eval {
        $dbh->do("...");
        #...code
        $dbh->do("...");
        $dbh->commit;
        1;
    };

    unless ($ok) {
        if ($dbh->is_trans_disconnect) {
            #connection to database has been lost during transaction
            #$dbh has been already reconnected to database as we felt here.
            #It is now safe to retry the transaction from the beginning.
        }
        elsif($dbh->is_fatal_disconnect) {
            #database is down and reconnect retries limit is reached
        }
        elsif($dbh->is_sql_error) {
            #all other DBI's errors that are not related to connection problems
            $dbh->rollback;
            #deal with sql error;
        }
    }

    #or simply run the perl code in transaction mode.
    $dbh->txn_do(sub {
        $dbh->do("...");
        #...code
        $dbh->do("...");
    });
    #successful completion is guaranteed except for sql or perl errors.

DESCRIPTION

This wrapper intercepts all requests. If some request fails this module detects the reason of fail. If the reason was database connection problem then wrapper would automatically reconnect and restart the query. Otherwise it would rethrow the exception.

If you are not in transaction then you can just do

    $dbh->do('...');
    $sth->execute(...);

This might have 2 fatal cases:

SQL error (a good reason to die).
Reconnect retries limit reached (database is completely down or network failure).

For example, if the connection to database were lost during 'execute' call, the module would reconnect to database with a timeout 'ReconnectTimeout'. If reconnect failed it would reconnect again 'ReconnectRetries' times with 'ReconnectInterval' interval (in seconds). If reconnect retries limit was reached it would raise an error and $dbh->is_fatal_disconnect would be true.

If you are in transaction then even DB disconnect will raise an error. But you can check $dbh->is_trans_disconnect and restart the transaction if it is 'true'. Other possible errors are the same: sql error and reconnect limit.

The recommended way of using transactions is

    $dbh->txn_do($code_ref);

because 'txn_do' would automatically restart the transaction if it was failed because of database disconnect. The transaction can be restarted at most 'TxnRetries' times. If 'TxnRetries' limit was reached then error would be raised and $dbh->is_fatal_trans_disconnect set to true. Other error cases are the same as above.

'txn_do' would try do to rollback if there was a perl or sql error (no rollback needed when you loose connection to database: DB server already has done it). Rollback is successul when $@ =~ /Rollback OK/;

Note: For the perfomance reasons, DBI attribute 'RaiseError' is always set to 'true'.

METHODS

Class methods

connect

    DBIx::RetryOverDisconnects->connect($dsn, $user, $pass, $attrs);

All parameters are passed directly to DBI. Additional $attrs are

ReconnectRetries - How many times DBIx::RetryOverDisconnects will try to reconnect to database. Default to 5.
ReconnectInterval - Interval (in seconds) between reconnect attemps. Default to 2.
ReconnectTimeout - Timeout (in seconds) for waiting the database to accept connection (because sometimes DBI->connect can block your application). Default to 5.
TxnRetries - How many times the wrapper would try to restart transaction if it was failed because of database connection problems. Default to 4.

Database handle object methods

set_callback

    $dbh->set_callback(afterReconnect => $code_ref);

Set callbacks for some events. Currently only afterReconnect is supported. It is called after every successful reconnect to database.

is_fatal_trans_disconnect

Returns 'true' if last failed operation was txn_do and TxnRetries limit was reached.

is_trans_disconnect

Return 'true' if last failed operation was a transaction and it could be restarted. The database handle was successfuly reconnected again.

is_fatal_disconnect

Return 'true' if reconnect retries limit has been reached. In this case the database handle is not connected.

is_sql_error

Return 'true' if query failed because of some other reason, not related to database connection problems. See $DBI::errstr for details.

ping

Always returns 'true' or dies ($dbh->is_fatal_disconnect = true). Does original DBI::db's ping and if it is false then it reconnects.

txn_do

    $dbh->txn_do($code_ref);

Executes $code_ref in a transaction environment. Automatically reconnects and restarts the transaction in any case of connection problems. 'txn_do' is able to die with one of the is_fatal_disconnect, is_sql_error, is_fatal_trans_disconnect set to true.

In most cases you don't need to wrap it into 'eval' because all of this exceptions are subject to die (database completely down, network down, bussiness logic error, etc).

OVERLOADED METHODS

Database handle object methods

prepare, do, statistics_info, begin_work, commit, rollback, selectrow_array, selectrow_arrayref, selectall_arrayref, selectall_hashref

Database statement object methods

execute, execute_array, execute_for_fetch

DATABASE SUPPORT

Currently PostgreSQL, MySQL, Oracle and SQLite are supported.

AUTHOR

Pronin Oleg <syber@cpan.org>

LICENSE

You may distribute this code under the same terms as Perl itself.