We're building a production PHP-MySQL application, and want MySQL stored procedures to be the central bullet-proof gateway to the database. Duplicate keys, table not found, server instance going down, etc all and any kind of error needs to be trapped and conveyed to the calling PHP web-based UI, and transaction rolled back in the stored proc upon such errors.
I am using PHP mysqli and calling a stored procedure as follows:
$stmt = mysqli_prepare($db, "call my_stored_proc(?, ?, ?, @ptid)");
if ($stmt && mysqli_stmt_bind_param($stmt, "sss", 'p1', 'p2', 'p3') &&
mysqli_stmt_execute($stmt) && mysqli_stmt_close($stmt)) {
echo "All fine!"
} else {
echo mysqli_error($db);
db_disconnect($db);
exit;
}
The stored procedure does some basic validation and signals a user-defined condition if the validation fails. And sure enough, my PHP code is able to catch and see those non-database (eg. formatting) validation errors arising from the stored procedure. After the non-database validations pass, the stored procedure goes on to do a database-related validation and if those pass it inserts a row in a table, and passes the ID in the last OUT parameter.
My problem is that if this insert fails (say, bcoz duplicate key error, or table not found error), my PHP code is simply not catching the error! It prints "All fine"!
Why is that? What am I missing?
I want my invocation of the stored proc to be bullet-proof, and all errors raised by the stored proc should be trappable in PHP.
FYI: If I call the stored proc from a mysql client (like MySQL Workbench or the mysql client on Linux), the errors are correctly reported.
LATER EDITS: FYI, the stored procedure code is simply:
delimiter $$
drop procedure if exists my_stored_proc $$
create procedure my_stored_proc
(
in p_name VARCHAR(31),
in p_notes VARCHAR(510),
in p_created_by VARCHAR(31),
out p_pt_id INT
)
begin
declare custom_exception condition for sqlstate '45000';
declare l_retval boolean;
declare l_right_now datetime default now();
select p_name regexp '^[[:space:]]*$' into l_retval;
if l_retval then
signal custom_exception set message_text = 'NAME cannot be blank.';
end if;
select p_name regexp '[^0-9_]' into l_retval;
if l_retval then
signal custom_exception set message_text = 'Invalid NAME.';
end if;
call validate_user_in_db(p_created_by, true, l_retval);
if not l_retval then
signal custom_exception set message_text = 'Invalid CREATED_BY user.';
end if;
insert into some_table
(
NAME, NOTES,
CREATED_BY, CREATED_ON
) values
(
p_name, p_notes,
p_created_by, l_right_now
);
set p_pt_id = last_insert_id();
end $$
delimiter ;
EVEN LATER UPDATE:
The weird thing is, if I comment out the call to validate_user_in_db in the above stored proc, things work fine and errors are correctly trapped (eg. duplicate key, etc) in PHP.
FYI: validate_user_in_db does the following:
create procedure validate_user_in_db (in p_user VARCHAR(127),
in p_active_only boolean, out p_retval boolean)
begin
set p_retval = false;
if p_active_only then
select sql_calc_found_rows 'x'
from SOME_USERS_TABLE
where username = p_user
and active = true
limit 1;
else
select sql_calc_found_rows 'x'
from SOME_USERS_TABLE
where username = p_user
limit 1;
end if;
set @l_num_rows = found_rows() ;
if @l_num_rows = 1 then
set p_retval = true;
end if;
end $$
Sorry for the long post. But I thought I'd give the full picture.
What am I missing? Why is my PHP code not getting back errors if the call to validate_user_in_db is enabled? Is validate_user_in_db changing some state permanently? Is the sql_calc_found_rows keyword messing things up?
FYI: This is PHP 7.3 and MySQL 5.6
