MySql cursor is failing to retrieve all results

MySQLstored-procedures

I am importing data from one table(say old_table). I have made a table(say new_table) in another database and i am using mysql stored procedure to populate it.
I need to iterate through each record in old_table, and check if users email is already present in new_table(new_table have records already present, migrated from some other table).
My cursor fails to retrieve all the records and fails after fetching few records. The value of no_more_rows becomes 1 after fetching few records even though the query gives more values when run separately.
Here is the code-

    CREATE DEFINER=`root`@`localhost` PROCEDURE `test_3_1(logging and nested procedure)`(IN `init_count` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    SQL SECURITY DEFINER
    COMMENT ''
BLOCK1: BEGIN
/*
5.1
migrating users from table con_consumer_detail 
(these con_consumer_detail users are guest checkouts and have no entry in con_consumer)
Tables populated in new db are
um_user
um_user_details -- (no sense to enrich this table)
um_user_shipping_information    --
um_user_proofs          (no proofs in guest checkouts)
um_user_phone_numbers           --
--no guest checkout with entry in table con_consumer_source_detail
*/

declare my_fname varchar(255);
declare my_lname varchar(255);
declare my_email varchar(255);

declare account_type varchar(20);
declare account_status varchar(20);
declare user_type varchar(255); 
declare my_con_consumer_detail_id bigint(20);

declare my_gender varchar(20);
declare my_date_of_birth date;

declare my_address_street1 varchar(255);
declare my_address_street2 varchar(255);
declare my_address_city varchar(255);
declare my_address_state varchar(255);
declare my_address_country varchar(255);
declare my_zip varchar(255);

declare my_pancard_number varchar(15);
declare my_aadharcard_number varchar(15);

declare my_um_user_id bigint(20);

declare no_more_rows1 int default 0;
declare count_id bigint(20) default 0;
declare count_row_completed bigint(20);

declare umuser_cursor cursor for select 
stg_citruspay.con_consumer_detail.first_name,
stg_citruspay.con_consumer_detail.last_name,
stg_citruspay.con_consumer_detail.email,
stg_citruspay.con_consumer_detail.id as con_consumer_detail_id,
stg_citruspay.con_consumer_detail.sex,
stg_citruspay.con_consumer_detail.date_of_birth,
stg_citruspay.con_consumer_detail.address_street1,
stg_citruspay.con_consumer_detail.address_street2,
stg_citruspay.con_consumer_detail.address_city,
stg_citruspay.con_consumer_detail.address_state,
stg_citruspay.con_consumer_detail.address_country,
stg_citruspay.con_consumer_detail.address_zip,

stg_citruspay.con_consumer_detail.pan_card,
stg_citruspay.con_consumer_detail.aadhar_card
from con_consumer_detail 
left join con_consumer on
con_consumer_detail.id=con_consumer.consumer_detail
where con_consumer.id is null 
limit 200;

DECLARE continue handler FOR not found set no_more_rows1= 1;
set count_id=init_count;

open umuser_cursor;
consumer_loop: LOOP

set count_id=count_id+1;


fetch umuser_cursor into my_fname,my_lname,my_email,my_con_consumer_detail_id,
my_gender,my_date_of_birth,my_address_street1,my_address_street2,my_address_city,
my_address_state,my_address_country,my_zip,my_pancard_number,my_aadharcard_number;

set account_status='ENABLED';
set account_type='BASIC';
set user_type='CONSUMER';




select count_id,my_fname,my_lname,my_email,my_con_consumer_detail_id,
my_gender,my_date_of_birth,my_address_street1,my_address_street2,my_address_city,
my_address_state,my_address_country,my_zip,my_pancard_number,my_aadharcard_number;

select "no_more_rows1:"+ no_more_rows1;

 if (no_more_rows1) then
    insert into stg_um_db.logger_table(
    rows_processed,
    procedure_executed
    )
    values(
    count_id-1,
    "5_1_last_row"
    );
leave consumer_loop;
close umuser_cursor;
end if;

/*finding existing user to merge guest checkout*/
    select stg_um_db.um_user.id into my_um_user_id
    from stg_um_db.um_user
    where um_user.email=my_email;

    select "my_um_user_id:"+ my_um_user_id;

    if(my_um_user_id is not null) then
    set count_id=count_id-1;
        select "existing user";
        /*
        if(char_length(my_zip)>32)then
        insert into stg_um_db.um_user_shipping_information(
        stg_um_db.um_user_shipping_information.um_user,
        stg_um_db.um_user_shipping_information.address_first_name,
        stg_um_db.um_user_shipping_information.address_last_name,
        stg_um_db.um_user_shipping_information.address_street1,
        stg_um_db.um_user_shipping_information.address_street2,
        stg_um_db.um_user_shipping_information.address_city,
        stg_um_db.um_user_shipping_information.address_state,
        stg_um_db.um_user_shipping_information.address_country
        )
        values(
        my_um_user_id,
        my_fname,
        my_lname,
        my_address_street1,
        my_address_street2,
        my_address_city,
        my_address_state,
        my_address_country
        );
        else
        insert into stg_um_db.um_user_shipping_information(
        stg_um_db.um_user_shipping_information.um_user,
        stg_um_db.um_user_shipping_information.address_first_name,
        stg_um_db.um_user_shipping_information.address_last_name,
        stg_um_db.um_user_shipping_information.address_street1,
        stg_um_db.um_user_shipping_information.address_street2,
        stg_um_db.um_user_shipping_information.address_city,
        stg_um_db.um_user_shipping_information.address_state,
        stg_um_db.um_user_shipping_information.address_country,
        stg_um_db.um_user_shipping_information.zip
        )
        values(
        my_um_user_id,
        my_fname,
        my_lname,
        my_address_street1,
        my_address_street2,
        my_address_city,
        my_address_state,
        my_address_country,
        my_zip
        );
        end if;
        */
    else
    select "New user";
        /*for new user*//*
        insert into stg_um_db.um_user(
        stg_um_db.um_user.id,
        stg_um_db.um_user.first_name,
        stg_um_db.um_user.last_name,
        stg_um_db.um_user.email,
        stg_um_db.um_user.account_type,
        stg_um_db.um_user.account_status,
        stg_um_db.um_user.user_type,
        stg_um_db.um_user.my_con_consumer_detail_id
        )
        values(
        count_id,
        my_fname,
        my_lname,
        my_email,
        account_type,
        account_status,
        user_type,
        my_con_consumer_detail_id
        );

        insert into stg_um_db.um_user_details(
        stg_um_db.um_user_details.um_user,
        stg_um_db.um_user_details.first_name,
        stg_um_db.um_user_details.last_name,
        stg_um_db.um_user_details.gender,
        stg_um_db.um_user_details.date_of_birth
        )
        values
        (
        count_id,
        my_fname,
        my_lname,
        my_gender,
        my_date_of_birth
        );

        if(char_length(my_zip)>32)then
        insert into stg_um_db.um_user_shipping_information(
        stg_um_db.um_user_shipping_information.um_user,
        stg_um_db.um_user_shipping_information.address_first_name,
        stg_um_db.um_user_shipping_information.address_last_name,
        stg_um_db.um_user_shipping_information.address_street1,
        stg_um_db.um_user_shipping_information.address_street2,
        stg_um_db.um_user_shipping_information.address_city,
        stg_um_db.um_user_shipping_information.address_state,
        stg_um_db.um_user_shipping_information.address_country
        )
        values(
        count_id,
        my_fname,
        my_lname,
        my_address_street1,
        my_address_street2,
        my_address_city,
        my_address_state,
        my_address_country
        );
        else
        insert into stg_um_db.um_user_shipping_information(
        stg_um_db.um_user_shipping_information.um_user,
        stg_um_db.um_user_shipping_information.address_first_name,
        stg_um_db.um_user_shipping_information.address_last_name,
        stg_um_db.um_user_shipping_information.address_street1,
        stg_um_db.um_user_shipping_information.address_street2,
        stg_um_db.um_user_shipping_information.address_city,
        stg_um_db.um_user_shipping_information.address_state,
        stg_um_db.um_user_shipping_information.address_country,
        stg_um_db.um_user_shipping_information.zip
        )
        values(
        count_id,
        my_fname,
        my_lname,
        my_address_street1,
        my_address_street2,
        my_address_city,
        my_address_state,
        my_address_country,
        my_zip
        );
        end if;


        if(my_pancard_number is not NULL and my_pancard_number!='') then
        insert into stg_um_db.um_user_proofs(
        stg_um_db.um_user_proofs.um_user,
        stg_um_db.um_user_proofs.proof_number,
        stg_um_db.um_user_proofs.proof_description_id
        )
        values(
        count_id,
        my_pancard_number,
        3
        );
        end if;

        if(my_aadharcard_number is not NULL and my_aadharcard_number!='') then
        insert into stg_um_db.um_user_proofs(
        stg_um_db.um_user_proofs.um_user,
        stg_um_db.um_user_proofs.proof_number,
        stg_um_db.um_user_proofs.proof_description_id
        )
        values(
        count_id,
        my_aadharcard_number,
        6
        );
        end if;
        */
end if;

        BLOCK2: BEGIN
        declare my_phone_number varchar(255);
        declare my_phone_type varchar(255);

        declare no_more_rows2 int default 0;

        declare um_phone_cursor cursor for select 
            stg_citruspay.con_consumer_detail_phone_number.phone_number,
            stg_citruspay.con_consumer_detail_phone_number.`type`
            from stg_citruspay.con_consumer_detail_phone_number
            where stg_citruspay.con_consumer_detail_phone_number.consumer_detail=my_con_consumer_detail_id;

        declare continue handler for not found
            set no_more_rows2=1;

        open um_phone_cursor;
        phone_loop: LOOP

        fetch um_phone_cursor into my_phone_number,my_phone_type;

/*  
select count_id,phone_number,phone_type,my_con_consumer_detail_id;
*/
        if no_more_rows2 then
            leave phone_loop;
            close um_phone_cursor;
        end if;

        /*
        if(my_um_user_id is not null) then

            if(phone_number is not null  and phone_number!='' ) then 
            insert into stg_um_db.um_user_phone_numbers(
            stg_um_db.um_user_phone_numbers.um_user,
            stg_um_db.um_user_phone_numbers.phone_number,
            stg_um_db.um_user_phone_numbers.phone_type
            )
            values(
            my_um_user_id,
            my_phone_number,
            my_phone_type
            );
            end if;

        else 
            if(phone_number is not null  and phone_number!='' ) then
            insert into stg_um_db.um_user_phone_numbers(
            stg_um_db.um_user_phone_numbers.um_user,
            stg_um_db.um_user_phone_numbers.phone_number,
            stg_um_db.um_user_phone_numbers.phone_type
            )
            values(
            count_id,
            my_phone_number,
            my_phone_type
            );
            end if;

        end if;
        */
        end loop phone_loop;
        end BLOCK2;

if (! (count_id % 10)) then 
set count_row_completed=count_id;

insert into stg_um_db.logger_table(
rows_processed,
procedure_executed
)
values(
count_row_completed,
"5_1"
);

DO SLEEP(0.5);
end if;

end loop consumer_loop;
END BLOCK1

Best Answer

NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant only within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition).

This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows."

Work Around: Use put SELECT ... INTO that may return no rows into a separate BEGIN ... END block with its own CONTINUE handler for NOT FOUND as a workaround.

Related Solutions

Mysql – Why is this MySQL proc using cursors failing to retrieve results

Try tagging all local variables with something like my_

DELIMITER @@

DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
    -- Declare loop constructs --
    DECLARE done INT DEFAULT FALSE; 

    -- Declare Person variables --
    DECLARE my_person_id INT;
    DECLARE my_era_username VARCHAR(100);
    DECLARE my_last_name VARCHAR(50);
    DECLARE my_first_name VARCHAR(50);
    DECLARE my_email VARCHAR(100);
    DECLARE my_email_primary VARCHAR(50);
    DECLARE my_degree_id_1 INT;
    DECLARE my_degree_id_2 INT;
    DECLARE my_member_status INT;

    DECLARE my_user_id INT;
    DECLARE my_user_email VARCHAR(100);

    -- Declare Cursor --
    DECLARE member_cursor CURSOR FOR 
        SELECT person_id, era_username, last_name, first_name,
              TRIM(email), TRIM(email_primary), degree_id_1, degree_id_2,
              member_status FROM z_data_person
        WHERE member_status IS NOT NULL;

    -- Declare Continue Handler --
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN member_cursor;

    read_loop: LOOP

        -- Fetch data from cursor --
        FETCH member_cursor 
        INTO my_person_id, my_era_username, my_last_name, my_first_name,
              my_email, my_email_primary, my_degree_id_1, my_degree_id_2,
              my_member_status;

        -- Exit loop if finished --
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Create User --
        SET my_user_id = SELECT (MAX(uid) + 1) FROM users;
        IF my_email_primary IS NOT NULL AND my_email_primary NOT LIKE '%null%' THEN
            SET my_user_email = my_email_primary;
        ELSE
            SET my_user_email = my_email;
        END IF;

        INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
        VALUES (my_user_id, my_era_username, SHA1(RAND()), my_user_email, UNIX_TIMESTAMP());

        -- Create Member --
        INSERT INTO `members` (`uid`, `first_name`, `last_name`, `phone`,
              `member_category_id`, `subscription_weekly_email`,
              `subscription_monthly_newsletter`)
        VALUES (my_user_id, my_first_name, my_last_name, my_phone_num,
              my_member_status, my_weekly_com, my_monthly_com);

        -- Add Degrees --
        IF degree_id_1 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (my_user_id, my_degree_id_1);
        END IF;

        IF degree_id_2 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (my_user_id, my_degree_id_2);
        END IF;

        -- Add Areas of Expertise --
        INSERT INTO `member_expertises_held` (`uid`, `specialty_id`)
        SELECT (SELECT MAX(uid) FROM users), z.specialty_id
        FROM z_map_person_specialty AS z
        WHERE z.person_id = my_person_id;

    END LOOP read_loop;

    CLOSE member_cursor;

END; @@

DELIMITER ;
CALL import_members();

In the book MySQL Stored Procedure Programming, all cursor examples give the local variable a unique name from its corresponding table column (if you have the book, see page 108 Example 5-15).

Sidenote : Where are weekly_com and monthly_com coming from ???

Mysql – Cannot update certain rows in innodb tables

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

Best Answer

Related Solutions

Mysql – Why is this MySQL proc using cursors failing to retrieve results

Mysql – Cannot update certain rows in innodb tables

Related Question