I am writing a simple MySQL Stored Procedure, and calling it from PHP using PDO.
This is just simple enough to illustrate the problem I'm having.
This procedure test_procedure_2
, does not throw any error.
CREATE PROCEDURE `test_procedure_1`()
BEGIN
select 'gary';
CALL raise_error;
END
It returns the result set:
[0] => Array
(
[0] => Array
(
[gary] => gary
)
)
However...
CREATE PROCEDURE `test_procedure_2`()
BEGIN
CALL raise_error;
select 'gary';
END
...this procedure test_procedure_2
, when called, will actually throw an PDOException with the message "SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE vjs_admin_dev.raise_error does not exist"
. And the select 'gary'
query never gets executed. I like that.
Why is it that the first procedure did NOT throw an error when it clearly contained an error? I would like to come up with a stored procedure that, whenever there is an error anywhere in it, it will throw an error. Thanks for your help!
I'm using
- Ubuntu: 16.04, and
- PHP: 7.0.15
- mysql: Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper
EDIT: I have tried this with mysqli, and with PDO, and I get the same results. Here are some simple code examples that show my issue:
Mysqli Version: https://pastebin.com/rATMST4H
PDO version: https://pastebin.com/Wiq3yFmQ
The output of the Mysqli version is:
Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN select 'gary'; CALL raise_error;END
RESULTS:Array ( [gary] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN CALL raise_error; select 'gary';END
CALL failed: (1305) PROCEDURE vjs_admin_dev.raise_error does not exist
----------
The output of the PDO version is:
Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN select 'gary'; CALL raise_error;END
RESULTS:Array ( [gary] => gary [0] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN CALL raise_error; select 'gary';END
CALL failed: (42000) Array ( [0] => 42000 [1] => 1305 [2] => PROCEDURE vjs_admin_dev.raise_error does not exist )
----------