php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #36144 only one row when using persistent connections and unbuffered queries
Submitted: 2006-01-24 15:30 UTC Modified: 2011-11-04 10:04 UTC
Votes:9
Avg. Score:4.6 ± 0.7
Reproduced:6 of 6 (100.0%)
Same Version:0 (0.0%)
Same OS:4 (66.7%)
From: php at kanariepiet dot com Assigned: mysql (profile)
Status: No Feedback Package: MySQL related
PHP Version: 5.1.2 OS: Linux
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: php at kanariepiet dot com
New email:
PHP Version: OS:

 

 [2006-01-24 15:30 UTC] php at kanariepiet dot com
Description:
------------
Assume you have a webserver serving two PHP scripts. Both PHP 
scripts use persistent MySQL database connections. Script one 
uses normal mysql_query calls, script two uses 
mysql_unbuffered_query calls.
Also, script one is doing additional mysql_query calls while 
retrieving the records of the first mysql_query (yes, an sql 
join would be better).

When script two (the one with mysql_unbuffered_query) 
finishes, and script one reuses the persistent database 
connection of script two, the resource identifier in script 
one gets lost after the first loop, resulting in only one 
returned row.
get_resource_type($res) should return 'mysql result', but will 
return 'Unknown'


Reproduce code:
---------------
Script1.html:
$db = mysql_pconnect ('host', 'user', 'pass');
print ('We are using MySQL thread: '. mysql_thread_id() .'<br />');

$res = mysql_query ("SELECT * FROM temp.documents");  // table with 1200 records

print ('mysql_num_rows() returned '. mysql_num_rows($res) .'<br />');

while ($row = mysql_fetch_assoc ($res)) {
  $res2 = mysql_query ("SELECT * FROM temp.attachments WHERE document = ". $row['id']);
  while ($row2 = mysql_fetch_assoc ($res2))
    $row['attachments'][] = $row2;

  $documents[] = $row;
}

print ('number of documents: '. count ($documents));


Script2.html:
$db = mysql_pconnect ('host', 'user', 'pass');
print ('We are using MySQL thread: '. mysql_thread_id() .'<br />');
$res = mysql_unbuffered_query ("SELECT * FROM temp.documents");
while ($row = mysql_fetch_assoc ($res)) {
}

Expected result:
----------------
Open script2.html and note the thread id.
Keep opening script1.html until the thread id is the same as 
the one script2 used.
You should see this:
(script2.html)
We are using MySQL thread: 1439

(script1.html)
We are using MySQL thread: 1439
mysql_num_rows() returned 1200
number of documents: 1200

Actual result:
--------------
However, the output will be:
(script2.html)
We are using MySQL thread: 1439

(script1.html)
We are using MySQL thread: 1439
mysql_num_rows() returned 1200
number of documents: 1

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-01-25 10:57 UTC] sniper@php.net
Assigned to the MySQL support.
 [2006-12-27 23:43 UTC] andrey@php.net
Do you still experience this behavior?
 [2007-01-04 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2010-08-18 12:40 UTC] andrey@php.net
-Assigned To: andrey +Assigned To: mysql
 [2010-11-02 17:31 UTC] felipe@php.net
-Status: No Feedback +Status: Feedback
 [2010-11-02 17:31 UTC] felipe@php.net
Please try using this snapshot:

  http://snaps.php.net/php5.3-latest.tar.gz
 
For Windows:

  http://windows.php.net/snapshots/


 [2011-11-04 10:04 UTC] uw@php.net
-Status: Feedback +Status: No Feedback
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Sat Dec 04 14:03:34 2021 UTC