ID: 47712
Updated by: and...@php.net
Reported By: ninzya at inbox dot lv
Status: Feedback
Bug Type: MySQL related
Operating System: Windows XP
PHP Version: 5.3.0RC2
Assigned To: mysql
New Comment:
Just tried again. 2 million requests, concurrency of 150, which led to
load of 90 on the box - pretty hefty, where a problem should show itself
and again no problem except that in some cases connection to MySQL
couldn't be established, which is to be expected. MySQL was set up to
have 1000 maximal connections, so it is not the bottleneck, just system
resources.
Previous Comments:
------------------------------------------------------------------------
[2009-06-08 12:46:49] and...@php.net
Hi, I switched on the zval cache and tried to reproduce the problem,
with no success.
I tested with Apache2 MPM. `ab` concurrency level starting from 20 up
to 100. Time from 30 to 45 seconds. I got, with your code, only 3 types
of errors. I added mysql_connect() myself
- mysql_connect() fails because cannot have more connections on the
unix socket. Using TCP/IP exhausts the resources earlier for connecting
to MySQL
- mysql_query() tries to connect, although the connection has failed,
typical for mysql_query()! It fails :
-- password problem
-- no resources
I did not see any corruption whatsoever. Difference from you is that I
tested on Linux, as server. ab was running on a Mac connected over a
FastEthernet switch.
Here follows the code I used:
<?php
$conn = mysql_connect("localhost", "root", "root");
mysql_select_db("test");
$result = mysql_query("SELECT a,b FROM stress_test");
if (!$result) {
echo 'Could not run query: ' . mysql_error();
exit;
}
/* Use the result, assuming we're done with it afterwards */
$row = mysql_fetch_assoc($result);
/* Now we free up the result and continue on with our script */
mysql_free_result($result);
var_dump($row);
?>
------------------------------------------------------------------------
[2009-06-08 10:36:49] and...@php.net
Is is possible to provide a package which we can run, even it is not
20-30lines of code, so we can try to reproduce the problem?
In the meanwhile I have committed a change to mysqlnd which I suppose
should lead to the problem disappearing - switching the zval cache off.
Could you try downloading and testing a binary from
http://windows.php.net/snapshots/
New snapshot should be ready in a few hours. I suppose you will need
the VC6 build.
Thank you!
Andrey
------------------------------------------------------------------------
[2009-05-12 09:42:13] ninzya at inbox dot lv
This bug is still present in PHP 5.3.0RC2 and is critical to me.
------------------------------------------------------------------------
[2009-04-19 12:04:11] ninzya at inbox dot lv
Changed bug summary.
My configuration:
Apache 2.2,
PHP 5.3.0RC1 as module,
Windows XP SP3,
MySQL community server 5.1.33
Bug being hit during high concurrency running apache benchmark: "ab -c
30 -n 10000". See previous posts for details.
------------------------------------------------------------------------
[2009-04-12 15:08:30] ninzya at inbox dot lv
Please excuse me for the delay. I have been figuring out what's the
cause of this bug and finally i have something to come up with.
The problem is in mysql_free_result function. Not in itself, but it's
behavior has changed. Consider the following example from the PHP manual
on mysql_free_result:
Example #1 A mysql_free_result() example
<?php
$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");
if (!$result) {
echo 'Could not run query: ' . mysql_error();
exit;
}
/* Use the result, assuming we're done with it afterwards */
$row = mysql_fetch_assoc($result);
/* Now we free up the result and continue on with our script */
mysql_free_result($result);
echo $row['id'];
echo $row['email'];
?>
This code with PHP 5.3.0 with mysqlnd will now fail under high
concurrency, because mysql_free_result will affect $row variable and
allow another threads to use it's zval to store data. I suspect that
mysql_free_result marks the referenced by $row data as 'free' and
another threads pick that zval up and work with it. As soon as you
release result, another thread may corrupt $row variable. Test this
example under high concurrency and you will get different values for
$row['id'] and $row['email']. Run 10000 req. test and some of them will
fail to produce correct output.
In my framework i use mysql_free_result before referencing last fetched
row of result set very often, that's why i hit this bug 'randomly'.
I have another example i just tested, this is part of my framework. The
idea is the same - mysql_free_result is being called before actually
using fetched data array.
$con =mysql_pconnect( 'localhost', 'root', '');
mysql_select_db( 'ewe10');
$q =mysql_query( $sql ='SELECT id, title, keywords, descr,
template_id, `title`, `keywords`, `descr`, `template_id`
FROM pages
WHERE node_id =11
AND alt_name =\'welcome\'
LIMIT 0, 1;', $con);
$row =mysql_fetch_assoc( $q);
mysql_free_result( $q);
if( $row['id'] ===null || $row['template_id'] !=8567 || $row['title']
!='My test page' || $row['keywords'] !='asdasd' || $row['descr']
!='asdasd') {
trigger_error( 'FAIL!', E_USER_WARNING);
trigger_error( 'SQL: ' .$sql, E_USER_WARNING);
ob_start();
var_dump( $row);
trigger_error( ob_get_clean(), E_USER_WARNING);
die('NOT OK');
}
die('OK');
SQL for the table:
CREATE TABLE `ewe10`.`pages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique page
id',
`node_id` int(10) unsigned NOT NULL COMMENT 'node id in which this
page is',
`title` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
COMMENT 'page title',
`keywords` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
COMMENT 'page keywords',
`descr` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT
'page description',
`template_id` int(10) unsigned DEFAULT NULL COMMENT 'template this
page is using (if NULL, template was deleted from database and this page
is stored as a backup)',
`type` enum('MAIN','PAGE') CHARACTER SET latin1 NOT NULL DEFAULT
'PAGE' COMMENT 'page type',
`alt_name` varchar(45) CHARACTER SET latin1 NOT NULL COMMENT 'page
alternative name',
`date_add` datetime NOT NULL COMMENT 'when page was added',
PRIMARY KEY (`id`),
UNIQUE KEY `NODE_ID_ALT_NAME` (`node_id`,`alt_name`) USING BTREE,
KEY `FK_pages_templates` (`template_id`),
KEY `TYPE` (`type`),
KEY `FK_pages_map` (`node_id`),
KEY `DATE_ADD` (`date_add`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
the only row in this table is shown on the screenshot i referenced in
previous report.
To address the issue more quickly, i would like to give you a hint -
after calling mysql_free_result there is no possibility to fetch any
more rows, so there's nothing to worry about mysql_fetch_*(), but there
is possibility that the last fetched row may be referenced. This means -
even if mysql_free_result was called, last fetched row must remain
locked in mysqlnd internal zval cache until the variable is
implicitly/explicitly unset.
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/47712
--
Edit this bug report at http://bugs.php.net/?id=47712&edit=1
