2

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:

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 ) 
----------
  • I think it ought to be returning two result sets - the first would be the 'gary' rs, and a second one with the error. Have you tried using mysqli, just to see if you get a different result? Also, how are you calling this from PHP /PDO? – dbdemon Apr 11 '18 at 7:34
  • @dbdemon I have added some examples of both mysqli and PDO methods of calling those procedures from PHP, as well as the output I get from those examples. Thanks for your help! – Gary Reckard Apr 11 '18 at 14:15
  • You seem to have swithed the order of selecting 'gary' and calling raise_error, but to me it looks like the mysqli output looks correct? Whereas the pdo output is still wrong. – dbdemon Apr 11 '18 at 15:03
  • @dbdemon In both examples (mysqli and PDO), I am calling both procedures. One selects and then raises error, the other raises error and then selects. In both situations, both mysqli and pdo are NOT showing me the error in the test_procedure_1. I did notice an issue with my example code, where I'm not calling next_result() to get more result sets, but even after I modify it, I'm still getting no error when I would expect one. – Gary Reckard Apr 11 '18 at 15:17
  • You don't seem to be calling the procedures, but only creating them. – mustaccio Apr 11 '18 at 16:49
0

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

  • Hi there @tombom. I'm not trying to prevent the execution of the "select 'gary'" statement. I'm just saying that even in my procedure has the "select 'gary'" in it, if it later contains a statement that should throw an error.... the procedure should report that error in some way. – Gary Reckard Feb 6 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.