You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Lev Lvovsky <li...@sonous.com> on 2006/11/28 07:45:09 UTC
DBI AutoCommit goes away when db connection is killed
Hello - this may be a question unrelated to mod_perl so much as it is
related to DBI and mysql, but any suggestions would be appreciated
greatly!
In testing out persistent connections while using transactions, we've
noticed that while in a loop which continuously begins and ends a
transaction, killing the persistent connection which Apache::DBI is
maintaining causes the still-running handler to report things like:
error: DBD driver has not implemented the AutoCommit attribute at /
usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBI.pm line
1668.
In dumping the status of the handle after this has been done, the
AutoCommit flag is no longer there either.
In the case that this happens, is there any way other than restarting
the apache process to correct this issue (simply setting AutoCommit
to 1 doesn't work as it's a tie'd variable).
thank you for any help you might shine on this!
-lev
Re: DBI AutoCommit goes away when db connection is killed
Posted by Perrin Harkins <pe...@elem.com>.
Lev Lvovsky wrote:
> we're trying to simulate a database outage for an application which
> needs to be able to fail gracefully, and come back up when the DB does.
> These errors can also be seen when the mysql server is restarted
> (connections get killed then too).
Yes, in both cases you no longer have a connection.
> How do we explicitly do this with Apache::DBI maintaining the persistent
> connection from the beginning?
Well, if you break the connection, it's broken. There's nothing that
Apache::DBI or anything else can do about that. What Apache::DBI does
is intercept your DBI->connect calls and return an already open handle
if possible, or open a new one if needed.
Here's an example of recovering from a lost connection. This is totally
untested code, but maybe it will give you the idea. This assumes that
RaiseError is on.
eval {
my $dbh = DBI->connect(...);
local $dbh->{AutoCommit} = 0;
$dbh->do('UPDATE ...');
$dbh->commit;
}
if ($@) {
# wrap this in eval, since it will fail if the connection is gone
eval { $dbh->rollback; }
# replace the dead connection with a new one if necessary
$dbh = DBI->connect(...);
}
The DBI->connect() call in the error handling block will get intercepted
by Apache::DBI, and will return the current open connection if it is
still able to ping the database. If it isn't able to, it will open a
new connection.
- Perrin
Re: DBI AutoCommit goes away when db connection is killed
Posted by Lev Lvovsky <li...@sonous.com>.
On Nov 28, 2006, at 6:18 AM, Perrin Harkins wrote:
>> /lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBI.pm line
>> 1668.
>
> That's what it says when you're trying to do something to a $dbh
> that is not connected. Why are you killing the connection during a
> transaction? What are you trying to do?
we're trying to simulate a database outage for an application which
needs to be able to fail gracefully, and come back up when the DB
does. These errors can also be seen when the mysql server is
restarted (connections get killed then too).
>> In the case that this happens, is there any way other than
>> restarting the apache process to correct this issue (simply
>> setting AutoCommit to 1 doesn't work as it's a tie'd variable).
>
> You need to connect to a database before it will work again.
How do we explicitly do this with Apache::DBI maintaining the
persistent connection from the beginning?
thanks!
-lev
Re: DBI AutoCommit goes away when db connection is killed
Posted by Perrin Harkins <pe...@elem.com>.
Lev Lvovsky wrote:
> In testing out persistent connections while using transactions, we've
> noticed that while in a loop which continuously begins and ends a
> transaction, killing the persistent connection which Apache::DBI is
> maintaining causes the still-running handler to report things like:
>
> error: DBD driver has not implemented the AutoCommit attribute at
> /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBI.pm line 1668.
That's what it says when you're trying to do something to a $dbh that is
not connected. Why are you killing the connection during a transaction?
What are you trying to do?
> In the case that this happens, is there any way other than restarting
> the apache process to correct this issue (simply setting AutoCommit to 1
> doesn't work as it's a tie'd variable).
You need to connect to a database before it will work again.
- Perrin
Re: DBI AutoCommit goes away when db connection is killed
Posted by "Philip M. Gollucci" <pg...@p6m7g8.com>.
Lev Lvovsky wrote:
> perl-Apache-DBI-1.01-1
This is new enough to include the 2 changes I listed below from its
Changes file; however, I'd recommend v1.06
> perl-DBI-1.52-1.fc5
> perl-DBD-mysql-3.0008_1-1
> MySQL 5.022 for all mysql related RPMs
These are fine.
>> Seems like your Apache::DBI::ping() method is busted which I know was
>> been tweaked -1-2 times recently.
> sorry, was this a fix, or a changed needed on our end?
These were fixes in the releases noted previously.
What exactly do you mean by 'kill' the connection ?
--
------------------------------------------------------------------------
Philip M. Gollucci (pgollucci@p6m7g8.com) 323.219.4708
Consultant / http://p6m7g8.net/Resume/resume.shtml
Senior Software Engineer - TicketMaster - http://ticketmaster.com
1024D/A79997FA F357 0FDD 2301 6296 690F 6A47 D55A 7172 A799 97F
I never had a dream come true
'Til the day that I found you.
Even though I pretend that I've moved on
You'll always be my baby.
I never found the words to say
You're the one I think about each day
And I know no matter where life takes me to
A part of me will always be...
A part of me will always be with you.
Re: DBI AutoCommit goes away when db connection is killed
Posted by Lev Lvovsky <li...@sonous.com>.
Hi Philip,
On Nov 27, 2006, at 11:16 PM, Philip M. Gollucci wrote:
> Lev Lvovsky wrote:
>> Hello - this may be a question unrelated to mod_perl so much as it
>> is related to DBI and mysql, but any suggestions would be
>> appreciated greatly!
> What versions of Apache-DBI, DBI, mysql ?
perl-Apache-DBI-1.01-1
perl-DBI-1.52-1.fc5
perl-DBD-mysql-3.0008_1-1
MySQL 5.022 for all mysql related RPMs
>> In testing out persistent connections while using transactions,
>> we've noticed that while in a loop which continuously begins and
>> ends a transaction, killing the persistent connection which
>> Apache::DBI is maintaining causes the still-running handler to
>> report things like:
>> error: DBD driver has not implemented the AutoCommit attribute at /
>> usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBI.pm
>> line 1668.
>> In dumping the status of the handle after this has been done, the
>> AutoCommit flag is no longer there either.
> $Apache::DBI::Debug = 2;
> $dbh->trace('file', 4);
will try this.
>
>> In the case that this happens, is there any way other than
>> restarting the apache process to correct this issue (simply
>> setting AutoCommit to 1 doesn't work as it's a tie'd variable).
> Seems like your Apache::DBI::ping() method is busted which I know
> was been tweaked -1-2 times recently.
>
> 1.00_01 05/29/2006
> - Fix a () bug in the connect() determining whether we must ping
> the database. PingTimeOut = 0 now works as documented.
> Submited by: [Joe Thomas <jo...@tellme.com>]
> Contributed by: [Patrick Mulvany <pa...@firedrake.org>]
>
>
>
> Also, relating to AutoCommit being 'reset'
> 0.100 08/10/2005
>
> - Move $Idx from a file-scoped variable to a connect() scoped
> variable, which gets passed to other subroutines as needed.
> This will ensure that the cleanup/rollback feature will work
> properly when a script uses more than one database handle to the
> same database.
> [Joe Thomas <jo...@tellme.com>]
sorry, was this a fix, or a changed needed on our end?
thank you for your help!
-lev
Re: DBI AutoCommit goes away when db connection is killed
Posted by Robert Landrum <rl...@aol.net>.
Lev Lvovsky wrote:
>> Once you reconnect, the connection is persistent again. At least,
>> that's been my experience with postgresql.
>
> This is exactly what I've been thinking to do, but I've not seen
> anything within Apache::DBI that allows me to reconnect per se. How do
> I perform that action?
>
> Also, forgive this question if it seems a little obtuse, but in the
> startup.pl file, I do something like this:
>
> Apache::DBI->connect_on_init(...)
>
My experience with Apache::DBI has been that it needs no special setup.
All I've ever done is:
my $dbh;
$cnt = 0;
while(!$dbh) {
eval {
$dbh = DBI->connect(....);
};
if($@) {
warn("DB Down: $@");
sleep 5;
$cnt++;
}
last if $cnt > 3;
}
unless($dbh) {
# show user an error page
}
Rob
Re: DBI AutoCommit goes away when db connection is killed
Posted by Lev Lvovsky <li...@sonous.com>.
On Nov 28, 2006, at 3:04 PM, Robert Landrum wrote:
> Lev Lvovsky wrote:
>>>> In testing out persistent connections while using transactions,
>>>> we've noticed that while in a loop which continuously begins and
>>>> ends a transaction, killing the persistent connection which
>>>> Apache::DBI is maintaining causes the still-running handler to
>>>> report things like:
>>>> error: DBD driver has not implemented the AutoCommit attribute
>>>> at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/
>>>> DBI.pm line 1668.
>> 1 -> STORE for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER
>> 'AutoCommit' 0) thr#915dc30
>> --> do_error
>> Turning off AutoCommit failed error 21 recorded: Turning off
>> AutoCommit failed
>> <-- do_error
>> STORE DBI::db=HASH(0x9bf49cc) 'AutoCommit' => 0
>> !! ERROR: 21 CLEARED by call to begin_work method
>> -> begin_work for DBD::mysql::db (DBI::db=HASH(0x9b8678c)
>> ~0x9bf49cc) thr#915dc30
>
> So, your code is calling begin_work, fetching a value, storing a
> value, and committing? The store will fail if the database
> connection goes away, and it'll do so when it attempts communicate
> with the server to set AutoCommit to 0.
>
> You might want to call $dbh->ping before calling your store
> routine. Either way, the way to make this fault tolerant is with eval.
>
> Once you've determined that there's been a fault, and that the
> fault means that the database is down, try to reconnect. Continue
> to try to reconnect until the database comes back online. Then
> retry the original failed transaction.
>
> Once you reconnect, the connection is persistent again. At least,
> that's been my experience with postgresql.
This is exactly what I've been thinking to do, but I've not seen
anything within Apache::DBI that allows me to reconnect per se. How
do I perform that action?
Also, forgive this question if it seems a little obtuse, but in the
startup.pl file, I do something like this:
Apache::DBI->connect_on_init(...)
how do I then get the database handle that this establishes, so that
I can pass it around to the mod_perl hanlders, and the subsequent DBI
stuff? get_handlers() from the Apache2::ServerUtil class?
thanks very much!
-lev
Re: DBI AutoCommit goes away when db connection is killed
Posted by Robert Landrum <rl...@aol.net>.
Lev Lvovsky wrote:
>>> In testing out persistent connections while using transactions, we've
>>> noticed that while in a loop which continuously begins and ends a
>>> transaction, killing the persistent connection which Apache::DBI is
>>> maintaining causes the still-running handler to report things like:
>>> error: DBD driver has not implemented the AutoCommit attribute at
>>> /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBI.pm line
>>> 1668.
> 1 -> STORE for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER
> 'AutoCommit' 0) thr#915dc30
> --> do_error
> Turning off AutoCommit failed error 21 recorded: Turning off AutoCommit
> failed
> <-- do_error
> STORE DBI::db=HASH(0x9bf49cc) 'AutoCommit' => 0
> !! ERROR: 21 CLEARED by call to begin_work method
> -> begin_work for DBD::mysql::db (DBI::db=HASH(0x9b8678c)~0x9bf49cc)
> thr#915dc30
So, your code is calling begin_work, fetching a value, storing a value,
and committing? The store will fail if the database connection goes
away, and it'll do so when it attempts communicate with the server to
set AutoCommit to 0.
You might want to call $dbh->ping before calling your store routine.
Either way, the way to make this fault tolerant is with eval.
Once you've determined that there's been a fault, and that the fault
means that the database is down, try to reconnect. Continue to try to
reconnect until the database comes back online. Then retry the original
failed transaction.
Once you reconnect, the connection is persistent again. At least,
that's been my experience with postgresql.
Rob
Re: DBI AutoCommit goes away when db connection is killed
Posted by Lev Lvovsky <li...@sonous.com>.
On Nov 27, 2006, at 11:16 PM, Philip M. Gollucci wrote:
>> In testing out persistent connections while using transactions,
>> we've noticed that while in a loop which continuously begins and
>> ends a transaction, killing the persistent connection which
>> Apache::DBI is maintaining causes the still-running handler to
>> report things like:
>> error: DBD driver has not implemented the AutoCommit attribute at /
>> usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBI.pm
>> line 1668.
>> In dumping the status of the handle after this has been done, the
>> AutoCommit flag is no longer there either.
> $Apache::DBI::Debug = 2;
> $dbh->trace('file', 4);
Below is the resultant log file - here, Transaction.pm has a call to
begin_work which turns off AutoCommit:
-------------- snippet of log while connection is still up
----------------
-> commit for DBD::mysql::db (DBI::db=HASH(0x9b8678c)~0x9bf49cc)
thr#915dc30
<- commit= 1 at /some/source/path/Transaction.pm line 38 via
at /some/source/path/SomeModule.pm line 36
1 -> STORE for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER
'AutoCommit' 1) thr#915dc30
1 <- STORE= 1 at /some/source/path/Transaction.pm line 38 via at /
some/source/path/SomeModule.pm line 36
-> begin_work for DBD::mysql::db (DBI::db=HASH(0x9b8678c)
~0x9bf49cc) thr#915dc30
1 -> FETCH for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER
'AutoCommit') thr#915dc30
1 <- FETCH= 1 at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-
multi/DBI.pm line 1666 via at /some/source/path/Transaction.pm line 23
1 -> STORE for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER
'AutoCommit' 0) thr#915dc30
1 <- STORE= 1 at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-
multi/DBI.pm line 1668 via at /some/source/path/Transaction.pm line 23
1 -> STORE for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER
'BegunWork' 1) thr#915dc30
STORE DBI::db=HASH(0x9bf49cc) 'BegunWork' => 1
------------------------------------------------------------------------
----
-------------- snippet of log while connection is down ----------------
<- commit= 1 at /some/source/path/Transaction.pm line 38 via
at /some/source/path/SomeModule.pm line 36
1 -> STORE for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER
'AutoCommit' 1) thr#915dc30
1 <- STORE= 1 at /some/source/path/Transaction.pm line 38 via at /
some/source/path/SomeModule.pm line 36
-> begin_work for DBD::mysql::db (DBI::db=HASH(0x9b8678c)
~0x9bf49cc) thr#915dc30
1 -> FETCH for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER
'AutoCommit') thr#915dc30
1 <- FETCH= 1 at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-
multi/DBI.pm line 1666 via at /some/source/path/Transaction.pm line 23
1 -> STORE for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER
'AutoCommit' 0) thr#915dc30
--> do_error
Turning off AutoCommit failed error 21 recorded: Turning off
AutoCommit failed
<-- do_error
STORE DBI::db=HASH(0x9bf49cc) 'AutoCommit' => 0
!! ERROR: 21 CLEARED by call to begin_work method
-> begin_work for DBD::mysql::db (DBI::db=HASH(0x9b8678c)
~0x9bf49cc) thr#915dc30
1 -> FETCH for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER
'AutoCommit') thr#915dc30
1 <- FETCH= 1 at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-
multi/DBI.pm line 1666 via at /some/source/path/Transaction.pm line 23
1 -> STORE for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER
'AutoCommit' 0) thr#915dc30
------------------------------------------------------------------------
----
thanks!
-lev
Re: DBI AutoCommit goes away when db connection is killed
Posted by "Philip M. Gollucci" <pg...@p6m7g8.com>.
Lev Lvovsky wrote:
> Hello - this may be a question unrelated to mod_perl so much as it is
> related to DBI and mysql, but any suggestions would be appreciated greatly!
What versions of Apache-DBI, DBI, mysql ?
>
> In testing out persistent connections while using transactions, we've
> noticed that while in a loop which continuously begins and ends a
> transaction, killing the persistent connection which Apache::DBI is
> maintaining causes the still-running handler to report things like:
>
> error: DBD driver has not implemented the AutoCommit attribute at
> /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBI.pm line 1668.
>
> In dumping the status of the handle after this has been done, the
> AutoCommit flag is no longer there either.
$Apache::DBI::Debug = 2;
$dbh->trace('file', 4);
> In the case that this happens, is there any way other than restarting
> the apache process to correct this issue (simply setting AutoCommit to 1
> doesn't work as it's a tie'd variable).
Seems like your Apache::DBI::ping() method is busted which I know was
been tweaked -1-2 times recently.
1.00_01 05/29/2006
- Fix a () bug in the connect() determining whether we must ping
the database. PingTimeOut = 0 now works as documented.
Submited by: [Joe Thomas <jo...@tellme.com>]
Contributed by: [Patrick Mulvany <pa...@firedrake.org>]
Also, relating to AutoCommit being 'reset'
0.100 08/10/2005
- Move $Idx from a file-scoped variable to a connect() scoped
variable, which gets passed to other subroutines as needed.
This will ensure that the cleanup/rollback feature will work
properly when a script uses more than one database handle to the
same database.
[Joe Thomas <jo...@tellme.com>]
--
------------------------------------------------------------------------
Philip M. Gollucci (pgollucci@p6m7g8.com) 323.219.4708
Consultant / http://p6m7g8.net/Resume/resume.shtml
Senior Software Engineer - TicketMaster - http://ticketmaster.com
1024D/A79997FA F357 0FDD 2301 6296 690F 6A47 D55A 7172 A799 97F
I never had a dream come true
'Til the day that I found you.
Even though I pretend that I've moved on
You'll always be my baby.
I never found the words to say
You're the one I think about each day
And I know no matter where life takes me to
A part of me will always be...
A part of me will always be with you.
Re: DBI AutoCommit goes away when db connection is killed
Posted by Lev Lvovsky <le...@sonous.com>.
I forgot to mention also that we're not using a startup.pl file, but
instead connecting to the DB several times using several different
accounts as a specific virtual host requires. At the risk of
pointing out the answer for myself, does this adversely affect anything?
thanks,
-lev
On Nov 27, 2006, at 10:45 PM, Lev Lvovsky wrote:
> Hello - this may be a question unrelated to mod_perl so much as it
> is related to DBI and mysql, but any suggestions would be
> appreciated greatly!
>
> In testing out persistent connections while using transactions,
> we've noticed that while in a loop which continuously begins and
> ends a transaction, killing the persistent connection which
> Apache::DBI is maintaining causes the still-running handler to
> report things like:
>
> error: DBD driver has not implemented the AutoCommit attribute at /
> usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBI.pm line
> 1668.
>
> In dumping the status of the handle after this has been done, the
> AutoCommit flag is no longer there either.
>
> In the case that this happens, is there any way other than
> restarting the apache process to correct this issue (simply setting
> AutoCommit to 1 doesn't work as it's a tie'd variable).
>
> thank you for any help you might shine on this!
> -lev