Statement handle side effects of swap_inner_handle?

I'm using swap_inner_handle in a HandleError sub to re-connect
the existing database handle. The trick is to re-connect if
the error warrants it, return false to the caller, which then
allows the caller to re-try the failed operation and keep
going.

Q: Do I need to use $if_active = 3 to re-prepare the cached
statement handles if I use swap_inner_handle?

e.g.

my $dbh = whatever;

my %queryz =
(
foobar => q{select foo from bar where baz = ?},

...
);

...

my $runquery =
sub
{
my $name = shift
or croak ... ;

my $sth = $dbh->prepare_cached( $queryz{$name}, $queryargz )
or croak ... ;

my $data =
do
{
my $d = '';

for( 1..$maxtries )
{
last
if $d = $dbh->selectall_arrayref( $sth, undef, @_ );
}

$d
};
};

The selectall_* calls normally obviate the need for $if_active;
however swapping the database handles might require re-preparing
all of the file handles using

my $sth = $dbh->prepare_cached( $queryz{$name}, $queryargz, 3 )

after etching the database handle's brains with swap_inner_handle;
or setting some magic "this needs to be re-prepared the next pass"
flag on all of the statement handles?

Or is the best way to just

my $kidz = $dbh->CachedKids;

delete @{ $kidz }{ keys %$kidz };

and be done with it?

Or is some similar maintainence done automatically by the
swap_inner_handle?

thanx


Reference:

"swap_inner_handle"
$rc = $h1->swap_inner_handle( $h2 );
$rc = $h1->swap_inner_handle( $h2, $allow_reparent );

Brain transplants for handles. You don't need to know about this
unless you want to become a handle surgeon.

A DBI handle is a reference to a tied hash. A tied hash has an
*inner* hash that actually holds the contents. The
swap_inner_handle() method swaps the inner hashes between two
handles. The $h1 and $h2 handles still point to the same tied
hashes, but what those hashes are tied to has been swapped. In
effect $h1 *becomes* $h2 and vice-versa. This is powerful stuff. Use
with care.

As a small safety measure, the two handles, $h1 and $h2, have to
share the same parent unless $allow_reparent is true.

The swap_inner_handle() method was added in DBI 1.44.

"prepare_cached"
$sth = $dbh->prepare_cached($statement)
$sth = $dbh->prepare_cached($statement, \%attr)
$sth = $dbh->prepare_cached($statement, \%attr, $if_active)

Like "prepare" except that the statement handle returned will be
stored in a hash associated with the $dbh. If another call is made
to "prepare_cached" with the same $statement and %attr parameter
values, then the corresponding cached $sth will be returned without
contacting the database server.

The $if_active parameter lets you adjust the behaviour if an already
cached statement handle is still Active. There are several
alternatives:

0: A warning will be generated, and finish() will be called on the
statement handle before it is returned. This is the default
behaviour if $if_active is not passed.
1: finish() will be called on the statement handle, but the warning
is suppressed.
2: Disables any checking.
3: The existing active statement handle will be removed from the
cache and a new statement handle prepared and cached in its place.
This is the safest option because it doesn't affect the state of the
old handle, it just removes it from the cache. [Added in DBI 1.40]

"CachedKids" (hash ref)
For a database handle, "CachedKids" returns a reference to the cache
(hash) of statement handles created by the "prepare_cached" method.
For a driver handle, returns a reference to the cache (hash) of
database handles created by the "connect_cached" method.



--
Steven Lembark 85-09 90th Street
Workhorse Computing Woodhaven, NY 11421
lembark@wrkhors.com 1 888 359 3508

Re: Statement handle side effects of swap_inner_handle?

On Wed, Jan 25, 2006 at 02:42:18PM -0500, Steven Lembark wrote:
>
> I'm using swap_inner_handle in a HandleError sub to re-connect
> the existing database handle. The trick is to re-connect if
> the error warrants it, return false to the caller, which then
> allows the caller to re-try the failed operation and keep
> going.
>
> Q: Do I need to use $if_active = 3 to re-prepare the cached
> statement handles if I use swap_inner_handle?

The inner handle carries all the DBI info, including CachedKids.
So swap_inner_handle is a simple and total brain transplant.

> my $sth = $dbh->prepare_cached( $queryz{$name}, $queryargz )

> if $d = $dbh->selectall_arrayref( $sth, undef, @_ );

> The selectall_* calls normally obviate the need for $if_active;
> however swapping the database handles might require re-preparing
> all of the file handles using
>
> my $sth = $dbh->prepare_cached( $queryz{$name}, $queryargz, 3 )
>
> after etching the database handle's brains with swap_inner_handle;
> or setting some magic "this needs to be re-prepared the next pass"
> flag on all of the statement handles?
>
> Or is the best way to just
>
> my $kidz = $dbh->CachedKids;
> delete @{ $kidz }{ keys %$kidz };
>
> and be done with it?

Or just $dbh->{CachedKids} = {};

> Or is some similar maintainence done automatically by the
> swap_inner_handle?

swap_inner_handle just does what it says - swaps handles. That's it.

I suspect what you'll need to do (for maximum transparency) is perform a
swap_inner_handle on each of the old dbh's kids to replace the now
defunct sth's with new ones freshly prepared using the new dbh.

If so then it's kind'a handy that the DBI now has a $h->{ChildHandles}
attribute.

If you really want to get fancy you could each check $old_sth->rows and
then fetch that many rows from the new $sth to leave it in the same
'place' (hopefully!) as the original. Just how mad do you want to be?

You're going to have to do some thinking and testing yourself on this
one Steven. You're the first to walk down this particular path.
Should be an interesting journey though!

Tim.

Re: Statement handle side effects of swap_inner_handle?

> The inner handle carries all the DBI info, including CachedKids.
> So swap_inner_handle is a simple and total brain transplant.


Q: Any chance of getting "$sth->clone"?

From what I can see in the doc's, the clone method
is specific to database handles (the statement's clone
would account for cached queries). Matching up the
cached vs. un-cached values is doable, but getting
the bound variables (especially lexicals) correct
is going to seriously difficult.


>> Or is some similar maintainence done automatically by the
>> swap_inner_handle?
>
> swap_inner_handle just does what it says - swaps handles. That's it.
>
> I suspect what you'll need to do (for maximum transparency) is perform a
> swap_inner_handle on each of the old dbh's kids to replace the now
> defunct sth's with new ones freshly prepared using the new dbh.

The trick will be matching the values of CachedKids
to those in ChildHandles to cache the proper set of
handles:

DB<1> x $dbh->{CachedKids}
0 HASH(0x86900a4)
' select now() ' => DBI::st=HASH(0x868ff24)
empty hash

Otherwise someone using prepare and prepare_cached
on the same sql string would end up with mismatched
statements after the reconnect.


Q: If the cached kids are stored in $new_dbh, wouldn't I
overwrite the statement handles when I performed the
$old_dbh->swap_inner_handle( $new_dbh )?

i.e., wouldn't I get a wholesale duplication via:

# remake all of the old statement handles
# using the new database handle.

$new_dbh->prepare( $_->{Statement} )
for grep { defined } $old_dbh->{ChildHandles};

# replace the database handle wholesale.

$old_dbh->swap_inner_handle( $new_dbh );

(ignoring issues with prepare_cached and bound variables
for a moment)?


Or is it necessary to do something like:

# save the existing statement handles (assuming
# they don't survive the $dbh->swap_inner_handle.

my @old_sth = grep { defined } @{ $old_dbh->{ChildHandles} };

my $old_kidz = $old_dbh->{ CachedKids };

my %cached = reverse %$old_kidz;

# generate a new dbh and install it. after the swap,
# the new kids are empty (based on $new_dbh having
# no prepared statements yet).

my $new_dbh = $old_dbh->clone;

$old_dbh->swap_inner_handle( $new_dbh );

my $new_kidz = $old_dbh->{ CachedKids };

# at this point the swap has wiped out the ChildHandles
# and CachedKids of $old_dbh; now to regenerate them.

for my $old_sth ( @old_sth )
{
my $sql = $old_sth->{ Statement };

my $new_sth = $new_dbh->prepare( $sql );

$old_sth->swap_inner_handle( $new_sth );

# thankfully, this thing is writeable...

$new_kidz->{ $sql } = $new_sth
if $cached{ $old_sth };
}

The only thing this doesn't pull across that I can think
of is the current row state of each handle and the bound
variables.


Regenerating the cached queries only would simply
require saving the keys and running $old_dbh->prepare_cached( $_ )
for keys %$saved_kidz_hash; the messy part is going to
be making sure I have the un-cached ones handled properly.


Q: From what I can see, $sth->{Type} doesn't give me
enough information to tell if a particular sth
is cached or not. Am I missing something?

> If so then it's kind'a handy that the DBI now has a $h->{ChildHandles}
> attribute.
>
> If you really want to get fancy you could each check $old_sth->rows and
> then fetch that many rows from the new $sth to leave it in the same
> 'place' (hopefully!) as the original. Just how mad do you want to be?

Only risk there is accidentally restarting the query-from-hell,
but that could be avoided with a configuration parmeter
to DBIx::Viagra.

Q: Is there any chance of getting a 'seek' to handle this?


It would look sometehing like:

$sth->seek( $sth->rows );

and save me from retrieving the entire list of rows into
core on the local side just to discard them.


I'll leave bound variables until after I've had my morning
coffe.

Thankfully I'm doing this in Perl; Heaven help the
poor slob who tries this in Java...



Aside: How likely to change is dbh->clone as of 1.50?

The "clone" method was added in DBI 1.33. It is very new and likely
to change.

thanx

--
Steven Lembark 85-09 90th Street
Workhorse Computing Woodhaven, NY 11421
lembark@wrkhors.com 1 888 359 3508

Re: Statement handle side effects of swap_inner_handle?

You may want to check the source of DBIx::HA.
I did a lot of that type of work, see below for a sample. Be warned though that I think some of that code could be cleaned up now that we have $h->{ChildHandles}. It's a lifesaver.
Also having the upgraded error handler will allow for cleaner code as well.
I also did not implement going to the proper row as Tim suggested, as I didn't need that functionality.


if (DBIx::HA::_isactivedb ($dsn)) {
($res, $to) = &_execute_with_timeout ($dsn, $sth);
$orig_error_code = $DBI::err;
$orig_error_string = $DBI::errstr;
if ($to) {
# It was a timeout error. The database handle may be in an unstable state, we must clear it.
# To check if the database is still active, we try to connect to it again.
# If that succeeds, then the database is fine. If not, then we need to fail over.
my ($dummy_dsn, $username, $auth, $attrs) = @{$DATABASE::conf{&DBIx::HA::_getdbname($dsn)}->{'active_db' }};
my $newdbh = &DBIx::HA::_connect_with_timeout($dsn, $username, $auth, $attrs);
if (! $newdbh) {
# Ooops. Even a new database connect fails. It's time to reconnect and reexecute
warn "$prefix in execute: execution failed with timeout, database unavailable. Attempting reconnect (with potential failover). Statement: $sql ; dsn: $dsn \n" if (DBIx_HA_DEBUG);
($dsn, $sth, $res) = _reexecute ($dsn, $sql, $sth);
} else {
# the reconnect worked, so the database is fine.
# get rid of the old database handle
warn "$prefix *** TIMEOUT! server busy: $sql ; dsn: $dsn \n" if (DBIx_HA_DEBUG);
$dbh->swap_inner_handle($newdbh);
eval { $newdbh->disconnect; };
undef $newdbh;
}
} elsif (! defined $res) {
# We got an error code from the server upon statement execution.
# We will let the client decide what to do and let it be.
warn "$prefix *** ERROR: $orig_error_code; $orig_error_string \n" if (DBIx_HA_DEBUG);
warn "$prefix in execute: bad sql: $sql ; dsn: $dsn \n" if (DBIx_HA_DEBUG);
}
} else { # current db is not active
eval { $sth->finish; };
($dsn, $sth, $res) = _reexecute ($dsn, $sql, $sth);
}

Tim Bunce wrote:
> On Wed, Jan 25, 2006 at 02:42:18PM -0500, Steven Lembark wrote:
>
>>I'm using swap_inner_handle in a HandleError sub to re-connect
>>the existing database handle. The trick is to re-connect if
>>the error warrants it, return false to the caller, which then
>>allows the caller to re-try the failed operation and keep
>>going.
>>
>>Q: Do I need to use $if_active = 3 to re-prepare the cached
>> statement handles if I use swap_inner_handle?
>
>
> The inner handle carries all the DBI info, including CachedKids.
> So swap_inner_handle is a simple and total brain transplant.
>
>
>> my $sth = $dbh->prepare_cached( $queryz{$name}, $queryargz )
>
>
>> if $d = $dbh->selectall_arrayref( $sth, undef, @_ );
>
>
>>The selectall_* calls normally obviate the need for $if_active;
>>however swapping the database handles might require re-preparing
>>all of the file handles using
>>
>> my $sth = $dbh->prepare_cached( $queryz{$name}, $queryargz, 3 )
>>
>>after etching the database handle's brains with swap_inner_handle;
>>or setting some magic "this needs to be re-prepared the next pass"
>>flag on all of the statement handles?
>>
>>Or is the best way to just
>>
>> my $kidz = $dbh->CachedKids;
>> delete @{ $kidz }{ keys %$kidz };
>>
>>and be done with it?
>
>
> Or just $dbh->{CachedKids} = {};
>
>
>>Or is some similar maintainence done automatically by the
>>swap_inner_handle?
>
>
> swap_inner_handle just does what it says - swaps handles. That's it.
>
> I suspect what you'll need to do (for maximum transparency) is perform a
> swap_inner_handle on each of the old dbh's kids to replace the now
> defunct sth's with new ones freshly prepared using the new dbh.
>
> If so then it's kind'a handy that the DBI now has a $h->{ChildHandles}
> attribute.
>
> If you really want to get fancy you could each check $old_sth->rows and
> then fetch that many rows from the new $sth to leave it in the same
> 'place' (hopefully!) as the original. Just how mad do you want to be?
>
> You're going to have to do some thinking and testing yourself on this
> one Steven. You're the first to walk down this particular path.
> Should be an interesting journey though!
>
> Tim.

Re: Statement handle side effects of swap_inner_handle?

On Thu, Jan 26, 2006 at 02:04:28AM -0500, Steven Lembark wrote:
>
> > The inner handle carries all the DBI info, including CachedKids.
> > So swap_inner_handle is a simple and total brain transplant.
>
> Q: Any chance of getting "$sth->clone"?

Doubtful. Patches always welcome of course. You've got Statement,
Active, and possibly ParamValues and ParamTypes attributes to work
with so it might be possible, maybe.

> From what I can see in the doc's, the clone method
> is specific to database handles (the statement's clone
> would account for cached queries). Matching up the
> cached vs. un-cached values is doable, but getting
> the bound variables (especially lexicals) correct
> is going to seriously difficult.

Yeap.

> >> Or is some similar maintainence done automatically by the
> >> swap_inner_handle?
> >
> > swap_inner_handle just does what it says - swaps handles. That's it.
> >
> > I suspect what you'll need to do (for maximum transparency) is perform a
> > swap_inner_handle on each of the old dbh's kids to replace the now
> > defunct sth's with new ones freshly prepared using the new dbh.
>
> The trick will be matching the values of CachedKids
> to those in ChildHandles to cache the proper set of
> handles:
>
> DB<1> x $dbh->{CachedKids}
> 0 HASH(0x86900a4)
> ' select now() ' => DBI::st=HASH(0x868ff24)
> empty hash
>
> Otherwise someone using prepare and prepare_cached
> on the same sql string would end up with mismatched
> statements after the reconnect.

No need. It should be harmless for CachedKids of the new dbh to be
empty. Or...

> Q: If the cached kids are stored in $new_dbh, wouldn't I
> overwrite the statement handles when I performed the
> $old_dbh->swap_inner_handle( $new_dbh )?

True. If you swap_inner_handle for all ChildHandles then you'll
automatically deal with CachedKids as well. Simple.

> The only thing this doesn't pull across that I can think
> of is the current row state of each handle

You could use $sth->rows and fetch to the same row count
(but there's no guarantee you'll be getting the same values).

> and the bound variables.

ParamValues and ParamTypes gives you a way to redo bind_param().
But there's no equivalent for bind_param_inout(), nor for bind_columns.

Umm, bind_columns is probably do-able if the DBI gave you a way to
access the current row array. Then you could just bind the new columns
to the old ones.


> Q: From what I can see, $sth->{Type} doesn't give me
> enough information to tell if a particular sth
> is cached or not. Am I missing something?

No. $is_cached = grep { $_ == $sth } values %{$dbh->{CachedKids}};

> > If so then it's kind'a handy that the DBI now has a $h->{ChildHandles}
> > attribute.
> >
> > If you really want to get fancy you could each check $old_sth->rows and
> > then fetch that many rows from the new $sth to leave it in the same
> > 'place' (hopefully!) as the original. Just how mad do you want to be?
>
> Only risk there is accidentally restarting the query-from-hell,
> but that could be avoided with a configuration parmeter
> to DBIx::Viagra.

It's not restarting the query-from-hell that worries me, it's the fact
that rows may have been added or deleted since the first select
(assuming it is a select statement) so the application may process some
rows twice and/or miss some rows.

But if you proceed with this then I'd expect your nice CPAN module will
give people lots of hooks to express what kinds of madness they're
comfortable with :)


> Q: Is there any chance of getting a 'seek' to handle this?
> It would look sometehing like:
>
> $sth->seek( $sth->rows );
>
> and save me from retrieving the entire list of rows into
> core on the local side just to discard them.

No. At least not till someone added even basic support for scrollable
cursors.

> Aside: How likely to change is dbh->clone as of 1.50?
>
> The "clone" method was added in DBI 1.33. It is very new and likely
> to change.

The more widely it's used the less likely it is to change :)

The 'big issue' is how to deal with attribute values that have changed
since the $dbh was created.

Tim.

Re: Statement handle side effects of swap_inner_handle?

>> Q: If the cached kids are stored in $new_dbh, wouldn't I
>> overwrite the statement handles when I performed the
>> $old_dbh->swap_inner_handle( $new_dbh )?
>
> True. If you swap_inner_handle for all ChildHandles then you'll
> automatically deal with CachedKids as well. Simple.

Some good news on the front...

>> The only thing this doesn't pull across that I can think
>> of is the current row state of each handle
>
> You could use $sth->rows and fetch to the same row count
> (but there's no guarantee you'll be getting the same values).

Artistic Opinion: Would reconnecting the damaged handle
and keep it usable with an appropriate error then loosing
the transaction (and letting the caller deal with it
as an exception) seem reasonable?

i.e., $dbh->rows is their problem if they know that the
transaction has failed.


> ParamValues and ParamTypes gives you a way to redo bind_param().
> But there's no equivalent for bind_param_inout(), nor for bind_columns.
>
> Umm, bind_columns is probably do-able if the DBI gave you a way to
> access the current row array. Then you could just bind the new columns
> to the old ones.

Any real odds?

>> Q: From what I can see, $sth->{Type} doesn't give me
>> enough information to tell if a particular sth
>> is cached or not. Am I missing something?
>
> No. $is_cached = grep { $_ == $sth } values %{$dbh->{CachedKids}};

thanks

> It's not restarting the query-from-hell that worries me, it's the fact
> that rows may have been added or deleted since the first select
> (assuming it is a select statement) so the application may process some
> rows twice and/or miss some rows.
>
> But if you proceed with this then I'd expect your nice CPAN module will
> give people lots of hooks to express what kinds of madness they're
> comfortable with :)

>
>
>> Q: Is there any chance of getting a 'seek' to handle this?
>> It would look sometehing like:

For the moment it seems as though I'd want to just abandon
the transaction and let the user restart it themselves.
There is enough context in it that a "Transaction failed" +
"reconnected" exception would give them enough data to
deal with it appropriately.

>> Aside: How likely to change is dbh->clone as of 1.50?
>>
>> The "clone" method was added in DBI 1.33. It is very new and likely
>> to change.
>
> The more widely it's used the less likely it is to change :)
>
> The 'big issue' is how to deal with attribute values that have changed
> since the $dbh was created.

Q: Which ones does clone use now?


thanks for the input.

--
Steven Lembark 85-09 90th Street
Workhorse Computing Woodhaven, NY 11421
lembark@wrkhors.com 1 888 359 3508

Re: Statement handle side effects of swap_inner_handle?

On Thu, Jan 26, 2006 at 02:33:21PM -0500, Steven Lembark wrote:
>
> >> The only thing this doesn't pull across that I can think
> >> of is the current row state of each handle
> >
> > You could use $sth->rows and fetch to the same row count
> > (but there's no guarantee you'll be getting the same values).
>
> Artistic Opinion: Would reconnecting the damaged handle
> and keep it usable with an appropriate error then loosing
> the transaction (and letting the caller deal with it
> as an exception) seem reasonable?

Yes. That would possibly suffice is all code gets its statement handles
via prepare_cached(). But then if all code used prepare_cached() and
connect_cached() then you wouldn't need to go down this road at all :)

> > ParamValues and ParamTypes gives you a way to redo bind_param().
> > But there's no equivalent for bind_param_inout(), nor for bind_columns.
> >
> > Umm, bind_columns is probably do-able if the DBI gave you a way to
> > access the current row array. Then you could just bind the new columns
> > to the old ones.
>
> Any real odds?

Odds?

> >> Aside: How likely to change is dbh->clone as of 1.50?
> >>
> >> The "clone" method was added in DBI 1.33. It is very new and likely
> >> to change.
> >
> > The more widely it's used the less likely it is to change :)
> >
> > The 'big issue' is how to deal with attribute values that have changed
> > since the $dbh was created.
>
> Q: Which ones does clone use now?

See the docs and then the code...

Tim.

Re: Statement handle side effects of swap_inner_handle?

>> > Umm, bind_columns is probably do-able if the DBI gave you a way to
>> > access the current row array. Then you could just bind the new columns
>> > to the old ones.
>>
>> Any real odds?
>
> Odds?

"Probably do-able"

Q1: How likely?
Q2: Any timeframe?

--
Steven Lembark 85-09 90th Street
Workhorse Computing Woodhaven, NY 11421
lembark@wrkhors.com 1 888 359 3508

Re: Statement handle side effects of swap_inner_handle?

>> Artistic Opinion: Would reconnecting the damaged handle
>> and keep it usable with an appropriate error then loosing
>> the transaction (and letting the caller deal with it
>> as an exception) seem reasonable?
>
> Yes. That would possibly suffice is all code gets its statement handles
> via prepare_cached(). But then if all code used prepare_cached() and
> connect_cached() then you wouldn't need to go down this road at all :)

Trick is to have the caller avoid having to use
connect_cached for themselves each time they
access the database handle. Ideally they should
be able to:

{
my $dbh = DBIx::Priaprism( @blah );

sub foo
{
do
{
eval
{
$dbh->foo( ... );
};
}
while( $@ =~ /Reconnected/ );
}
}

and have the connection up forever.


--
Steven Lembark 85-09 90th Street
Workhorse Computing Woodhaven, NY 11421
lembark@wrkhors.com 1 888 359 3508

Re: Statement handle side effects of swap_inner_handle?

On Fri, Jan 27, 2006 at 07:12:12PM -0500, Steven Lembark wrote:
>
> >>> Umm, bind_columns is probably do-able if the DBI gave you a way to
> >>> access the current row array. Then you could just bind the new columns
> >>> to the old ones.
> >>
> >>Any real odds?
> >
> >Odds?
>
> "Probably do-able"
>
> Q1: How likely?
> Q2: Any timeframe?

As soon as someone sends me a patch I'm happy with :)

But it occurs to me that you could call $row = $sth->_get_fbav;
(See perldoc DBI::DBD for details.) That'll have the side effect of
incrementing the row count for that $sth, but that's not a problem in
your situation.

Tim.