php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #43586 Unable to query again after foreach on MySQL
Submitted: 2007-12-13 07:08 UTC Modified: 2008-01-20 17:10 UTC
Votes:2
Avg. Score:3.0 ± 0.0
Reproduced:1 of 2 (50.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: vicrry at yahoo dot com dot hk Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.2.5 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: vicrry at yahoo dot com dot hk
New email:
PHP Version: OS:

 

 [2007-12-13 07:08 UTC] vicrry at yahoo dot com dot hk
Description:
------------
It seems the cursor didn't close after iterated through with foreach statement, but I don't insist any other causes.

I don't know if it is expected, but it really cause inconvenience and reduce readability to add a fetchAll() line after every query.

Reproduce code:
---------------
$Pdo = new PDO('mysql:host=localhost,dbname=test');

$res=$Pdo->query('SELECT * FROM TEST_TABLE');
foreach($res as $row) print_r($row);

$res=$Pdo->query('SELECT * FROM TEST_TABLE2');
foreach($res as $row) print_r($row);

Expected result:
----------------
Prints out each row in table 'TEST_TABLE' and 'TEST_TABLE2'.

Actual result:
--------------
The first query seems works fine, though it might have error info left unprinted.

The second query doesn't work at all, and the query() statement didn't even report an error.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-12-18 15:17 UTC] msaraujo@php.net
Hello,

Can you please send me your SQL definition for TEST_TABLE and TEST_TABLE2?


 [2007-12-21 12:52 UTC] vicrry at yahoo dot com dot hk
I believe the following definition will surely cause what I mentioned.

-- ------------------------------------ --
TEST_TABLE ( id int(12) PRIMARY KEY );
TEST_TABLE2 ( id int(12) PRIMARY KEY );
-- ------------------------------------ --

In fact it occurs on every foreach I used on any table in my database.
 [2008-01-04 06:49 UTC] vicrry at yahoo dot com dot hk
I found a simple solve for this error.

Unset the PDOStatement object each time before your query again with the same variable.

Reproduce code:
---------------
$Pdo = new PDO('mysql:host=localhost,dbname=test');

$res=$Pdo->query('SELECT * FROM TEST_TABLE');
foreach($res as $row) print_r($row);

unset($res);

$res=$Pdo->query('SELECT * FROM TEST_TABLE2');
foreach($res as $row) print_r($row);
---------------

But this seems to be a bug anyway, it DO causes inconveniences.
 [2008-01-04 07:08 UTC] vicrry at yahoo dot com dot hk
Another potential cause for this bug is the MySql specific attribute 
"PDO::MYSQL_ATTR_USE_BUFFERED_QUERY".

I found it totally solved when I create my PDO object with this attribute set to true.

The shared buffer in the PDOStatement object is probably not released after foreach statements.

Not sure memory leaks occurs or not.
 [2008-01-20 17:10 UTC] msaraujo@php.net
This is not a bug.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Jul 16 20:01:29 2024 UTC