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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
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

Pull Requests

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-2025 The PHP Group
All rights reserved.
Last updated: Wed Jan 08 05:01:28 2025 UTC