Ima::DBI and connections at Apache startup

[prev] [thread] [next] [Date index for 2004/10/04]

Hi,

In short, combining Ima::DBI, Class::DBI::mysql and Apache opened 
database connections at startup time and kept them across forking
causing discomfort...


I've been encountering irregular database (mysql) errors with our
mod_perl application, errors like:
 
 DBD::mysql::st fetchrow_array failed: fetch() without execute() [for
 Statement "SELECT division, user FROM user_division WHERE id=? "] at
 /usr/local/share/perl/5.6.1/DBIx/ContextualFetch.pm line 87.

But the message and location varied, and wasn't easily replicated in
the wild.  In a testing environment with a limited number of server
processes, starting a long, database-intensive download would interfere
causing various bizarre database errors.

Independent apache processes in -X mode did not cause errors suggesting
that the problem was a connection being opened in the httpd parent.


My Class::DBI base class calls set_db and then I was using
Class::DBI::mysql to find the database metadata.  This 
happened in my apache_startup.pl and used $class->db_Main to access
the database (hard-coded).

Ima::DBI->db_Main is a closure that caches the database handle, only
calling DBI->connect_cached when ping-ing fails.  
In my case, this handle caching seemed to lead to the errors.

Simply disconnecting the handle didn't fix it, although I felt 
it should (???) and since there is no way to clear the closure's 
handle it was difficult to fix.  It's still not fully resolved, 
but moving the $dbh of _mk_db_closure inside the sub stops any 
caching and works for now.

I also has Apache::DBI loaded and thought that DBI automagically
used it.  Investigating, I found this to be true but while
Apache::DBI is mod_perl aware and doesn't cache during server
startup, Ima::DBI caches regardless.  The impression I had from
reading (long ago) the docs of Ima::DBI was that it just used
connect_cached and left the caching to DBI.

The _mk_db_closure seems to be there for 3 reasons:
 1) delay connecting until needed
 2) remembering connection parameters (in the closure)
 3) db handle caching and ping-ing

3) is supplied by Apache::DBI in a much more mod_perl friendly 
way (with ping timeouts, connect_on_init, cleanup handlers).

2) is supplied by DBI::db->clone (perhaps only experimentally?).


My current hack of Ima::DBI need replacing, I'm not yet sure how,
but I want to see if this is an isolated problem.  The list
touched on it some time ago but it didn't seem to get resolved.

>From the "Apache pre-forking" thread on this list:

On Tue, 2004-08-03 at 07:50, Perrin Harkins wrote: 
> On Mon, 2004-08-02 at 17:19, Tim Bunce wrote:
> > It doesn't cache connections, but does it (or something else) explicitly
> > disconnect before the fork?
> 
> Good point, it does not.  Looking back at the code where I used this
> under mod_perl with set_up_table(), I was fetching my own database
> handle.  That was safe, because I went straight through Apache::DBI. 
> Ima::DBI::_mk_db_closure(), which is called if you use set_db(), would
> make the connection persistent even during Apache startup.
> 
> Ima::DBI does contain a $dbh->FETCH('Active') && $dbh->ping check, but
> I'm not sure if that's enough to tell that a handle was opened before
> forking.  Maybe an additional check is needed.

I think an additional check is needed, take it from Apache::DBI.
This is one approach, make Ima::DBI mod_perl aware.

Another approach is to let DBI handle it and just call connect_cached.
This'd be slower, I guess.  But at least then Ima::DBI isn't duplicating
Apache::DBI and DBI's functions.  Perhaps DBI's clone and connect_cached
and replace this.  Only the connection delaying function needs to be
retained.

See also Re: Apache::DBI and DBIx::* by Perrin Harkins
http://mathforum.org/epigone/modperl/plertrarblerm/1092428520.6279.185.camel@xxxxxxxxx.xxxxxxxxxxx

So what is to be done about _mk_db_closure?

Brad


Class::DBI: 0.96
Ima::DBI: 0.33
Class::DBI::mysql: 0.16
DBI: 1.43
Apache::DBI: 0.94
Apache: 1.27
Apache::Session: 1.54
Apache::Session::Flex: 1.01 (mysql w/o locking)

perl This is perl, v5.6.1 built for i386-linux
apache Server version: Apache/1.3.27 (Unix)
mysql  Ver 12.22 Distrib 4.0.20, for pc-linux-gnu (i386)
       using innodb tables

        -- 
         ... But in the end, the details of a matter are important. The right 
 or wrong of one's way of doing thing is found are trivial matters.
                              -- Hagakure http://bereft.net/hagakure/