Why does MySQL procedure stops executing upon running into an error? How to I allow it to continue?

Question

I have a stored procedure that does many queries. And what I am trying to do is log any error "if any occurred" into a new table I have created then move on to the next query.

Here is a portion of the procedure

DELIMITER $$

CREATE DEFINER=`root`@`10.%` PROCEDURE `prod_create_new_tasks`()
MAIN: 
BEGIN


    SET @trys = 0;

    loop_label:  LOOP

        SET  @trys :=  @trys+1, @p1 = '', @p2 = '';

        DROP TEMPORARY TABLE IF EXISTS su;
        CREATE TEMPORARY TABLE su (KEY(user_id)) ENGINE=MEMORY AS 
        SELECT user_id, fullname, current_team_id, status
        FROM mydb.view_users;

        SET @total_errors = @@error_count;

        GET DIAGNOSTICS CONDITION 1
        @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;


        IF(@total_errors > 0 OR @trys > 10) THEN
                -- log error
                INSERT INTO mydb.error_logs(error_no, error_text, procedure_name, stage)
                                        VALUES(@p1, @p2, current_procedure_name, 'loop_label' );

            -- if there are too many tries to create the temporary table and it keeps failing Quit!!
            IF( @trys > 10) THEN
                 LEAVE MAIN;
            ELSE 
                -- sleep for 10 seconds and go back to create the table again
                SELECT SLEEP(10);
                ITERATE  loop_label;
            END IF;

        END IF;
        LEAVE loop_label;
    END LOOP;

    -- set MySQL Safe mode ON on update
    SET SQL_SAFE_UPDATES = 1;
END

Following the logic there the code should try to create a temporary table. If there was any error while trying to create the temporary table GET DIAGNOSTICS should capture the error and store it into error_logs table. After 10 seconds, another attempt to create the table should take.

What seems to be happening is as soon the first attempt to create the table fails it stops and write the error to the screen. no error are logged into error_logs table and no other attempt takes place.

It seems that there is a variables that need to be changed for the season to allow the procedure to continue even if it encounter an error.

My question is How can I log the error "if any" into the table and allow the loop to jump to the next run without stopping?

Note, I tried to change the view_users table to something that does not exists on purpose so I can get the error "table does not exists" logged into the table but that did not work.

Answer 1

I finally figured out the solution. I would like to post the answer to help out anyone that is running into the same issue.

The solution is to DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

Continue Handler will allow the code to run even if there was an error. from there I set the sqlstate and errorno then I store them and move on.

Here is my final code.

DELIMITER $$

CREATE DEFINER=`root`@`10.%` PROCEDURE `prod_create_new_tasksddd`()
MAIN:
BEGIN

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
        @err_sqlstate = RETURNED_SQLSTATE,
        @err_message = MESSAGE_TEXT,
        @err_num = MYSQL_ERRNO;

        SET @hasError = TRUE;
    END;

    SET @current_procedure_name = 'taskddd';
    SET @trys = 0;
    SET @hasError  = FALSE;

    loop_label: LOOP

        SET @trys := @trys+1;


        DROP TEMPORARY TABLE IF EXISTS su;
        SET @hasError = FALSE;

        -- trigger error 1146
        CREATE TEMPORARY TABLE su LIKE t2;


        IF(@hasError OR @trys > 10) THEN

            IF @trys > 10 THEN 
                SET @msg = 'Gave Up!';
            ELSE 
                SET @msg = CONCAT('Trying Again ', @trys);
            END IF;

            INSERT INTO mydb.error_logs(error_no, error_sqlstate, error_text, procedure_name, stage, trys, current_actions) VALUES(@err_num, @err_sqlstate, @err_message, @current_procedure_name, 'loop_label', @trys , @msg);

            -- if there are too many tries to create the temporary table and it keeps failing Quit!!
            IF( @trys > 10) THEN
                LEAVE MAIN;
            ELSE
                -- sleep for 2 seconds and go back to create the table again
               SET @err_sqlstate = '', @err_message = '', @err_num = '', @msg = NULL;
               SET @hasError  = FALSE;
                SELECT SLEEP(2);
                ITERATE loop_label;
            END IF;

        END IF;

        LEAVE loop_label;
    END LOOP;

END