This one caught me a few days ago, but I didn’t have to time to concentrate on it (I was being harassed by screaming kids at the time). I came across the same problem today, and managed to figure it out with the help of some useful prompts from the ILUGgers.

The problem manifests as a query failing to run for no apparent reason.

An example of some code that exhibits the bug (taken from the KFM project:

function _moveDirectory($from,$to){
	global $db;
	$q=$db->query('select * from directories where id="'.$from.'"');
	$from=$q->fetch();
	$q=$db->query('select * from directories where id="'.$to.'"');
	$to=$q->fetch();
	if(strpos($to['physical_address'],$from['physical_address'])===0)return 'error: cannot move a directory into its own sub-directory'; # TODO: new string
	if(file_exists($to['physical_address'].'/'.$from['name']))return 'error: "'.$to['physical_address'].'/'.$from['name'].'" already exists'; # TODO: new string
	rename($from['physical_address'],$to['physical_address'].'/'.$from['name']);
	if(!file_exists($to['physical_address'].'/'.$from['name']))return 'error: could not move directory'; # TODO: new string
	$len=strlen(preg_replace('#/[^/]*$#','',$from['physical_address']));
	$fugly='update directories set physical_address=("'.addslashes($to['physical_address']).'"||substr(physical_address,'.($len+1).',length(physical_address)-'.($len).')) where physical_address like "'.addslashes($from['physical_address']).'/%" or id="'.$from['id'].'"';
	$db->exec($fugly) or die('error: '.print_r($db->errorInfo(),true));
	$db->exec('update directories set parent="'.$to['id'].'" where id="'.$from['id'].'"') or die('error: '.print_r($db->errorInfo(),true));
	return _loadDirectories(1);
}

The problem is that the SQL statement $fugly (so named because it’s fucking ugly) and the following one do not run, and return an error saying the database is locked. This is despite the fact that earlier in the same function, we’ve read from the database with no problems.

After much banging of heads, I found the problem – the variable $q holds a lock on the database, but it’s a read lock. In order to change to ‘write’ mode, you need to free up that variable before you attempt the write.

function _moveDirectory($from,$to){
	global $db;
	$q=$db->query('select * from directories where id="'.$from.'"');
	$from=$q->fetch();
	$q=$db->query('select * from directories where id="'.$to.'"');
	$to=$q->fetch();
	$q=null;
	if(strpos($to['physical_address'],$from['physical_address'])===0)return 'error: cannot move a directory into its own sub-directory'; # TODO: new string
	if(file_exists($to['physical_address'].'/'.$from['name']))return 'error: "'.$to['physical_address'].'/'.$from['name'].'" already exists'; # TODO: new string
	rename($from['physical_address'],$to['physical_address'].'/'.$from['name']);
	if(!file_exists($to['physical_address'].'/'.$from['name']))return 'error: could not move directory'; # TODO: new string
	$len=strlen(preg_replace('#/[^/]*$#','',$from['physical_address']));
	$fugly='update directories set physical_address=("'.addslashes($to['physical_address']).'"||substr(physical_address,'.($len+1).',length(physical_address)-'.($len).')) where physical_address like "'.addslashes($from['physical_address']).'/%" or id="'.$from['id'].'"';
	$db->exec($fugly) or die('error: '.print_r($db->errorInfo(),true));
	$db->exec('update directories set parent="'.$to['id'].'" where id="'.$from['id'].'"') or die('error: '.print_r($db->errorInfo(),true));
	return _loadDirectories(1);
}

The conclusion is that you can make a load of read requests in a row, or a load of write requests in a row, but if you are mixing the query types, you need to free the result each time.