MySQL connections and PHP forked processes

Posted in MySql and PHP -

It is possible to fork a process with PHP and have one or more child processes running. If a connection has already been established to a MySQL database and the child processes run queries it's quite likely a "MySQL server has gone away" or a "Lost connection to MySQL server during query" type error will occur. This post shows why, and how to solve the problem.

Example code

The first example below shows the database connection being established, then 3 children being forked with each running some sort of query.

mysql_connect($server, $username, $password);
msyql_select_db($database);

// do some database work before forking

$pidsCount = 0;
for($i = 0; $i < 3; $i++) {
	$pids[$pidsCount] = pcntl_fork();
	if($pids[$pidsCount]) {
		// i am the parent
		$pidsCount++;
	}
	else {
		// i am the child
		runChildProcess($i);
		exit();
	}
}

for($i = 0; $i < $pidsCount; $i++) {
	pcntl_waitpid($pids[$i], $status, WUNTRACED);
}

function runChildProcess($i) {

	mysql_query('SELECT something FROM sometable');
	echo "$i: ", mysql_error(), "\n";
	
}

Running this process a number of times will result in errors some of the time.

Reason for the error

The parent and child processes all share the same database connection. When the first child process exits it will disconnect from the database, which means the same connection all processes are using will be disconnected, causing any further queries to fail.

The solution

The solution is to disconnect from the database before forking the sub processes and then establish a new connection in each process. The fourth parameter also should be passed to the mysql_connect function as "true" to ensure a new link is established; the default is to share an existing connection is the login details are the same.

Using this solution, the above code can be rewritten as follows:

mysql_connect($server, $username, $password);
msyql_select_db($database);
// do some database work before forking
mysql_close();

$pidsCount = 0;
for($i = 0; $i < 3; $i++) {
	$pids[$pidsCount] = pcntl_fork();
	if($pids[$pidsCount]) {
		// i am the parent
		$pidsCount++;
	}
	else {
		// i am the child
		mysql_connect($server, $username, $password, true);
		msyql_select_db($database);
		runChildProcess($i);
		exit();
	}
}

for($i = 0; $i < $pidsCount; $i++) {
	pcntl_waitpid($pids[$i], $status, WUNTRACED);
}

function runChildProcess($i) {

	mysql_query('SELECT something FROM sometable');
	echo "$i: ", mysql_error(), "\n";
	
}

If the parent process doesn't need to do any database work before forking the child processes, then don't use lines 1 to 4 as there's no point connecting at that stage.



Related posts:


Comments