How to troubleshoot MySQL procedure - Why loop is reaching max runs?

Question

I have MySQL stored procedure that will create temporary table from remote server using FEDERATED tables. Then the procedure used these temporary table to do some work.

I am seeing some issue where I get an error like

Unable to connect to foreign data source
Got timeout reading communication packets
Unable to connect to foreign data source
Got error 1430 from storage engine

The problem here is that if these temporary table failed to create the entire procedure fails because the tables do not exists.

So I thought I can do some checking after the attempt to create temporary table and if the attempt return error then I go back and try it again. to stop an infinite loop I added a condition to quite after 10 tries.

The issue that i am running into is that the script quits and it does not run all the way to the end.

here is a portion of the procedure where the procedure is quitting and not sure why.

DELIMITER $$

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

DECLARE current_procedure_name CHAR(60) DEFAULT 'activities';
DECLARE last_run_time DATETIME DEFAULT NULL;
DECLARE current_run_time_start DATETIME DEFAULT NOW();

    -- set the SQL mode to ''
    SET SQL_MODE = '';

    -- set MySQL Safe mode OFF on update
    SET SQL_SAFE_UPDATES = 0;

SET @trys = 0;

loop_label:  LOOP

    SET  @trys :=  @trys+1;
    -- db.view_users is a federated table
    DROP TEMPORARY TABLE IF EXISTS view_users1, view_users2, view_users3;
    CREATE TEMPORARY TABLE view_users1 (KEY(user_id)) ENGINE=MEMORY AS 
    SELECT user_id, fullname
    FROM db.view_users;

    IF(@@error_count > 0 OR @trys > 10) THEN

        -- 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;

    CREATE TEMPORARY TABLE view_users2 (KEY(user_id)) ENGINE=MEMORY AS 
    SELECT * FROM view_users1;

    IF(@@error_count > 0) THEN
        ITERATE  loop_label;
    END IF;

    CREATE TEMPORARY TABLE view_users3 (KEY(user_id)) ENGINE=MEMORY AS 
    SELECT * FROM view_users1;

    IF(@@error_count > 0) THEN
        ITERATE  loop_label;
    END IF;

END LOOP;


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

How can I find out why it is quitting? it seem that @trys is reaching 11 and it quits but I don't understand why would it?

I have tried to run this code outside the procedure and the second line returns 0 error;

DROP TEMPORARY TABLE IF EXISTS view_users1, view_users2, view_users3;
CREATE TEMPORARY TABLE view_users1 (KEY(user_id)) ENGINE=MEMORY AS 
SELECT user_id, fullname
FROM db.view_users;
SELECT @@error_count;

A second question, is there a better approach for this problem? it is important that this procedure runs all the way.

Answer 1

I finally figure out the cause of the issue.

from the manual 13.6.5.5 LOOP Syntax

The statements within the loop are repeated until the loop is terminated. Usually, this is accomplished with a LEAVE statement. Within a stored function, RETURN can also be used, which exits the function entirely

at the end just before END LOOP; I needed to add LEAVE loop_label to end the LOOP other wise it will continue until @trys reaches 11;