Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:30123
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: pass (pb1.pair.com: domain zend.com designates 212.25.124.162 as permitted sender)
Message-ID: <4667F2B3.9030308@zend.com>
Date: Thu, 07 Jun 2007 15:57:39 +0400
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Peter Christensen <pch@ordbogen.com>
CC:  internals@lists.php.net, Georg Richter <georg@mysql.com>, 
 Andrey Hristov <andrey@mysql.com>
References: <4667EEEB.8040209@ordbogen.com>
In-Reply-To: <4667EEEB.8040209@ordbogen.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Subject: Re: [PHP-DEV] Bug regarding unbuffered MySQL queries and persistent
 connections
From: antony@zend.com (Antony Dovgal)

Hello Peter.

The analysis looks correct.

I'm going to apply the patch soon.
Georg, Andrey, any objections?

On 07.06.2007 15:41, Peter Christensen wrote:
> 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
> 


-- 
Wbr, 
Antony Dovgal