MySQL Stored Procedure not throwing error if it contains a query that succeeds

Question

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 ) 
----------

Answer 1

That's not possible. You can not prevent execution of statements based on if later statements will throw an error in a stored procedure.

The only way to achieve something similar would be something like this:

drop procedure if exists test_procedure_3;
DELIMITER $$
CREATE PROCEDURE `test_procedure_3`()
BEGIN
    if exists (select 1 from information_schema.routines where routine_name = 'raise_error' and routine_schema = database()) 
    then
    select 'gary';

    CALL raise_error;
    end if;
END$$
DELIMITER ;

call test_procedure_3;

So you just check, if the procedure exists beforehand. There's no other way (I tried with transactions, signal, prepared statements and what not).