Chapter 20. Relational Databases and mod_perl

20.1.5.1. Database locking risks

20.1.5.2. Transactions

In general, it is good practice to perform an explicit commit or rollback at the end of every script. To avoid inconsistencies in the database in case AutoCommit is Off and the script terminates prematurely without an explicit rollback, the Apache::DBI module uses a PerlCleanupHandler to issue a rollback at the end of every request.

20.1.5.3. Opening connections with different parameters

These two connect( ) calls will create two different connections:

my $dbh = DBI->connect
    ("DBI:mysql:test:localhost", '', '',
     {
      PrintError => 1, # warn( ) on errors
      RaiseError => 0, # don't die on error
      AutoCommit => 1, # commit executes immediately
     }
    ) or die "Cannot connect to database: $DBI::errstr";

my $dbh = DBI->connect
    ("DBI:mysql:test:localhost", '', '',
     {
      PrintError => 1, # warn( ) on errors
      RaiseError => 0, # don't die on error
      AutoCommit => 0, # don't commit executes immediately
     }
    ) or die "Cannot connect to database: $DBI::errstr";

Notice that the only difference is in the value of AutoCommit.

However, you are free to modify the handle immediately after you get it from the cache, so always initiate connections using the same parameters and set AutoCommit (or whatever) afterward. Let's rewrite the second connect( ) call to do the right thing (i.e., not to create a new connection):

my $dbh = DBI->connect
    ("DBI:mysql:test:localhost", '', '',
     {
      PrintError => 1, # warn( ) on errors
      RaiseError => 0, # don't die on error
      AutoCommit => 1, # commit executes immediately
     }
    ) or die "Cannot connect to database: $DBI::errstr";
$dbh->{AutoCommit} = 0; # don't commit if not asked to

When you aren't sure whether you're doing the right thing, turn on debug mode.

When the $dbh attribute is altered after connect( ), it affects all other handlers retrieving this database handle. Therefore, it's best to restore the modified attributes to their original values at the end of database handle usage. As of Apache::DBI Version 0.88, the caller has to do this manually. The simplest way to handle this is to localize the attributes when modifying them:

my $dbh = DBI->connect(...) ...
{
  local $dbh->{LongReadLen} = 40;
}

Here, the LongReadLen attribute overrides the value set in the connect( ) call or its default value only within the enclosing block.

The problem with this approach is that prior to Perl Version 5.8.0 it causes memory leaks. So the only clean alternative for older Perl versions is to manually restore $dbh's values:

my @attrs = qw(LongReadLen PrintError);
my %orig = ( );

my $dbh = DBI->connect(...) ...

# store the values away
$orig{$_} = $dbh->{$_} for @attrs;
# do local modifications
$dbh->{LongReadLen} = 40;
$dbh->{PrintError}  = 1;

# do something with the database handle
# ...

# now restore the values
$dbh->{$_} = $orig{$_} for @attrs;

Another thing to remember is that with some database servers it's possible to access more than one database using the same database connection. MySQL is one of those servers. It allows you to use a fully qualified table specification notation. So if there is a database foo with a table test and a database bar with its own table test, you can always use:

SELECT * FROM foo.test ...

or:

SELECT * FROM bar.test ...

No matter what database you have used in the database name string in the connect( ) call (e.g., DBI:mysql:foo:localhost), you can still access both tables by using a fully qualified syntax.

Alternatively, you can switch databases with USE foo and USE bar, but this approach seems less convenient, and therefore error-prone.

20.1.5.4. Cannot find the DBI handler

20.1.5.5. The morning bug

The error was caused by Apache::DBI returning an invalid connection handle (the server had closed it because of a timeout), and the script was dying on that error. The ping( ) method was introduced to solve this problem, but it didn't work properly until Apache::DBI Version 0.82 was released. In that version and after, ping( ) was called inside an eval block, which resolved the problem.

It's still possible that some DBD:: drivers don't have the ping( ) method implemented. The Apache::DBI manpage explains how to write it.

Another solution is to increase the timeout parameter when starting the database server. We usually start the MySQL server with the script safe_mysqld, so we modified it to use this option:

nohup $ledir/mysqld [snipped other options] -O wait_timeout=172800

The timeout value that we use is 172,800 seconds, or 48 hours. This change solves the problem, but the ping( ) method works properly in DBD::mysql as well.

20.1.5.6. Apache:DBI does not work

PERL_CHILD_INIT=1 PERL_STACKED_HANDLERS=1

or:

EVERYTHING=1

Turn on debug mode using the $Apache::DBI::DEBUG variable.

20.1.5.7. Skipping connection cache during server startup

10169 Apache::DBI PerlChildInitHandler
10169 Apache::DBI skipping connection cache during server startup
Database handle destroyed without explicit disconnect at
/usr/lib/perl5/site_perl/5.6.1/Apache/DBI.pm line 29.

If so, you are trying to open a database connection in the parent httpd process. If you do, the children will each get a copy of this handle, causing clashes when the handle is used by two processes at the same time. Each child must have its own unique connection handle.

To avoid this problem, Apache::DBI checks whether it is called during server startup. If so, the module skips the connection cache and returns immediately without a database handle.