Thursday, May 6, 2010

PDO bug: exception caught but reappears in foreach() loop

I was experimenting with PDO/MySQL trying to see how a PHP code will behave with a single static DB connection, multiple connections but declared as "persistent", unbuffered MySQL queries and so on - and discovered a bug which I'd like to show here, because it took me a while to find any reference to it.
I tested it with PHP 5.2.13 under both UNIX and Windows.
Below is the code that demonstrates the problem, and two solutions/workarounds:

#
# PDO foreach exception bug
# Demonstration code
# Author: http://www.tiv.net
# 2010-05-06
#
# Note: same results will appear if using "while fetch()"
# instead of "foreach()" loop
#

print 'This code works OK (Exception is cleaned artificially)
';
$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() !== '00000' ) {
        $conn->query( "select 'CLEAN_PDO_ERROR'" );
    }
print 'NO exception will be thrown.
';
}

print 'This code works OK (two separate connections)
';
$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 'NO exception will be thrown.
';
}


print 'This code throws unexpected exception in foreach
';
$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 'Exception will be thrown after this...
';
}
?>