Apache::DBI losing connection

Michael L. Artz Sat, 14 Feb 2004 17:48:28 -0800

I'm running a web application under Apache2/mod_perl2 using PostgreSQL as a backend and DBI/Apache::DBI to connect to it. I'm still in development of the system, so I have a couple of pages with invalid SQL on them. The problem is that, once I go to one of these pages with invalid SQL, every other SQL statement that I try and execute that uses that particular handle (since Apache::DBI cached it) fails. So, if I'm running apache in debug (single-server) mode, once I hit this invalid page, every other request will fail, even to valid pages.

The code below is what seems to be a valid simplification of what is happening within my application ... I create a dbh, prepare and execute an invalid SELECT, and then try and use the dbh again for a valid query, and the valid query fails. Is this what is supposed to happen?

-------------------------------------
#!/usr/bin/perl
use DBI;
my @database = ("dbi:Pg:dbname=iact",
                "dragon", "",
                { AutoCommit => 0, RaiseError => 0 });

my $dbh = DBI->connect(@database);
#invalid SQL
my $sql = "SELECT crap;";

open(STDERR,">>/tmp/trace") || die ">>/tmp/trace $!";
print STDERR "\nDBI trace(10) in ".__FILE__.' at '.__LINE__."\n\n";
$dbh->trace(10);

my $sth = $dbh->prepare($sql);
$sth->execute();

$sql = "SELECT 1;";
my ($should_be_one) = $dbh->selectrow_array($sql);
$dbh->disconnect();
-------------------------------------
The output from the above being:

[EMAIL PROTECTED] iactweb $ ./testdb.pl DBD::Pg::st execute failed: ERROR: Attribute "crap" not found at ./testdb.pl line 12. DBD::Pg::db selectrow_array failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at ./testdb.pl line 15. [EMAIL PROTECTED] iactweb $

Also below is the trace file obtained from the STDERR (thanks for that snippet Render). Any ideas on how to correct this behavior, or am I doing something stupid?

Thanks
-Mike

DBI trace(10) in ./testdb.pl at 14

DBI::db=HASH(0x8274ca4) trace level set to 10 in DBI 1.39-nothread (pid 27412) Note: perl is running without the recommended perl -w option -> prepare for DBD::Pg::db (DBI::db=HASH(0x81cdc94)~0x8274ca4 'SELECT crap;') dbih_setup_handle(DBI::st=HASH(0x81c5480)=>DBI::st=HASH(0x81c5504), DBD::Pg::st, 81c548c, Null!) dbih_make_com(DBI::db=HASH(0x8274ca4), 81c6538, DBD::Pg::st, 144, 0) thr#0 dbih_setup_attrib(DBI::st=HASH(0x81c5504), Err, DBI::db=HASH(0x8274ca4)) SCALAR(0x8206c7c) (already defined) dbih_setup_attrib(DBI::st=HASH(0x81c5504), State, DBI::db=HASH(0x8274ca4)) SCALAR(0x8206cdc) (already defined) dbih_setup_attrib(DBI::st=HASH(0x81c5504), Errstr, DBI::db=HASH(0x8274ca4)) SCALAR(0x8206cac) (already defined) dbih_setup_attrib(DBI::st=HASH(0x81c5504), TraceLevel, DBI::db=HASH(0x8274ca4)) 10 (already defined) dbih_setup_attrib(DBI::st=HASH(0x81c5504), FetchHashKeyName, DBI::db=HASH(0x8274ca4)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0x81c5504), HandleError, DBI::db=HASH(0x8274ca4)) undef (not defined) dbd_st_prepare: statement = >SELECT crap;< dbd_st_preparse: statement = >SELECT crap;< <- prepare= DBI::st=HASH(0x81c5480) at ./testdb.pl line 17 -> execute for DBD::Pg::st (DBI::st=HASH(0x81c5480)~0x81c5504) dbd_st_execute dbd_st_execute: statement = >SELECT crap;< ERROR: Attribute "crap" not found error 7 recorded: ERROR: Attribute "crap" not found !! ERROR: 7 'ERROR: Attribute "crap" not found' <- execute= undef at ./testdb.pl line 18 DBD::Pg::st execute failed: ERROR: Attribute "crap" not found at ./testdb.pl line 18. !! ERROR: 7 CLEARED by call to selectrow_array method -> selectrow_array in DBD::_::db for DBD::Pg::db (DBI::db=HASH(0x81cdc94)~0x8274ca4 'SELECT 1;') >> prepare DISPATCH (DBI::db=HASH(0x8274ca4) rc1/4 @3 g0 ima201 pid#27412) at /usr/lib/perl5/site_perl/5.8.0/i686-linux/DBI.pm line 1389 1 -> prepare for DBD::Pg::db (DBI::db=HASH(0x8274ca4)~INNER 'SELECT 1;' undef) dbih_setup_handle(DBI::st=HASH(0x81c554c)=>DBI::st=HASH(0x81c55e8), DBD::Pg::st, 81c5558, Null!) dbih_make_com(DBI::db=HASH(0x8274ca4), 81c6538, DBD::Pg::st, 144, 0) thr#0 dbih_setup_attrib(DBI::st=HASH(0x81c55e8), Err, DBI::db=HASH(0x8274ca4)) SCALAR(0x8206c7c) (already defined) dbih_setup_attrib(DBI::st=HASH(0x81c55e8), State, DBI::db=HASH(0x8274ca4)) SCALAR(0x8206cdc) (already defined) dbih_setup_attrib(DBI::st=HASH(0x81c55e8), Errstr, DBI::db=HASH(0x8274ca4)) SCALAR(0x8206cac) (already defined) dbih_setup_attrib(DBI::st=HASH(0x81c55e8), TraceLevel, DBI::db=HASH(0x8274ca4)) 10 (already defined) dbih_setup_attrib(DBI::st=HASH(0x81c55e8), FetchHashKeyName, DBI::db=HASH(0x8274ca4)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0x81c55e8), HandleError, DBI::db=HASH(0x8274ca4)) undef (not defined) dbd_st_prepare: statement = >SELECT 1;< dbd_st_preparse: statement = >SELECT 1;< 1 <- prepare= DBI::st=HASH(0x81c554c) at /usr/lib/perl5/site_perl/5.8.0/i686-linux/DBI.pm line 1389 >> execute DISPATCH (DBI::st=HASH(0x81c554c) rc1/1 @1 g0 ima41 pid#27412) at /usr/lib/perl5/site_perl/5.8.0/i686-linux/DBI.pm line 1391 -> execute for DBD::Pg::st (DBI::st=HASH(0x81c554c)~0x81c55e8) dbd_st_execute dbd_st_execute: statement = >SELECT 1;< ERROR: current transaction is aborted, queries ignored until end of transaction block error 7 recorded: ERROR: current transaction is aborted, queries ignored until end of transaction block !! ERROR: 7 'ERROR: current transaction is aborted, queries ignored until end of transaction block' <- execute= undef at /usr/lib/perl5/site_perl/5.8.0/i686-linux/DBI.pm line 1391 >> DESTROY DISPATCH (DBI::st=HASH(0x81c554c) rc1/1 @1 g0 ima4 pid#27412) at /usr/lib/perl5/site_perl/5.8.0/i686-linux/DBI.pm line 1403 via ./testdb.pl line 21 <> DESTROY ignored for outer handle DBI::st=HASH(0x81c554c) (inner DBI::st=HASH(0x81c55e8)) >> DESTROY DISPATCH (DBI::st=HASH(0x81c55e8) rc1/1 @1 g0 ima4 pid#27412) at /usr/lib/perl5/site_perl/5.8.0/i686-linux/DBI.pm line 1403 via ./testdb.pl line 21 -> DESTROY for DBD::Pg::st (DBI::st=HASH(0x81c55e8)~INNER) dbd_st_destroy error: 7 'ERROR: current transaction is aborted, queries ignored until end of transaction block' <- DESTROY= undef at /usr/lib/perl5/site_perl/5.8.0/i686-linux/DBI.pm line 1403 via ./testdb.pl line 21 DESTROY (dbih_clearcom) (sth 0x81c554c 0x81863f0, com 0x81c57e8, imp DBD::Pg::st): FLAGS 0x111: COMSET Warn PrintError PARENT DBI::db=HASH(0x8274ca4) KIDS 0 (0 Active) IMP_DATA undef NUM_OF_FIELDS 0 NUM_OF_PARAMS 0 dbih_clearcom 0x81c554c (com 0x81c57e8, type 3) done.

!! ERROR: 7 'ERROR: current transaction is aborted, queries ignored until end of transaction block' <- selectrow_array= ( ) [0 items] at ./testdb.pl line 21 DBD::Pg::db selectrow_array failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at ./testdb.pl line 21. !! ERROR: 7 CLEARED by call to disconnect method -> disconnect for DBD::Pg::db (DBI::db=HASH(0x81cdc94)~0x8274ca4) dbd_db_disconnect dbd_db_disconnect: AutoCommit=off -> rollback <- disconnect= 1 at ./testdb.pl line 23 -> DESTROY for DBD::Pg::st (DBI::st=HASH(0x81c5504)~INNER) dbd_st_destroy <- DESTROY= undef -> DESTROY for DBD::Pg::db (DBI::db=HASH(0x8274ca4)~INNER) dbd_db_destroy <- DESTROY= undef

Apache::DBI losing connection

Reply via email to