How to detect a rollback in MySQL stored procedure?
Solution 1
You can add an output param and then set it to the value you want in your exit handlers.
Here's an example using your proc:
delimiter $$
create procedure multi_inserts(
IN var1 int(11),
.
.
.
IN string1 text,
OUT p_return_code tinyint unsigned
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
insert into table1(a,b,c,d) values(var1,var2,var3,var4);
insert into table2(e,f,g) values(var5,var6,string1);
COMMIT;
-- SUCCESS
set p_return_code = 0;
END $$
delimiter ;
Solution 2
You would usually do this PHP-side if you wanted to catch errors. Read http://php.net/manual/en/pdo.transactions.php for more information.
Comments
-
Kou almost 2 years
I'm trying to figure out a way to detect an occurrence of rollback in a MySQL stored procedure so I could handle the situation accordingly from a PHP script, but so far I can not find any solution.
My stored procedure looks like this:
delimiter | create procedure multi_inserts( IN var1 int(11), . . . IN string1 text ) BEGIN declare exit handler for sqlexception rollback; declare exit handler for sqlwarning rollback; START TRANSACTION; insert into table1(a,b,c,d) values(var1,var2,var3,var4); insert into table2(e,f,g) values(var5,var6,string1); COMMIT; END delimiter ;
I did a rollback test on this procedure and it did rollback but I got no false. I want my stored procedure to throw some kind of error message if the transaction failed, so I could handle it like this:
$result = mysql_query($procedure); if(!$result) { //rollback occured do something }
Is there a way to detect rollback in MySQL? Am I missing something? Any reply will be appreciated. Thanks for reading.
Thanks to your advices I fixed this problem. Here's what I did:
Stored Procedure
delimiter | create procedure multi_inserts( IN var1 int(11), . . . IN string1 text ) BEGIN declare exit handler for sqlexception sqlwarning BEGIN rollback; select -1; END; START TRANSACTION; insert into table1(a,b,c,d) values(var1,var2,var3,var4); insert into table2(e,f,g) values(var5,var6,string1); COMMIT; END delimiter ;
If I use out variable instead of select -1, it gives me this error:
OUT or INOUT argument is not a variable or NEW pseudo-variable in BEFORE trigger
I don't know what did I wrong, but I couldn't fix this problem.
PHP script
$result=mysqli_query($con,$procedure); if(is_object($result)) { //rollback happened do something! }
If the SP is successful it throws true.
-
Kou over 13 yearsThanks for the reply. Maybe I should use PDO.
-
Kou over 13 yearsThanks for the detailed answer. I rewrote my SP and it worked fine in MySQL console but php's mysql_query couldn't handle the result set and gave me funny error, so I updated PHP to 5.3.3 for mysqli_query. I thought this would solve it. How naive of me. After the update I got another error in Pear Auth.php which I'm using as an authentication and I cant see if mysqli_query really would solve the problem unless I solve the new problem first. The latest problem is that I cant update pear auth for some reason. I guess pear auth is incompatible with 5.3.3. Now what should I do? sorry I'm ranting..
-
zohar about 9 yearsplease add description