DB Connections resetting

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

I have a perl daemon script which gets data from DB and for each row forks a child process which will make a web service request. My script ensures that the number of forks will never be greater than 5.

Both the parent and child processes uses Rose:: DB:: Object connection to access and update mysql db. During the execution, the parent process database connection is getting reset.

Error:
process_webservice stopped get_objects() - DBD::mysql::st execute failed: Lost connection to MySQL server during query at /home/perl/5.8/Rose/DB/Object/Manager.pm

Any pointers on how to resolve this issue ?

Thanks

Comment on DB Connections resetting

Replies are listed 'Best First'.
Re: DB Connections resetting by dsheroh (Monsignor) on Nov 20, 2009 at 13:50 UTC
As WizardOfUz suggested, double-check that your children aren't sharing the parent's DB connection and then closing it out from under the parent's feet. If that's not the issue, try setting: `$dbh->{mysql_auto_reconnect} = 1;` [download] after opening your connection.	[reply] [d/l]
Re: DB Connections resetting by WizardOfUz (Friar) on Nov 20, 2009 at 11:13 UTC
Are you sure that your child processes are not re-using their parent's database connection?	[reply]
Re^2: DB Connections resetting by vmraj (Novice) on Nov 23, 2009 at 07:42 UTC
I printed dbh and it gives different mysql_thread_id. Doesn't it mean that they are different connections? If I am wrong how do i find out whether they are the same connection or not.	[reply]
Re^3: DB Connections resetting by afoken (Chancellor) on Nov 23, 2009 at 16:35 UTC
Look at your code. When you create a DBI connection and fork() after that, your child processes will try to use the DBI connection from the parent, and usually fail miserably. Create the connection after fork()ing, not before. If you need a connection before you fork(), close it before calling fork(). Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re: DB Connections resetting by siracusa (Friar) on Nov 22, 2009 at 00:26 UTC
If you grab the SVN version of Rose::DB, which is thread- and fork-safe, it should automatically handle this situation for you. (These changes will go out in the next CPAN release of Rose::DB.)	[reply]
Re^2: DB Connections resetting by siracusa (Friar) on Jan 03, 2010 at 16:10 UTC
This version is now on CPAN.	[reply]
Re: DB Connections resetting by zwon (Abbot) on Nov 22, 2009 at 11:21 UTC
Instead of forking child process for every web request, and consequently establishing new connection to MySQL, you can use some event oriented framework like AnyEvent::HTTP, POE::Component::Client::HTTP, or just Event or EV and perform all requests in single process.	[reply]