When Apache::DBI receives a connection request, before it decides to use an existing cached connection it insists that the new connection be opened in exactly the same way as the cached connection. If you have one script that sets AutoCommit and one that does not, Apache::DBI will make two different connections. So, for example, if you have limited Apache to 40 servers at most, instead of having a maximum of 40 open connections, you may end up with 80.

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.