Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:30122
Return-Path: <pch@ordbogen.com>
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Delivered-To: mailing list internals@lists.php.net
Received: (qmail 58314 invoked by uid 1010); 7 Jun 2007 11:42:30 -0000
Delivered-To: ezmlm-scan-internals@lists.php.net
Delivered-To: ezmlm-internals@lists.php.net
Received: (qmail 58298 invoked from network); 7 Jun 2007 11:42:30 -0000
Received: from unknown (HELO lists.php.net) (127.0.0.1)
  by localhost with SMTP; 7 Jun 2007 11:42:30 -0000
Authentication-Results: pb1.pair.com header.from=pch@ordbogen.com; sender-id=unknown
Authentication-Results: pb1.pair.com smtp.mail=pch@ordbogen.com; spf=permerror; sender-id=unknown
Received-SPF: error (pb1.pair.com: domain ordbogen.com from 83.221.143.170 cause and error)
X-PHP-List-Original-Sender: pch@ordbogen.com
X-Host-Fingerprint: 83.221.143.170 mail.ordbogen.com Linux 2.5 (sometimes 2.4) (4)
Received: from [83.221.143.170] ([83.221.143.170:40595] helo=mail3.coolsystems.dk)
	by pb1.pair.com (ecelerity 2.1.1.9-wez r(12769M)) with ESMTP
	id 87/39-50146-5FEE7664 for <internals@lists.php.net>; Thu, 07 Jun 2007 07:41:43 -0400
Received: from localhost (localhost [127.0.0.1])
	by mail3.coolsystems.dk (Postfix) with ESMTP id CAE051EF2D1
	for <internals@lists.php.net>; Thu,  7 Jun 2007 13:41:38 +0200 (CEST)
Received: from mail3.coolsystems.dk ([83.221.143.170])
	by localhost (Angel [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 10638-10 for <internals@lists.php.net>;
	Thu, 7 Jun 2007 13:41:37 +0200 (CEST)
Received: from [192.168.1.113] (0x5735692a.odnxx12.adsl-dhcp.tele.dk [87.53.105.42])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail3.coolsystems.dk (Postfix) with ESMTP id 930381EF2C6
	for <internals@lists.php.net>; Thu,  7 Jun 2007 13:41:37 +0200 (CEST)
Message-ID: <4667EEEB.8040209@ordbogen.com>
Date: Thu, 07 Jun 2007 13:41:31 +0200
User-Agent: Mozilla-Thunderbird 2.0.0.0 (X11/20070601)
MIME-Version: 1.0
To:  internals@lists.php.net
Content-Type: multipart/mixed;
 boundary="------------050306010909040104080407"
X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at coolsystems.dk
Subject: Bug regarding unbuffered MySQL queries and persistent connections
From: pch@ordbogen.com (Peter Christensen)

--------------050306010909040104080407
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I'm new to the list, so I apologize if this is the wrong place to post!

Anyway, several years now, I've been struggling with errors such as:

PHP Warning: <function>: <num> is not a valid MySQL result resource in 
<file>

at various places on the sites I work on. But the warnings only happens 
a few times a day, despite the thousands of visitors we have daily. 
Besides, I've carefully verified that the PHP code is in fact not wrong 
(like duplicate mysql_free_result and the likes). Eventually we ignored 
the problem due to its rarity.

Lately however, the likelihood of errors at certain places in our system 
increased, which made my boss grant me indefinitely time to solve the 
problem.

After some three to four days of analysis of live memory dumps, I traced 
the problem to be the active_result_id field within the php_mysql_conn 
structure. Apparently this field is not cleared when a persistent MySQL 
connection is pulled from the list, so should you accidentally hit that 
same resource id and then perform another query, your previous query was 
freed.

An example:

bug.php:

<?php

header("Content-Type: text/plain");

mysql_pconnect("hostname", "username", "password");

if ($_GET["step"] == 1)
{
   $res = mysql_unbuffered_query("SELECT * FROM db.table");
   echo("Created resource ".(int)$res."\n");
   mysql_free_result($res);
}
else if ($_GET["step"] == 2)
{
   $res1 = mysql_query("SELECT * FROM db.table");
   echo("Created resource ".(int)$res1."\n");
   $res2 = mysql_query("SELECT * FROM db.table");
   echo("Created resource ".(int)$res2."\n");
   mysql_free_result($res2);
   if (!is_resource($res1))
     echo("Resource ".(int)$res1." was destroyed\n");
   else
     mysql_free_result($res1);
}
?>


Now, if I open bug.php?step=1 and then bug.php?step=2 within the same 
HTTP connection, I get:


bug.php?step=1:
Created resource 4


bug.php?step=2:
Created resource 4
Created resource 5
Resource 4 was destroyed


The thing is, when bug.php?step=1 is called, a MySQL connection is 
established and resource 3 contains a MySQL structure and 
active_result_id is set to 0. Then we perform an unbuffered query which 
get resource id 4. This id is stored in active_result_id.

When we call bug.php?step=2 immediately after, we get the very same 
MySQL connection as well as the active_result_id set to 4. Then we 
perform the first query, and while mysql_query actually looks for 
resource #4, it doesn't find it and so does nothing. But then it 
performs the query and creates resource #4. Finally we perform the 2nd 
query, but only this time resource #4 actually exist, so the resource is 
freed before the query is performed, thus resulting in subsequent errors.


So, to summarise the problem is really quite simple. active_result_id is 
only cleared within php_mysql_do_connect if the connection is not 
persistent, or if a persistent connection was not found. So the natural 
solution is simply to clear it as it should (patch attached)


I'm sorry if my mail is too large, but my experience with other open 
source projects, is that a full description of the cause and reason of a 
patch increase the chance of the patch getting accepted (and understood).


For the record, the problem apparently affects all versions of PHP. 
(tested on various home-built versions up to and including 4.4.7, as 
well as Debian built PHP 5.2.0-8).
I might also contact the Debian PHP maintainers regarding the bug, so 
that I won't have to use home-built packages on all our servers (or turn 
off persistent connections).


Best regards,
  Peter Christensen

--------------050306010909040104080407
Content-Type: text/x-patch;
 name="active_result_id.diff"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="active_result_id.diff"

diff -Nru php-src.old/ext/mysql/php_mysql.c php-src.new/ext/mysql/php_mysql.c
--- php-src.old/ext/mysql/php_mysql.c	2007-06-07 13:20:09.000000000 +0200
+++ php-src.new/ext/mysql/php_mysql.c	2007-06-07 13:20:19.000000000 +0200
@@ -732,6 +732,7 @@
 #endif
 
 			mysql = (php_mysql_conn *) le->ptr;
+			mysql->active_result_id = 0;
 		}
 		ZEND_REGISTER_RESOURCE(return_value, mysql, le_plink);
 	} else { /* non persistent */

--------------050306010909040104080407--