Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:30124
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: error (pb1.pair.com: domain hristov.com from 85.92.73.163 cause and error)
Message-ID: <4667F4AE.7000204@hristov.com>
Date: Thu, 07 Jun 2007 15:06:06 +0300
User-Agent: Thunderbird 1.5.0.7 (X11/20060911)
MIME-Version: 1.0
To: Antony Dovgal <antony@zend.com>
CC: Peter Christensen <pch@ordbogen.com>,  internals@lists.php.net, 
 Georg Richter <georg@mysql.com>
References: <4667EEEB.8040209@ordbogen.com> <4667F2B3.9030308@zend.com>
In-Reply-To: <4667F2B3.9030308@zend.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Subject: Re: [PHP-DEV] Bug regarding unbuffered MySQL queries and persistent
 connections
From: php@hristov.com (Andrey Hristov)

 Hi,
Antony Dovgal wrote:
> Hello Peter.
> 
> The analysis looks correct.
> 
> I'm going to apply the patch soon.
> Georg, Andrey, any objections?

no

> 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
>>
> 
> 

Andrey