fenonn has asked for the wisdom of the Perl Monks concerning the following question:

I have been working with DBI and MySQL on and off for about two months and came across something that confused me. Reading the DBI man page I found that I should disconnect from the database before exiting the script. I assumed that after executing the script I can nolonger access the database without connecting again, but when I tired using the code below I could still get data from the database. Is there something I have missed or doesn’t the disconnect statement disconnect me from the database?

Fenonn. 
#!/usr/bin/perl -w
use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:fenonn:localhost","fenonn","password
+");

$dbh->disconnect;

my $sth = $dbh->prepare("SELECT data1 FROM table1");

$sth->execute() || die "Couldn't execute statement: $DBI::errstr; stop
+ped";

my ( $data ) = $sth->fetchrow_array();

print $data;

[download]

Replies are listed 'Best First'.
Re: Disconnecting from a Database
by tadman (Prior) on Oct 13, 2001 at 00:35 UTC
    The 'disconnect' method is a little abstract. It might not even disconnect at all, in the literal sense, as the documentation seems to imply that it "sets Active to Off". This implies that it is more like a "commit" than "disconnect".

    Also from the DBI documentation is some details on how the disconnect call can vary from platform to platform:
    The transaction behavior of the "disconnect" method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any outstanding changes, but others (such as Informix) will rollback any outstanding changes.
    So, this behavior may be an artifact of DBD::mysql more than anything else. Testing with PostgreSQL, or Oracle may shed some light on this.
[reply]
Re: Disconnecting from a Database
by CubicSpline (Friar) on Oct 13, 2001 at 00:34 UTC
    I would suggest that maybe this is a problem with the DBD::mysql module. The disconnect method does what you'd expect it to do, or at least that's what it's intended to do.

    Your code above will not run on my Win32 system using DBD::ODBC for my connection. It chokes on the prepare because $dbh is no longer a valid database handle.

[reply]
[d/l]
Re: Disconnecting from a Database
by Stegalex (Chaplain) on Oct 13, 2001 at 19:06 UTC
    If you are using Apache as your server, you should be using mod_perl v 1.24 or higher and Apache::DBI to provide connection pooling. Simply stated, Apache::DBI will keep a certain number of database connections open and will ignore your connect/disconnect method calls so you won't have to modify your code.

    I am not guaranteeing that this will improve things for you, but I think it is fair to say that Apache::DBI is generally perceived to be a performance enhancer.

    I have noticed garbage collection issues with the version of Oracle (I know you are using MySQL) in which even though I am explicitely closing my cursors and undefing my $STH's after I am done. In Oracle, there is a way to check and see if you have open cursors after the program has finished executing. Leaving open cursors has catastrophic effects on the performance of your httpd causing it to suck all available CPU and memory and thus bringing your server's performance to its knees. If you are experiencing similar problems, see if there is a way to detect open cursors after your program executes. Also, if you are not already doing so, you should be doing an $STH->finish on every statement handle after you are done using it.
[reply]
Re: Disconnecting from a Database
by Indomitus (Scribe) on Oct 13, 2001 at 21:41 UTC
    I always put my disconnect statement at the end of the function/program whenever I'm done with the database completely. I'm not a DBI whiz so that might not be The Right Thing but that's always what I've done. 
    my $dbh = DBI->connect("DBI:mysql:fenonn:localhost","fenonn","password
+");

my $sth = $dbh->prepare("SELECT data1 FROM table1");

$sth->execute() || die "Couldn't execute statement: DBI::errstr; stopp
+ed";

my ( $data ) = $sth->fetchrow_array();

$dbh->disconnect;

print $data;

    [download]
[reply]
[d/l]
Re: Disconnecting from a Database
by perrin (Chancellor) on Oct 13, 2001 at 00:59 UTC
    Are you using Apache::DBI?
[reply]
Re: Disconnecting from a Database
by graq (Curate) on Oct 16, 2001 at 15:31 UTC
    I have not looked this up specifically, so I may be wrong.
    But this is my thinkging:

    $dbh->disconnet does not undefine $dbh, so perl is still holding a valid reference.
    It is possible that DBI or DBD is force opening the connection as a default (because the reference is valid).

    Admittedly a long shot. But it might be worth checking.

[reply]

Back to Seekers of Perl Wisdom