php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #63185 nextRowset() ignores MySQL errors with native prepared statements
Submitted: 2012-09-29 11:31 UTC Modified: 2020-12-08 09:41 UTC
From: dexen dot devries at gmail dot com Assigned:
Status: Closed Package: PDO MySQL
PHP Version: 5.4.7 OS: linux
Private report: No CVE-ID: None
 [2012-09-29 11:31 UTC] dexen dot devries at gmail dot com
Description:
------------
An SQL stored procedure will return several rowsets when it contains several SELECT statements. In MySQL CLI client (`mysql'), if any statement raises SQL error, the procedure is aborted, HOWEVER, all rowsets prior to the error  are displayed.


In PHP, PDO->nextRowset() switches to subsequent rowsets, or indicates no more rowsets with `FALSE'. However, it ignores SQL errors in second and later SQL statements.

For reproduction, the following SQL procedures refer to nonexistent table `no_such_table'.

For reference SQL program for MySQL CLI client (`mysql') -- which correctly indicates errors.
<<input>>:

DROP TABLE IF EXISTS test_table;
DROP TABLE IF EXISTS no_such_table;
DROP PROCEDURE IF EXISTS test_procedure_error_at_first;
DROP PROCEDURE IF EXISTS test_procedure_error_at_second;

CREATE TABLE test_table (aaa int PRIMARY KEY);
INSERT INTO test_table SELECT 1 UNION SELECT 2;

DELIMITER $$

CREATE PROCEDURE test_procedure_error_at_first ()
	BEGIN
		SELECT * FROM no_such_table; -- will raise error
		SELECT * FROM test_table;
	END;

CALL test_procedure_error_at_first $$

CREATE PROCEDURE test_procedure_error_at_second ()
	BEGIN
		SELECT * FROM test_table;
		SELECT * FROM no_such_table; -- will raise error
	END;

CALL test_procedure () $$

<<output for `test_procedure_error_at_first()'>>:
ERROR 1146 (42S02): Table 'no_such_table' doesn't exist

<<output for `test_procedure_error_at_second()'>>:
+-----+
| aaa |
+-----+
|   1 |
|   2 |
+-----+
2 rows in set (0.00 sec)

ERROR 1146 (42S02): Table 'no_such_table' doesn't exist


Test script:
---------------
<?php


$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('DROP TABLE IF EXISTS test_table');
$pdo->exec('DROP TABLE IF EXISTS no_such_table');
$pdo->exec('DROP PROCEDURE IF EXISTS test_procedure_error_at_first');
$pdo->exec('DROP PROCEDURE IF EXISTS test_procedure_error_at_second');

$pdo->exec('CREATE TABLE test_table (aaa int PRIMARY KEY)');
$pdo->exec('INSERT INTO test_table SELECT 1 UNION SELECT 2');

$pdo->exec('CREATE PROCEDURE test_procedure_error_at_first ()
	BEGIN
		SELECT * FROM no_such_table; -- will raise error
		SELECT * FROM test_table;
	END');
$pdo->exec('CREATE PROCEDURE test_procedure_error_at_second ()
	BEGIN
		SELECT * FROM test_table;
		SELECT * FROM no_such_table; -- this SHOULD raise error, but will be IGNORED
	END');

	# this would correctly indicate error raised by first SELECT * FROM no_such_table by raising an PDOException
#$pdo->query('CALL test_procedure_error_at_first()');


$st = $pdo->query('CALL test_procedure_error_at_second()');

	# this correctly fetches data from first SELECT * FROM test_table
var_dump($st->fetchAll());

	# this IGNORES error raised by second SELECT * FROM no_such_table
var_dump($st->nextRowset());
var_dump($st->fetchAll());

Expected result:
----------------
array(2) { [0]=> array(2) { ["aaa"]=> int(1) [0]=> int(1) } [1]=> array(2) { ["aaa"]=> int(2) [0]=> int(2) } }	# from var_dump($st->fetchAll());

PDOException: Base table or view not found: 1146 Table 'no_such_table' doesn't exist	# from $pdo->nextRowset()

Actual result:
--------------
array(2) { [0]=> array(2) { ["aaa"]=> int(1) [0]=> int(1) } [1]=> array(2) { ["aaa"]=> int(2) [0]=> int(2) } }	# from var_dump($st->fetchAll());
bool(false)	# from var_dump($st->nextRowset());
array(0) { }	# from var_dump($st->fetchAll());


Patches

0001-bugfix-check-errors-returned-from-mysqlnd_stmt_next_.patch (last revision 2012-10-25 10:56 UTC by dexen dot devries at gmail dot com)

Pull Requests

Pull requests:

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-09-29 11:34 UTC] dexen dot devries at gmali dot com
Was tested on PHP 5.4.7 (stock for Slackware 14.0), with mysqlnd driver for PDO (Client API version: mysqlnd 5.0.10 - 20111026 - $Id: b0b3b15c693b7f6aeb3aa66b646fee339f175e39)
 [2012-10-25 10:56 UTC] dexen dot devries at gmali dot com
To simplify OP: MySQL PDO with `mysqlnd' and with PDO::ATTR_EMULATE_PREPARES set to `false' triggers this bug -- errors returned along 2nd or later rowsets  are ignored. For example, when calling an SQL stored procedure which contains several SELECT statements, and thus returns multiple rowsets.

Relevant C fragment, file ext/pdo_mysql/mysql_statement.c:

static int pdo_mysql_stmt_next_rowset(pdo_stmt_t *stmt TSRMLS_DC) /* {{{ */
{
(...)
#if PDO_USE_MYSQLND
	if (!H->emulate_prepare) {
		if (!mysqlnd_stmt_more_results(S->stmt)) {
			PDO_DBG_RETURN(0);
		}
		if (mysqlnd_stmt_next_result(S->stmt)) {
			/*** here error goes unnoticed ***/
			PDO_DBG_RETURN(0);
		}

Trivial patch attached.
 [2013-11-22 14:48 UTC] dexen dot devries at gmail dot com
Another take on the bug, with a testcase: http://www.yiiframework.com/forum/index.php/topic/39970-transaction-and-multiple-queries/
 [2014-01-01 12:28 UTC] felipe@php.net
-Package: PDO related +Package: PDO MySQL
 [2020-12-08 09:21 UTC] nikic@php.net
-Summary: nextRowset() ignores MySQL errors +Summary: nextRowset() ignores MySQL errors with native prepared statements -Status: Open +Status: Verified
 [2020-12-08 09:21 UTC] nikic@php.net
I think for emulated prepares this was fixed in https://github.com/php/php-src/commit/0737be7e7baf1fece1683ca9f33064733d8b3514, but for native prepared statements I can reproduce this on current master.
 [2020-12-08 09:41 UTC] nikic@php.net
Looks like just reporting the error from next_result() doesn't cut, because the preceding more_results() call already returns false in this case.
 [2020-12-08 10:36 UTC] nikic@php.net
Automatic comment on behalf of nikita.ppv@gmail.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=bd093ad8615267ae4ff1a237e6285dc182a9ff57
Log: Fixed bug #63185
 [2020-12-08 10:36 UTC] nikic@php.net
-Status: Verified +Status: Closed
 [2021-02-26 22:17 UTC] Anonymous at Gmail dot com
The following pull request has been associated:

Patch Name: Ignore externally managed and generated files
On GitHub:  https://github.com/php/web-windows/pull/21
Patch:      https://github.com/php/web-windows/pull/21.patch
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Nov 05 08:01:28 2024 UTC