php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Request #42322 pdo_mysql fetch() throws exception on INSERT statements
Submitted: 2007-08-16 19:21 UTC Modified: 2020-12-08 09:10 UTC
Votes:9
Avg. Score:4.3 ± 0.8
Reproduced:9 of 9 (100.0%)
Same Version:3 (33.3%)
Same OS:5 (55.6%)
From: norbert at linuxnetworks dot de Assigned: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: 5.2.3 OS: Linux Debian Testing
Private report: No CVE-ID: None
View Developer Edit
 [2007-08-16 19:21 UTC] norbert at linuxnetworks dot de 
Description:
------------
If using the MySQL PDO driver in PDO::ERRMODE_EXCEPTION mode, calling fetch() after executing an INSERT/UPDATE/DELETE statement throws an exception. Instead, it should return FALSE in this case like for an empty result set. Please compare to the SQLite PDO driver which works correctly.

Reproduce code:
---------------
<?php

$db = new PDO( $dsn, $username, $password );
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

try
{
    $db->exec("CREATE TABLE IF NOT EXISTS TestTable (id INT)");

    $sql = 'INSERT INTO TestTable VALUES ( NULL )';
    $query = $db->prepare( $sql );
    $query->execute();

    while( ( $row = $query->fetch( PDO::FETCH_ASSOC ) ) !== FALSE ) {}
    $query->closeCursor();
}
catch( PDOException $pe ) {
    echo 'Got PDOException: ' . $pe->getMessage();
}

?>

Expected result:
----------------
$query->fetch() should return FALSE, as it is not an error to call fetch() for an INSERT statement, because it might be unknown what type of statement will be executed (like in an SQL shell where users can enter their custom queries).

Actual result:
--------------
PDOException

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-01-07 12:39 UTC] uw@php.net 
Changing category to PDO.

I don't think this is a bug.

Its hard to say what is "correct" when it comes to PDO. PDO shows many inconsistencies when comparing different drivers. With MySQL there is no result set to fetch after executing an INSERT statement.

Fetch is one place where the "PDO specification" states explicitly that drivers differ in their behaviour:
"The results of this fetch are driver dependent and the data is usually stored in the driver_data member of the pdo_stmt_t object. The ori and offset parameters are only meaningful if the statement represents a scrollable cursor. This function returns 1 for success or 0 in the event of failure."
http://www.php.net/manual/en/internals2.pdo.implementing.php

As no standard behaviour has been defined, how can either MySQL or any other driver be wrong? I guess this issue should be called a Feature request but not a bug.

SQLite     -> no exception

MySQL      -> exception
Postgres   -> exception

Ulf
 [2008-01-07 17:24 UTC] uw@php.net 
How DB2 Express and Oracle Express deal with the example code...

DB2 Express - 
Warning: PDOStatement::fetch(): SQLSTATE[24000]: Invalid cursor state: -99999 [IBM][CLI Driver] CLI0115E  Invalid cursor state. SQLSTATE=24000 (SQLFetchScroll[4294867297] at /home/nixnutz/php5/ext/pdo_ibm/ibm_statement.c:867) in /home/nixnutz/php5/ext/pdo_ibm/tests/bug_42322.php on line 19

Oracle Express - 
Warning: PDOStatement::fetch(): SQLSTATE[HY000]: General error: 24374 OCIStmtFetch: ORA-24374: Definition nicht erfolgt vor Abruf oder Ausf?hren und Abruf
 (/home/nixnutz/php5/ext/pdo_oci/oci_statement.c:467) in /home/nixnutz/php5/ext/pdo_oci/tests/bug_42322.php
 [2011-01-01 23:53 UTC] jani@php.net
-Package: Feature/Change Request +Package: PDO related
 [2011-08-22 14:00 UTC] rosen at developer dot bg 
I think that the issue here might be that PHP misinterprets what the mysql client library returns. See this: http://bugs.mysql.com/bug.php?id=706 - as far as I understand, the C function mysql_errno() is not supposed to return errors relating to a fetch operation - which means that if mysql_query failed and then mysql_fetch_row fails due to being at the end of the result set, mysql_errno will still return the error from mysql_query. This would trick PDO into thinking that it was the mysql_fetch_row that failed, thowing a PDOException that refers to a completely different error (the one previously caused by mysql_query).

Example code:
-------------
<?php

$dbh = new PDO('mysql:host=localhost', 'username', 'password');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$res = $dbh->query('SELECT 1'); // result has only one row

foreach ($res as $row) {
	// perform another query that fails with an error, but catch the exception
	try {
		$dbh->query('blah blah');
	} catch (PDOException $e) {
		echo "Exception caught.\n";
	}
}


Expected output:
----------------
Exception caught.


Actual output:
--------------
Exception caught.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'blah blah' at line 1' in ~/bug.php:9
Stack trace:
#0 ~/bug.php(9): unknown()
#1 {main}
  thrown in ~/bug.php on line 9
 [2011-08-22 14:03 UTC] rosen at developer dot bg 
The above test was performed on PHP 5.3.6 (cli)

uname -a: Linux gateway 2.6.9-78.0.22.ELsmp #1 SMP Thu Apr 30 19:17:40 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
 [2017-04-24 19:09 UTC] adambaratz@php.net
-Package: PDO related +Package: PDO MySQL
 [2020-12-08 09:10 UTC] nikic@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: nikic
 [2020-12-08 09:10 UTC] nikic@php.net 
This has been resolved by https://github.com/php/php-src/commit/a83cc03c138b8cf27a840bd7cd913eb7050e55ba. Calling fetch on upsert is no longer considered an error.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Mon Dec 30 17:01:29 2024 UTC