RE: [PHP-DB] Forking and database connections

Good point. Anything (children or parent) sharing the connection will get it closed on them. I was looking up persistent connections, then I thought you could just address the way you used the pcntl_* functions. I use mysql_* directly (where mysql_connect "Opens or reuses a connection to a MySQL server."), so you may need to look into PEAR::DB for persistent connections. Where in your code does it say you are using persistent connections? I haven't had the need to use them yet. For instance mysql_pconnect (the persistent counterpart) says "Second, the connection to the SQL server will not be closed when the execution of the script ends." If you are using the PEAR::DB persistent connection as documented, then I'd open a bug report. Again, I haven't used them. Regards, Dwight > -----Original Message----- > From: Chris Verges [mailto:chrisv@cyberswitching.com] > Sent: Monday, April 23, 2007 12:01 PM > To: Dwight Altman; php-db@lists.php.net > Subject: Re: [PHP-DB] Forking and database connections > > Hey Dwight, > > Thanks for the replies! When the first child thread closes, all of the DB > connections seem to close. That is, the $db->execute() statements will > fail > in the child threads after the first child thread closes. It's almost as > though separate connections are not being made, but they are instead > sharing > the same connection. This would make sense if persistent connection > pooling > was enabled, but I assumed there to be a "garbage collection" mechanism in > place to avoid prematurely closing a persistent connection if multiple > resources are using it. > > Again, thanks for the help! Any other ideas on what might be wrong, or > should I open a bug against it? > > Thanks! > Chris > > > On 4/23/07 8:12 AM, "Dwight Altman" <dwight@multicam.com> wrote: > > > Actually I suppose you need to loop with foreach to wait on all children > > before attempting any $db->execute, then after your foreach loop, get > your > > $db and execute. > > > > Regards, > > Dwight > > > >> -----Original Message----- > >> From: Dwight Altman [mailto:dwight@multicam.com] > >> Sent: Monday, April 23, 2007 10:09 AM > >> To: 'php-db@lists.php.net' > >> Subject: RE: [PHP-DB] Forking and database connections > >> > >> http://php.he.net/manual/en/function.pcntl-fork.php > >> says 'The reason > for > >> the MySQL "Lost Connection during query"...' like what you concluded, > >> although they grab a new $db connection in the first for loop > >> "} else if ( $pid ) {" I bet a child closes the one you create after > the > >> for loop while the parent is waiting in the foreach loop. > >> > >> In your foreach when you wait for each child, can you $db- > >execute($stmt, > >> $data) AFTER you pcntl_waitpid($pid, $status) ? Just reverse the > lines? > >> > >> I think after the wait is when the child closes the connection (since I > >> suppose you are reusing the same connection), so it is already closed. > >> > >> > >> Regards, > >> Dwight > >>> -----Original Message----- > >>> From: Chris Verges [mailto:chrisv@cyberswitching.com] > >>> Sent: Saturday, April 21, 2007 12:21 PM > >>> To: php-db@lists.php.net > >>> Subject: [PHP-DB] Forking and database connections > >>> > >>> Hey all, > >>> > >>> I'm writing a PHP script that queries a database for some records, > >> splits > >>> the records into N number of groups of equal size, and then creates N > >>> number > >>> of forks where each child handles one of the groups. During the > >> execution > >>> of each of the child processes, I'd like the parent process to update > >> the > >>> status of the job in the database. > >>> > >>> The problem is regarding my database connection pre- and post- fork. > >>> After > >>> reading the pcntl_fork() page on the PHP manual, I realize that the > >> child > >>> process inherits the file descriptor, and if the child process closes > >> the > >>> connection, then it is closed in the parent process. So for each > child > >>> process (because I have more than one), I reinitialize the database > >> link. > >>> I > >>> also reinitialize the database link for the parent process immediately > >>> after > >>> the fork. > >>> > >>> However, when a child process finishes, it seems like the database > link > >>> that > >>> I reinitialized in the parent process also disconnects. I thought a > >> fork > >>> copied the entire heap, and therefore would make two copies of the > >> object > >>> instances that would remain segmented for the life of the processes. > >>> Changes made to one copy of the heap wouldn't affect others. However, > >>> this > >>> doesn't seem to be the case. > >>> > >>> So at this point, my workaround is to wait until all of the child > >>> processes > >>> are finished, then re-initialize the database link, and give an > updated > >>> status message at the end rather than incrementally as child processes > >>> finish. > >>> > >>> Here's some proof-of-concept code that explains what I mean: > >>> > >>> <?php > >>> > >>> /* Include PEAR::DB */ > >>> require_once('DB.php'); > >>> > >>> # Database table definition > >>> # ------------------------- > >>> # CREATE TABLE logs ( > >>> # message VARCHAR(128) NOT NULL > >>> # ); > >>> > >>> /* Create the initial database connection for the parent process */ > >>> $dsn = 'mysql://test:test@localhost/testdb'; > >>> $db = DB::connect($dsn); > >>> if ( PEAR::isError($db) ) { > >>> die($db->getMessage() . "\n"); > >>> } > >>> > >>> /* This will be the common SQL statement for all inserts */ > >>> $sql = "INSERT INTO logs > >>> (message) VALUES (?);"; > >>> $stmt = $db->prepare($sql); > >>> > >>> /* Perform a DB update */ > >>> $data = array('Started parent process'); > >>> $db->execute($stmt, $data); > >>> > >>> /* Create the child processes */ > >>> $childPids = array(); > >>> for ( $i = 0; $i < 5; $i++ ) { > >>> $pid = pcntl_fork(); > >>> if ( $pid == -1 ) { > >>> die("\nUnable to fork!\n"); > >>> } else if ( $pid ) { > >>> /* Parent process */ > >>> echo "Child process $pid created\n"; > >>> array_push($childPids, $pid); > >>> } else { > >>> /* Child process */ > >>> $myPid = posix_getpid(); > >>> > >>> /* Create a new database connection for the child > >> process > >>> */ > >>> $db = DB::connect($dsn); > >>> if ( PEAR::isError($db) ) { > >>> die("\nChild process $myPid: " . $db- > >>> getMessage() > >>> . > >>> "\n" . $db->getDebugInfo() . > >>> "\n"); > >>> } > >>> > >>> $data = array("Child process $myPid"); > >>> $stmt = $db->prepare($sql); > >>> $db->execute($stmt, $data); > >>> > >>> /* Add some latency for testing purposes */ > >>> sleep(5); > >>> exit; > >>> } > >>> } > >>> > >>> /* Create a new database connection for the parent process */ > >>> $db = DB::connect($dsn); > >>> if ( PEAR::isError($db) ) { > >>> die("\nParent process: " . $db->getMessage() . "\n" > >>> . > >>> $db->getDebugInfo() . "\n"); > >>> } > >>> > >>> /* Wait for the children to finish */ > >>> foreach ( $childPids as $pid ) { > >>> $data = array("Parent process waiting on child process $pid"); > >>> $db->execute($stmt, $data); > >>> pcntl_waitpid($pid, $status); > >>> $data = array("Child process $pid is finished"); > >>> $db->execute($stmt, $data); > >>> } > >>> > >>> $data = array("Parent process is finished"); > >>> $db->execute($stmt, $data); > >>> > >>> ?> > >>> > >>> The command-line output of this code: > >>> > >>> $ php forking-proof-of-concept.php > >>> Child process 27012 created > >>> Child process 27013 created > >>> Child process 27014 created > >>> Child process 27015 created > >>> Child process 27016 created > >>> > >>> Child process 27016: DB Error: unknown error > >>> [nativecode=2013 ** Lost connection to MySQL server during query] ** > >>> mysql://test:test@localhost/testdb > >>> > >>> And finally the database entries after running the code: > >>> > >>> mysql> select * from logs; > >>> +------------------------+ > >>> | message | > >>> +------------------------+ > >>> | Started parent process | > >>> | Child process 27012 | > >>> | Child process 27013 | > >>> | Child process 27014 | > >>> | Child process 27015 | > >>> +------------------------+ > >>> 5 rows in set (0.00 sec) > >>> > >>> Any help in understanding this is appreciated! > >>> > >>> Thanks! > >>> Chris > >>> > >>> -- > >>> PHP Database Mailing List (http://www.php.net/) > >>> To unsubscribe, visit: http://www.php.net/unsub.phpäU›¸jDSÅZ > >>> ’TD>