MySQL CONTINUE, EXIT handlers not detecting network error codes and sqlstates.

Question

Using MySQL version: 5.7.20-0ubuntu0.16.04.1

Hi

I have written a small procedure:

CREATE PROCEDURE fed_insert (INOUT last_user_id varchar(30), OUT success boolean)

BEGIN

DECLARE v_user_id varchar(30) default null;
DECLARE v_count_stock_wl int default 0;
DECLARE is_online boolean default true;
DECLARE done boolean default false;

DECLARE crsr CURSOR FOR select user_id, count(distinct sc_id) from stock_watchlist where user_id > last_user_id group by user_id order by user_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION BEGIN
    select 'EXITING.' as msg;
    set success = false;
END;

OPEN crsr;

fetch_loop: LOOP

    IF done THEN
        LEAVE fetch_loop;
    END IF;

    select ROW_FORMAT='Dynamic' from information_schema.tables where table_name='stock_wl_t_fed' into is_online;

    IF is_online THEN
        fetch crsr into v_user_id, v_count_stock_wl;
        insert stock_wl_t_fed values (v_user_id, v_count_stock_wl);
        set last_user_id = v_user_id;
    ELSE
        select 'Federated table is offline.' as msg;
        do sleep(1);
    END IF;

END LOOP fetch_loop;

CLOSE crsr;

set success = true;

END$$

My problem lies with the handler:

DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION BEGIN
    select 'EXITING.' as msg;
    set success = false;
END;

This handler is not detecting certain error codes and sqlstates

1161: Got timeout reading communication packets
1296: Got error %d '%s' from %s
SQLSTATE 'HY000'
SQLSTATE '08S01'

In its current form the handler works sometimes and sometimes it does not.

The problem might lie with the federated table. I am clueless though.

This has been bugging me from quite some time.

Any help would be deeply appreciated.

Thanks

MySQL CONTINUE, EXIT handlers not detecting network error codes and sqlstates.

0 Answers0