Lost connection error with prepare_cached

Hello all. I'm trying to solve a bug in a daemon which uses multiple
processes to handle jobs in a queue. The code is bound up in a rather
large Class::DBI app which makes posting a sample difficult. The
error I'm getting is this:

DBD::mysql::st execute failed: Lost connection to MySQL server
during query

This error always occurs in the parent process, and the children never
encounter an error. Here are the things I've determined:

- The children are definitely opening their own DB connections.

- Doing *nothing* in the forked child processes still triggers the
error condition.

- Not forking at all makes the problem go away.

- Only statements prepared with prepare_cached() cause the error
seen. Even a simple 'SELECT NOW() FROM foo' will cause the error
if prepared with prepare_cache() and executed frequently in the
parent.

- The error doesn't always occur immediately and sometimes many
children are forked and reaped successfully.

- I can change the way the statements are executed (using
selectall_arrayref manually instead of Class::DBI) to generate a
different error:

DBD::mysql::db selectall_arrayref failed: fetch() without execute()

- Disconnecting and reconnecting before every DBI call in the parent
process fixes the problem.

- DBI's trace output doesn't show me anything interesting (but I'd
be happy to send it to anyone that wants a look).

- MySQL isn't logging any errors, even with --log-warning=2.

- Setting mysql_auto_reconnect doesn't help, and neither does
InactiveDestroy.

- Clearing the CachedKids hash before each statement is executed
doesn't help.

I'm pretty much out of ideas at this point. If anyone has any
suggestions or ideas about how to attack the problem, please send
them.

In case it helps, this is Perl 5.8.6, DBI 1.48, DBD::mysql 3.002,
MySQL 4.1.12-standard-log.

Thanks,
-sam

Re: Lost connection error with prepare_cached

Child process will inherit the parents db connection and $dbh.
When the child process exits perl will DESTROY all objects,
included the inherited $dbh. That will disconnect the mysql
socket connection 'behind the back' of the parent.

You must set InactiveDestroy on the inherited $dbh to avoid this.
Double check it.

Tim.

On Sat, Sep 10, 2005 at 06:26:50PM -0400, Sam Tregar wrote:
> Hello all. I'm trying to solve a bug in a daemon which uses multiple
> processes to handle jobs in a queue. The code is bound up in a rather
> large Class::DBI app which makes posting a sample difficult. The
> error I'm getting is this:
>
> DBD::mysql::st execute failed: Lost connection to MySQL server
> during query
>
> This error always occurs in the parent process, and the children never
> encounter an error. Here are the things I've determined:
>
> - The children are definitely opening their own DB connections.
>
> - Doing *nothing* in the forked child processes still triggers the
> error condition.
>
> - Not forking at all makes the problem go away.
>
> - Only statements prepared with prepare_cached() cause the error
> seen. Even a simple 'SELECT NOW() FROM foo' will cause the error
> if prepared with prepare_cache() and executed frequently in the
> parent.
>
> - The error doesn't always occur immediately and sometimes many
> children are forked and reaped successfully.
>
> - I can change the way the statements are executed (using
> selectall_arrayref manually instead of Class::DBI) to generate a
> different error:
>
> DBD::mysql::db selectall_arrayref failed: fetch() without execute()
>
> - Disconnecting and reconnecting before every DBI call in the parent
> process fixes the problem.
>
> - DBI's trace output doesn't show me anything interesting (but I'd
> be happy to send it to anyone that wants a look).
>
> - MySQL isn't logging any errors, even with --log-warning=2.
>
> - Setting mysql_auto_reconnect doesn't help, and neither does
> InactiveDestroy.
>
> - Clearing the CachedKids hash before each statement is executed
> doesn't help.
>
> I'm pretty much out of ideas at this point. If anyone has any
> suggestions or ideas about how to attack the problem, please send
> them.
>
> In case it helps, this is Perl 5.8.6, DBI 1.48, DBD::mysql 3.002,
> MySQL 4.1.12-standard-log.
>
> Thanks,
> -sam
>
>

Re: Lost connection error with prepare_cached

On Sun, 11 Sep 2005, Tim Bunce wrote:

> Child process will inherit the parents db connection and $dbh.
> When the child process exits perl will DESTROY all objects,
> included the inherited $dbh. That will disconnect the mysql
> socket connection 'behind the back' of the parent.
>
> You must set InactiveDestroy on the inherited $dbh to avoid this.
> Double check it.

Thanks Tim, you're exactly right. I thought this might have been the
problem but when I tested it I was actually setting InactiveDestroy on
the wrong handle. I'll see if I can come up with a POD patch for DBI
to make it clearer how this is supposed to work, if that's ok with you.

-sam

Re: Lost connection error with prepare_cached

On Sun, Sep 11, 2005 at 03:55:47PM -0400, Sam Tregar wrote:
> On Sun, 11 Sep 2005, Tim Bunce wrote:
>
> > Child process will inherit the parents db connection and $dbh.
> > When the child process exits perl will DESTROY all objects,
> > included the inherited $dbh. That will disconnect the mysql
> > socket connection 'behind the back' of the parent.
> >
> > You must set InactiveDestroy on the inherited $dbh to avoid this.
> > Double check it.
>
> Thanks Tim, you're exactly right. I thought this might have been the
> problem but when I tested it I was actually setting InactiveDestroy on
> the wrong handle. I'll see if I can come up with a POD patch for DBI
> to make it clearer how this is supposed to work, if that's ok with you.

Patches welcome!

Tim.