php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #44454 Unexpected exception thrown in foreach() statement
Submitted: 2008-03-17 14:11 UTC Modified: 2013-02-18 00:33 UTC
Votes:7
Avg. Score:4.9 ± 0.3
Reproduced:6 of 6 (100.0%)
Same Version:4 (66.7%)
Same OS:4 (66.7%)
From: mfischer@php.net Assigned:
Status: No Feedback Package: PDO related
PHP Version: 5.*, 6CVS (2009-04-25) OS: *
Private report: No CVE-ID: None
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
21 + 28 = ?
Subscribe to this entry?

 
 [2008-03-17 14:11 UTC] mfischer@php.net
Description:
------------
I'm using the SQL SELECT result of an PDO query directly in an foreach() loop (via it's iterator capability).

Within this loop I'm doing INSERTs and they violate integrity constraints and thus PDO throws an exception.

I'm catching and basically suppressing the exception. However when the foreach() loop is finished, an exception gets thrown with the line where the foreach() statement is declared.

It seems internally the thrown but silently ignored exception has queued and suddenly being let loose when the foreach() loop finishes.

It doesn't matter whether the SELECT-statement fetches the data from the table where it is inserted or not, this is just done to make the example simpler.

I've also tested with php5.3-win32-200803170030 and it's the same behavior.

It seems to be an similar issue like #39345, but in my case I'm using the mysql driver and the exception is within my PHP source.

Reproduce code:
---------------
<?php
    $oPdo = new PDO('mysql:host=db01;dbname=test', 'test', 'test');
    $oPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $oPdo->query('DROP TABLE IF EXISTS test');
    $sSqlCreate = <<<SQL
CREATE TABLE `test` (
  `a` bigint(20) unsigned NOT NULL,
  `b` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `unique_ref` (`a`,`b`)
) ENGINE=MyISAM;
SQL;

    $oPdo->query($sSqlCreate);
    $oPdo->query('TRUNCATE test');
    $oPdo->query('INSERT INTO test(a, b) VALUES(1, 1)');

    $oResult = $oPdo->query('SELECT * FROM test');
    foreach ($oResult as $aRow) {
        try {
            $oPdo->query('INSERT INTO test(a, b) VALUES(1, 1)');
        } catch (Exception $e) { }
    }
?>


Expected result:
----------------
Don't throw an exception:

$ php reproduce_pdo_bug.php
# 0
# 1
$

Actual result:
--------------
$ php reproduce_pdo_bug.php
# 0
# 1

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-1' for key 1 in reproduce_pdo_bug.php on line 20

Call Stack:
    0.0002      58460   1. {main}() reproduce_pdo_bug.php:0


Patches

fix-mysql_statement.c-5.3-201101041530.patch (last revision 2011-01-04 16:20 UTC by rgagnon24 at gmail dot com)
fix-mysql_statement.c-5.2.13.patch (last revision 2010-06-03 18:51 UTC by rgagnon24 at gmail dot com)

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-07-03 16:37 UTC] uw@php.net
No comment on the cause just some observations. Issue exists with SQLite as well. 

PHP 5.3 CVS

MySQL results:

 emulated prepared statements - not using traversable - OK
 emulated prepared statement  -     using traversable - Issue exists
 
 native prepared statements   - not using traversable - OK
 native prepared statements   -     using traversable - OK

SQLite:

 emulated prepared statements - not using traversable - Issue exists
 emulated prepared statement  -     using traversable - Issue exists



"not using traversable":

$stmt = $db->query('SELECT a, b FROM test');
while ($row = $stmt->fetch()) {
  try {
    $db->exec('INSERT INTO test(a, b) VALUES (1, 1)');
  } catch (Exception $e) {
    printf("while loop\n");
  }
}


"using traversable":

$stmt = $db->query('SELECT a, b FROM test');
foreach ($stmt as $row) {
  try {
   $db->exec('INSERT INTO test(a, b) VALUES (1, 1)');
  } catch (Exception $e) {
    printf("foreach\n");
  }
}  
 [2008-07-03 16:44 UTC] uw@php.net
Correction:

 emulated prepared statements - not using traversable - Issue exists
 emulated prepared statement  -     using traversable - Issue exists

 [2009-04-25 14:57 UTC] jani@php.net
Does this still exist? If so, update the version properly.. ;)
 [2009-04-25 17:16 UTC] mfischer@php.net
Using Windows snapshots, verified that the bug still exists in 5.2.10-dev and 5.3.0RC2-dev . Cannot test with 6.0.0-dev, the php.exe doesn't even properly start.
 [2010-05-07 02:13 UTC] gregory at tiv dot net
I have a simpler test case, one solution/explanation and one workaround.
Tested under:
Windows - PHP 5.2.13 (cli) (built: Feb 24 2010 14:32:32)
FreeBSD - PHP 5.2.12 with Suhosin-Patch 0.9.7 (cli) (built: Feb 24 2010 23:12:45)

Demonstration code:
-------------------

<?php
#
# PDO foreach exception bug
# Demonstration code
# Author: Gregory Karpinsky, http://www.tiv.net
# 2010-05-06

print '<p>This code works OK (Exception is cleaned artificially)</p>';
$conn = new PDO( 'mysql:host=localhost', 'test', 'test' );
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$oRS = $conn->query( "select 'OK'" );
foreach ( $oRS as $row ) {
	try {
		$conn->query( 'Bogus SQL' );
	} catch (PDOException $e) {}
	if ( $conn->errorCode() ) {
		$conn->query( "select 'CLEAN_PDO_ERROR'" );
	}
print '<p>NO exception will be thrown.</p>';
}

print '<p>This code works OK (two separate connections)</p>';
$conn = new PDO( 'mysql:host=localhost', 'test', 'test' );
$conn2 = new PDO( 'mysql:host=localhost', 'test', 'test' );
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$oRS = $conn->query( "select 'OK'" );
foreach ( $oRS as $row ) {
	try {
		$conn2->query( 'Bogus SQL' );
	} catch (PDOException $e) {}
print '<p>NO exception will be thrown.</p>';
}


print '<p>This code throws unexpected exception in foreach</p>';
$conn = new PDO( 'mysql:host=localhost', 'test', 'test' );
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$oRS = $conn->query( "select 'OK'" );
foreach ( $oRS as $row ) {
	try {
		$conn->query( 'Bogus SQL' );
	} catch (PDOException $e) {}
print '<p>Exception will be thrown after this...</p>';
}
?>
 [2010-05-08 06:08 UTC] gregory at tiv dot net
Correction:
-----------
if ( $conn->errorCode() ) {

should be

if ( $conn->errorCode() !== '00000' ) {
 [2010-06-03 20:03 UTC] rgagnon24 at gmail dot com
Looking at pdo_mysql extension source code, I see the exception is actually being raised twice, not being buffered.

from php5.2.13 source, file ext/pdo_mysql/mysql_statement.c is calling _pdo_mysql_error() in two places.  Once from line 218 in pdo_mysql_stmt_execute() after mysql_real_query() fails, and then again at line 425 in pdo_mysql_stmt_fetch() because mysql_errno() indicates there is an error.
 [2010-06-03 20:36 UTC] rgagnon24 at gmail dot com
From: http://dev.mysql.com/doc/refman/5.0/en/mysql-errno.html

   "Note that some functions like mysql_fetch_row() don't set mysql_errno() if they succeed."

And: http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-row.html

   "Note that error is not reset between calls to mysql_fetch_row()"
-----------------------------------------------------------------------------
Since all the SELECT'd rows are fetched ok, the error from the botched insert is still hanging around for mysql_errno() to find, and raise the exact same exception after the data is finished being iterated.
 [2010-06-03 20:58 UTC] rgagnon24 at gmail dot com
I've attached a patch "fix-mysql_statement.c-5.2.13.patch" to resolve this problem.

From: http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-row.html

   "When used after mysql_store_result(), mysql_fetch_row() returns NULL when there are no more rows to retrieve. When used after mysql_use_result(), mysql_fetch_row() returns NULL when there are no more rows to retrieve or if an error occurred"

The patch simply does not raise an exception during a NULL result from mysql_fetch_row() since it only indicates the exhaustion of data.

The condition added simply matches the use of either mysql_use_result() or mysql_store_result() called in pdo_mysql_stmt_execute().  When not buffered, mysql_use_result() is called.  Therefore, the same check is performed after the fetch before deciding to raise an exception.

Also, when un-buffered queries are used, the test script above would be invalid as you cannot perform another operation on a result-set when not all of the results have been retrieved.

The patch was created against the released PHP 5.2.3 source code tarball.  It's so small, you should be able to modify it easily for any version of the mysql_statement.c file.
 [2010-06-20 19:08 UTC] felipe@php.net
-Status: Verified +Status: Assigned -Assigned To: +Assigned To: mysql
 [2010-08-18 12:51 UTC] johannes@php.net
-Status: Assigned +Status: Feedback
 [2010-08-18 12:51 UTC] johannes@php.net
Please try a 5.3 snapshot.
 [2011-01-04 13:37 UTC] uw@php.net
-Assigned To: mysql +Assigned To:
 [2011-01-04 13:37 UTC] uw@php.net
Not a MySQL specific issue thus it should not be assigned to mysql, see my earlier comment:

[2008-07-03 14:37 UTC] uw@php.net
No comment on the cause just some observations. Issue exists with SQLite as well. 
[...]
 [2011-01-04 17:22 UTC] rgagnon24 at gmail dot com
Problem still exists in PHP 5.3 snapshot 201101041530

Patch uploaded to account for the changes in mysql_statement.c since the last time I patched PHP 5.2
 [2013-02-18 00:33 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC