Mysql – Nested Cursors in Mysql problem with FETCH

MySQLmysql-workbenchstored-procedures

I have the problem with my nested cursors in MySQL.

My second cursor only advances 1 single record of the first courses and then shows me message indicating. Below is a sample of the response from my Mysql Workbeanch.

OPEN queryLegalArticleSource;
    queryLegalArticleSource_LOOP : LOOP
        -- PARSED VARS
        FETCH queryLegalArticleSource INTO configTypeArticleTypeTwo;  
        -- ************************************
        -- VALIDATE PROCESS noMoreRows
        -- ************************************
        IF noMoreRowsTwo = 1 THEN
            CLOSE queryLegalArticleSource;
            LEAVE queryLegalArticleSource_LOOP;
        END IF;                        
        SELECT legalBodyId, companyIdSource, configTypeArticleTypeTwo, noMoreRowsTwo;
    END LOOP queryLegalArticleSource_LOOP;     
CLOSE queryLegalArticleSource;

If I comment the FETCH line

-- FETCH queryLegalArticleSource INTO configTypeArticleTypeTwo;

Get all the results, but I do not have the variable I need.

Check if any cursor was closing it before, but apparently I have everything right.

Does anyone have any idea?

Best Answer

You are trying to close the cursor after it is already closed. If no more rows=1 is true, it closes the cursor. Then exists the loop. Then tries to close the cursor again.

Related Solutions

MySQL Cursor Batching Process

Perhaps you could add a counter to the loop so that every 100 rows, you run SLEEP for one second.

I will add that code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_UsersLoop`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_UsersLoop`(
)
BEGIN
  DECLARE v_BatchCount,v_BatchLimit INTEGER;
  DECLARE v_user_id INTEGER DEFAULT 0 ;
  DECLARE v_officialjobtitle VARCHAR(100) DEFAULT '';
  DECLARE v_location VARCHAR(100) DEFAULT '';
  DECLARE v_corp_id BIGINT DEFAULT 0;
  DECLARE v_name VARCHAR(100) DEFAULT '';
  DECLARE maxcount INTEGER DEFAULT 5; -- 1000; -- 10 ;  -- max numbers of users (testing) - change to select count(*) from users where role='Level 6';
  DECLARE loopcounter INTEGER DEFAULT 0 ;
  DECLARE user_cursor CURSOR FOR 
  SELECT u.`user_id`,TRIM(u.`officialjobtitle`),TRIM(u.`location`),u.`corp_id`,CONCAT(TRIM(u.`fname`),' ',TRIM(u.`lname`)) `name` FROM users u WHERE u.`role`='Level 6' ORDER BY u.`officialjobtitle` ;
  SELECT COUNT(*) INTO maxcount FROM users WHERE role='Level 6' and corp_id=1; -- number of users to loop through

  SET v_BatchCount = 0;
  SET v_BatchLimit = 100;

  OPEN user_cursor ;
  get_users :
  LOOP
    FETCH user_cursor INTO v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name ;
    IF v_officialjobtitle<>'' THEN
        CALL sp_Step2 (v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name) ;  -- Do all process for the current user
        SET v_BatchCount = v_BatchCount + 1;
        IF v_BatchCount = v_BatchLimit THEN
            SELECT SLEEP(1) INTO @dummy;
            SET v_BatchCount = 0;
        END IF ;
    END IF ;
    SET loopcounter = loopcounter + 1 ;
    IF loopcounter > maxcount THEN 
        LEAVE get_users ;
    END IF ;
  END LOOP get_users ;
  CLOSE user_cursor ;

  SELECT loopcounter AS `Rows_Processed`;

END$$

DELIMITER ;

Please note the code

SET v_BatchCount = 0;
SET v_BatchLimit = 100;

and

SET v_BatchCount = v_BatchCount + 1;
IF v_BatchCount = v_BatchLimit THEN
    SELECT SLEEP(1) INTO @dummy;
    SET v_BatchCount = 0;
END IF ;

These encapsulate the SLEEP for every 100 rows.

Feel free to increase the v_BatchLimit to 1000 or more if you are dealing with large result sets.

Please note that I changed

SELECT SLEEP(1);

SELECT SLEEP(1) INTO @dummy;

That way, each SLEEP command does not echo anything.

Mysql – What would be most elegant way to replicate data contents periodically into a MySQL table

If you don't need to retain the data from the previous day when you update the data, you could do this without downtime:

Day 1:

insert data from API into Table1

Day 2+:

create a new table with the same structure as Table1

create NewTable like Table1;

insert API data into NewTable (note: app is still using Table1 at this point)
switch out the tables in one statement

rename Table1 to OldTable, NewTable to Table1;

drop OldTable now if you want to, or keep some archives

Best Answer

Related Solutions

MySQL Cursor Batching Process

Mysql – What would be most elegant way to replicate data contents periodically into a MySQL table

Related Question