Re: Re: using multiple packages w/1 database connection

[tachyon]

Pet peeve. If you don't religiously disconnect from the DB what CAN happen is that when the script completes $dbh gets undeffed BUT the underlying connection MAY potentially remain until it times out. Although the DBI DESTROY method should be called on script exit and this should call disconnect we have practical experince that this is not always the case. Also if you are using transactions the Commit/Rollback behaviour on disconnect is not defined so you might wish to add a commit to the end block as well to finalise everything.

On MySQL you get a default limit of 100 max_connections and a timeout of 8 hours. Result: Run a script that fails to disconnect more than 100 times in 8 hours and you will potentially use up all your available connections. At this point the DB connect will error out every time until an old connection times out. I think is is failry sound practice to code the connect like this, with the disconnect in an END block immediately below. It makes sure it happens.

Package DBH;
my $dbh=DBI->connect('DBD:foo:baz',bar,qux);
END{ $dbh->disconnect if $dbh };

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[iburrell]

When the program exits all file handles closed by the OS. That should close the socket for the database connection. The Perl code that cleans up might not get run. I guess some database servers could sit there waiting until they get an explicit close command and not notice that the client disappeared.

Recent versions of DBI include an END block that does a disconnect_all and closes all database connections. Also, the DESTROY is supposed to call rollback before the disconnect. Doing a rollback in the END block is the safest thing otherwise some unknown, possibly failed, transaction could be committed.

[tachyon]

Yes I know that is supposed to happen. Truth be known this is really more of a mod_perl problem, but it actually applies to any long running perl process that has within it code in the form sub { my $dbh = DBI->connect.... }. Under mod_perl for example the combination of lazy garbage collection and ongoing runtime mean the DESTROY and END blocks (respectively) never get called. It was a real problem we had porting to mod_perl which is why I mention it.

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print