lordg lordg - 6 months ago 8
SQL Question

MySql PDO and exceptions thrown within 2nd level internal procedure calls

We have a situation with the following...

DROP PROCEDURE IF EXISTS unit_throw_error;
CREATE PROCEDURE unit_throw_error() CALL throw_error();

DROP PROCEDURE IF EXISTS throw_error;
CREATE PROCEDURE throw_error() CALL _proc_does_not_exist();


If you call the first procedure
unit_throw_error
, the php PDO object will not throw an exception as created through the 2nd procedure. Some sample code:

$dsn = "mysql:host=localhost;dbname=test";
$username = "...";
$password = "...";

$pdo = new PDO($dsn, $username, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$query = "CALL unit_throw_error();";
$stmt = $pdo->prepare($query);
$stmt->execute();

do {
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} while ($stmt->nextRowset() && $stmt->columnCount());

$stmt->closeCursor();


The expected result is a thrown
PDOException
but this doesn't happen. Any ideas?

EDIT

We've isolated this to only happen when a successful select occurs before the line that throws an exception...

-- CREATE DATABASE `exception_test` /*!40100 DEFAULT CHARACTER SET utf8 */;

use exception_test;

DROP TABLE if exists `test_data`;
CREATE TABLE `test_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`some_field` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `test_data` (`some_field`) VALUES ('sdf');
INSERT INTO `test_data` (`some_field`) VALUES ('fgh');
INSERT INTO `test_data` (`some_field`) VALUES ('ghj');

drop procedure if exists `unit_throw_error`;
create procedure unit_throw_error() call unit_throw_error_2();

drop procedure if exists `unit_throw_error_2`;
delimiter //
create procedure unit_throw_error_2()
begin

select * from test_data;

call unknown_procedure();

end//
delimiter ;


Does anyone know why when a select query causes the exception to not be thrown?

Answer

When dealing with stored procedures, you need to call next_result() method, in order to make PDO aware of the other results returned by the procedure.

So, the code should be

$stmt = $pdo->prepare("CALL unit_throw_error()");
$stmt->execute();
do {
    $data = $stmt->fetchAll();
    var_dump($data);
} while ($stmt->nextRowset() && $stmt->columnCount());

In this case PDO will fetch the next result from a database, and if this result is an error, then exception will be thrown.

Edit:

It seems that for this particular case of syntax error (i.e. one that occurs even before the execution), you don't actually need to call nextRowset(). Instead, just make sure that PDO is set into ERRMODE_EXCEPTION.

But nevertheless, nextRowset() have to be called with stored procedures anyway, in order to let other queries to be run and in order to get the error if several resultsets are expected.

Comments