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

Hi fellow monks,

I use DBI to connect to a MySQL database on a remote server (Linux). The main Perl application forks a couple of child processes which all do queries on the database. Each process opens its own database connection on startup.

The problem is that the main process (which forks three child processes at startup and when needed) suffers a lot of "DBI lost connection" errors. The child processes do the same, but never produce the same error: 

11-06-2003 13:31:13: [ERROR=5] DBD::mysql::st execute failed: Lost con
+nection to MySQL server during query at ... line 147.
11-06-2003 13:32:00: [ERROR=5] DBD::mysql::st execute failed: Lost con
+nection to MySQL server during query at ... line 147.
11-06-2003 13:32:52: [ERROR=5] DBD::mysql::st execute failed: Lost con
+nection to MySQL server during query at ... line 147.
11-06-2003 13:36:45: [ERROR=5] DBD::mysql::st execute failed: Lost con
+nection to MySQL server during query at ... line 147.
11-06-2003 13:41:15: [ERROR=5] DBD::mysql::st execute failed: Lost con
+nection to MySQL server during query at ... line 147.
11-06-2003 13:41:30: [ERROR=5] DBD::mysql::db do failed: Lost connecti
+on to MySQL server during query at ... line 194.
11-06-2003 13:47:14: [ERROR=5] DBD::mysql::st execute failed: Lost con
+nection to MySQL server during query at ... line 147.

[download]
Why is this happening only on the main Perl program and not in the child processes? They all use the same Database package. Can it have something to do with the fact this process forks of child processes and this causes the Database connection to be closed/lost?

Any input is highly appreciated.

TIA, Marcel

Replies are listed 'Best First'.
Re: DBI lost connection errors
by runrig (Abbot) on Jun 11, 2003 at 16:33 UTC
    The child processes might be closing the parent's database handle. Look at the InactiveDestroy attribute in the DBI docs. This should be set to true in the child processes (after forking) for the parent db handle. Or, if possible, you should only connect the parent db handle after the forking is done.

    And just to be sure, the child processes get their own db handle after forking, right?

[reply]
      ++runig

      This is the problem, I already found out the child processes automatically close the database connections of the parent but couldn't find a way to prevent this. Never noticed this attribute, but InactiveDestroy is the answer for this problem when using DBI and forking childs.

      I've set the InactiveDestroy attribute to true on the parent's database handles and it runs smoothly now.

      Thanks alot!
[reply]
        I've set the InactiveDestroy attribute to true on the parent's database handles and it runs smoothly now.
        Did you read the part where it says InactiveDestroy should be set to true in the parent and false in the child, or vice-versa, but not true in both? If you are using the handle in the parent (and you usually should not fork and then use an existing handle in the child), then you should set it to false in the parent, fork, then set it to true in the child right after forking. Or you could set it to true, fork, then set it back to false in the parent right after forking.
[reply]
Re: DBI lost connection errors
by barrd (Canon) on Jun 11, 2003 at 13:03 UTC
[reply]
      Hi barrd,

      Thanks for your reply, I've already checked these links. I am almost sure it has something to do with the Perl code, since it only affects the main process and not its childs.

      If I take a look at the MySQL connections, the connection of the main process never changes, although the connection is lost quite often.

      Regards, Marcel
[reply]
        Hi again Marcello,

        Hmmm, at a loss now, though maybe if you post some code that might help. I'm going to keep a close eye on this thread as I will be doing something similar in the next couple of months or so (which is why I knew the links at mysql.com). Hopefully after posting some code a more knowledgeable Monk might be able to shed some light.

        Sorry, I did try though... ;) Good luck.

        barrd

[reply]

Back to Seekers of Perl Wisdom