DBIx::something that can restore session variables on reconnection?

Question

Ordinary DBI::db handler will lost all database session settings that was made using $dbh->do('SET variable_name=value').

Is there any DBIx::* class/package or so that provides method like "set_session" to set session variables and can restore this variables after detection of connection lost (connection timeout in 90% of real cases) ?

It may looks like this:

# inside the user code:
$dbh->set(variable => 'string', yet_another_variable => 42)

# inside the DBIx::* package:
sub reconnect {
# ...
  while (my ($var, $val) = each %{$self->saved_vars}) {
    $self->dbh->do("SET $var=?", {}, $val)
  }
# ...
}

It is recommended to use a connection manager like DBIx::Connector or something from the Mojo::Pg family as it will reconnect when needed and when forked - you can share the connector object without worry, and retrieve handles when needed. When using these you can use the same DBI callbacks or the connection event to set up each new connection. — Grinnz
@Grinnz , what about using DBIx::SQLEngine instead of DBIx::Connector. It seems, the reconnection implemented in DBIx::SQLEngine too, but it is simpler to use than Connector — drvtiny
It looks a lot more complicated to me, and quite a bit older. If you want an ORM that can also serve as the connection manager, the ideal choice is DBIx::Class. — Grinnz

simbabque simbabque · Accepted Answer · 2019-06-03T16:26:42

DBI supports something called Callbacks. I can't link to this bit of the doc as the section is quite long, so here it is verbatim.

A more common application for callbacks is setting connection state only when a new connection is made (by connect() or connect_cached()). Adding a callback to the connected method (when using connect) or via connect_cached.connected (when useing connect_cached()>) makes this easy. The connected() method is a no-op by default (unless you subclass the DBI and change it). The DBI calls it to indicate that a new connection has been made and the connection attributes have all been set. You can give it a bit of added functionality by applying a callback to it. For example, to make sure that MySQL understands your application's ANSI-compliant SQL, set it up like so:
my $dbh = DBI->connect($dsn, $username, $auth, {
    Callbacks => {
        connected => sub {
            shift->do(q{
                SET SESSION sql_mode='ansi,strict_trans_tables,no_auto_value_on_zero';
            });
            return;
        },
    }
});

This is your exact use-case I believe. Do this instead of running your own code after you've connected.

DBIx::something that can restore session variables on reconnection?

1 Answers