Req #42322 [Opn->Csd]: pdo_mysql fetch() throws exception on INSERT statements

Edit report at https://bugs.php.net/bug.php?id=42322&edit=1 ID: 42322 Updated by: nikic@php.net Reported by: norbert at linuxnetworks dot de Summary: pdo_mysql fetch() throws exception on INSERT statements -Status: Open +Status: Closed Type: Feature/Change Request Package: PDO MySQL Operating System: Linux Debian Testing PHP Version: 5.2.3 -Assigned To: +Assigned To: nikic Block user comment: N Private report: N New Comment: This has been resolved by https://github.com/php/php-src/commit/a83cc03c138b8cf27a840bd7cd913eb7050e55ba. Calling fetch on upsert is no longer considered an error. Previous Comments: ------------------------------------------------------------------------ [2011-08-22 14:03:52] 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 ------------------------------------------------------------------------ [2011-08-22 14:00:07] 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 ------------------------------------------------------------------------ [2008-01-07 17:24:33] 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 ------------------------------------------------------------------------ [2008-01-07 12:39:54] 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 ------------------------------------------------------------------------ [2007-08-16 19:21:33] 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 ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=42322&edit=1