Gossamer Forum: General: Perl Programming: Correct way to close a DBI connection...

Mar 18, 2005, 2:03 AM

Andy

Veteran / Moderator (18436 posts)

Mar 18, 2005, 2:03 AM

Post #1 of 6

Shortcut

Correct way to close a DBI connection...

Hi,

I've been using DBI connections for a while now (normally just use GT::SQL <G>). I've recently come across a problem though, whereas quite a few of the processes keep running on.

I know this isn't down to the process being too long; and have an idea that it *could* be down to the connection being closed correctly.

I've tried many methods;

$sth->disconnect;
DBI->disconnect;
$sth = undef;

and quite a few more. However, none of these seem to close the connection correctly.

NB, the MySQL server is a different one to the main web-server (i.e the one that has the sites on), would this have anything to do with it?

The only reason I noticed anything going on, was because at peak times, we have been getting an error message, about needing to use "mysqladmin flush-hosts". As a temp fix, I've set this up on an hourly cron, but I'd rather get to the bottom of the problem Smile

TIA!

Andy (mod)
andy@ultranerds.co.uk

Mar 21, 2005, 2:41 AM

Andy

Veteran / Moderator (18436 posts)

Mar 21, 2005, 2:41 AM

Post #2 of 6

Shortcut

Re: [Andy] Correct way to close a DBI connection... In reply to

*bump*

Andy (mod)
andy@ultranerds.co.uk

Mar 22, 2005, 8:05 AM

Chaz

Enthusiast (819 posts)

Mar 22, 2005, 8:05 AM

Post #3 of 6

Shortcut

Re: [Andy] Correct way to close a DBI connection... In reply to

The proper way is to close the DB handle. From the docs:

Code:
$rc = $dbh->disconnect  or warn $dbh->errstr;

http://search.cpan.org/~timb/DBI/DBI.pm#disconnect

But, like the docs state, this is usually done for you on DESTROY. Maybe you have a closure hiding somewhere? Are you running under mod_perl? Are you getting any warnings in your log?

~Charlie

Mar 22, 2005, 8:08 AM

Andy

Veteran / Moderator (18436 posts)

Mar 22, 2005, 8:08 AM

Post #4 of 6

Shortcut

Re: [Chaz] Correct way to close a DBI connection... In reply to

Hi,

Thanks for the reply :)

Managed to get it to close correctly with;

Code:
$sth->finish(); 
$dbh->disconnect();

Not getting any error messages at all now <G>

Cheers

Andy (mod)
andy@ultranerds.co.uk

Mar 22, 2005, 8:17 AM

Chaz

Enthusiast (819 posts)

Mar 22, 2005, 8:17 AM

Post #5 of 6

Shortcut

Re: [Andy] Correct way to close a DBI connection... In reply to

Cool :) Were you getting any erros or warnings in the logs or just the open connections to the DB?

~Charlie

Mar 22, 2005, 8:31 AM

Andy

Veteran / Moderator (18436 posts)

Mar 22, 2005, 8:31 AM

Post #6 of 6

Shortcut

Re: [Chaz] Correct way to close a DBI connection... In reply to

Nah, the main errors were related to the DESTROY. Apparantly that was because I didn't use $sth->finish();

Basically, I was closing the connection ... but not the query <G>

Cheers

Andy (mod)
andy@ultranerds.co.uk