Bug regarding unbuffered MySQL queries and persistent connections

Hi,
Antony Dovgal wrote:

Hello Peter.

The analysis looks correct.

I'm going to apply the patch soon.
Georg, Andrey, any objections?

no

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