How to catch any exception in triggers and store procedures for mysql?

Question

I have been trying to catch mysql exception especially for triggers and store procedures.How can we catch the exception from mysql side?. I still not found any solution. your help would be appreciate.

Thanks Hitesh

Solution 1

Because this comes up in the top of my search for MySQL error handling in triggers, I thought I'd share my solution for MySQL 5.5+

My original post: https://stackoverflow.com/a/26115231/1733365 Duplicated below...

Because this article comes up towards the top when I search for error handling in MySQL triggers, I thought I'd share some knowledge.

If there is an error, you can force MySQL to use a SIGNAL, but if you don't specify it as a class as SQLEXCEPTION, then nothing will happen, as not all SQLSTATEs are considered bad, and even then you'd have to make sure to RESIGNAL if you have any nested BEGIN/END blocks.

Alternatively, and probably simpler still, within your trigger, declare an exit handler and resignal the exception.

CREATE TRIGGER `my_table_AINS` AFTER INSERT ON `my_table` FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        RESIGNAL;
    DECLARE EXIT HANDLER FOR SQLWARNING
        RESIGNAL;
    DECLARE EXIT HANDLER FOR NOT FOUND
        RESIGNAL; 
    -- Do the work of the trigger.
END

And if in your body there occurs an error, it will be thrown back up to the top and exit with an error. This can also be used in stored procedures and whatnot.

This works with anything version 5.5+.

Solution 2

Check out the syntax for DECLARE HANDLER

http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html

Also, if you're trying to debug a SP, this might be helpful for you:

http://www.bluegecko.net/mysql/debugging-stored-procedures/