Bug #63185 [Ver]: nextRowset() ignores MySQL errors with native prepared statements

Edit report at https://bugs.php.net/bug.php?id=63185&edit=1 ID: 63185 Updated by: nikic@php.net Reported by: dexen dot devries at gmail dot com Summary: nextRowset() ignores MySQL errors with native prepared statements Status: Verified Type: Bug Package: PDO MySQL Operating System: linux PHP Version: 5.4.7 Block user comment: N Private report: N New Comment: Looks like just reporting the error from next_result() doesn't cut, because the preceding more_results() call already returns false in this case. Previous Comments: ------------------------------------------------------------------------ [2020-12-08 09:21:28] 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. ------------------------------------------------------------------------ [2013-11-22 14:48:39] 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/ ------------------------------------------------------------------------ [2012-10-25 10:56:11] 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. ------------------------------------------------------------------------ [2012-09-29 11:34:05] 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-09-29 11:31:19] 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()); ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=63185&edit=1