MySQL Cursor bug?

Posted on November 7, 2012, 9:39 pm, by Rhys, under MySQL.

I came across this little funny with MySQL cursors today. This may be documented somewhere in the manual but I couldn’t find it. Thought I’d post it here for anyone else experiencing cursor issues with MySQL. First, a quick illustration of the issue…

This stored procedure has valid syntax and compiles (in 5.1 & 5.4).

?View Code MYSQL
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `test`.`usp_cursor_test`$$
 
CREATE
    PROCEDURE `test`.`usp_cursor_test`()
    LANGUAGE SQL
    BEGIN
 
	DECLARE done INT DEFAULT FALSE;
        DECLARE table_schema VARCHAR(100);
        DECLARE table_name VARCHAR(100);
 
        DECLARE my_cursor CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME
                                     FROM INFORMATION_SCHEMA.TABLES
                                     WHERE TABLE_TYPE = 'BASE TABLE';
 
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
        OPEN my_cursor;
 
        table_loop: LOOP
 
		FETCH my_cursor INTO table_schema, 
				     table_name;
 
		IF done THEN
			LEAVE table_loop;
		END IF;
 
		SELECT table_schema, table_name;
 
	END LOOP;
 
        CLOSE my_cursor;
 
    END$$
 
DELIMITER ;

If you call this stored procedure;

?View Code MYSQL
CALL usp_cursor_test();

You’ll observe a load of null records are returned. What’s going on here? After a little head scratching I discovered this was due to my FETCH statement using variable names the same as the cursor values. The fix is quite simple;

?View Code MYSQL
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `test`.`usp_cursor_test`$$
 
CREATE
    PROCEDURE `test`.`usp_cursor_test`()
    LANGUAGE SQL
    BEGIN
 
	DECLARE done INT DEFAULT FALSE;
        DECLARE p_table_schema VARCHAR(100);
        DECLARE p_table_name VARCHAR(100);
 
        DECLARE my_cursor CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME
                                     FROM INFORMATION_SCHEMA.TABLES
                                     WHERE TABLE_TYPE = 'BASE TABLE';
 
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
        OPEN my_cursor;
 
        table_loop: LOOP
 
		FETCH my_cursor INTO p_table_schema, 
				     p_table_name;
 
		IF done THEN
			LEAVE table_loop;
		END IF;
 
		SELECT p_table_schema, p_table_name;
 
	END LOOP;
 
        CLOSE my_cursor;
 
    END$$
 
DELIMITER ;

Simply ensure your variables names don’t match the cursor column names and you’re good to go. This doesn’t just affect the INFOMRATION_SCHEMA database. I’ve tested this using one of my own databases and the issue happens just the same.

While this is a little annoying, it’s good practice to append your variable names with something like p_ in my opinion to make your code more readable.


Tags: , , ,
Comment (RSS)  |  Trackback

2 Comments

  1. Clement says:
    December 22, 2012 at 7:13 am

    I too was facing the same issue. My variable and column names are not the same. I had appened the variable names with ‘v_’ to identify them easliy. However, I renamed them as ‘a’ and ‘b’ but I am still facing the same issue.

    Here’s my code with the required DDL’s, DML’s and output.

    – Creating the schema
    CREATE SCHEMA `test_cursor` ;

    – Creating the table
    CREATE TABLE `product` (
    `product_id` int(5) NOT NULL AUTO_INCREMENT,
    `product_code` varchar(5) DEFAULT NULL,
    PRIMARY KEY (`product_id`)
    ) ENGINE=InnoDB ;

    – Inserting test data into the table
    INSERT INTO `test_cursor`.`product` (`product_code`) VALUES (‘prodA’);
    INSERT INTO `test_cursor`.`product` (`product_code`) VALUES (‘prodB’);
    INSERT INTO `test_cursor`.`product` (`product_code`) VALUES (‘prodC’);
    INSERT INTO `test_cursor`.`product` (`product_code`) VALUES (‘prodD’);
    INSERT INTO `test_cursor`.`product` (`product_code`) VALUES (‘prodE’);

    – Procedure
    DELIMITER $$
    CREATE PROCEDURE `test_cursor`.`P_READ_PRODUCT` ()
    BEGIN
    /*
    The procedure fetches data from the source product table and displays the same.
    */
    – VARIABLE/CONSTANT DECLARATION
    DECLARE v_done INT DEFAULT FALSE; — Variable used in the continue handler.
    – variables used to store the values fetched by the cursor itr_product from product table.
    DECLARE a int(5);
    DECLARE b varchar(5);

    – Declaration of cursor for iterating through the unprocessed records in the source table.
    DECLARE itr_product CURSOR FOR SELECT product_id, product_code FROM test_cursor.product;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; — Declaring continue handler for the cursor stg_product_data

    OPEN itr_product; — Open the cursor
    read_loop: LOOP — Loop through the records
    FETCH itr_product INTO a, b; — Fetching data into variables
    IF v_done THEN — Checking if the cursor has fetched the last record
    LEAVE read_loop; — Exit if last record had been fetched
    ELSE
    SELECT @a, @b ; — Displaying the values fetched by the cursor
    END IF;
    END LOOP;
    CLOSE itr_product; — Close the cursor
    END

    – Calling the procedure
    call `test_cursor`.`P_READ_PRODUCT`();

    – Result set/Output
    @a @b
    null null

  2. Rhys says:
    December 22, 2012 at 12:42 pm

    Hi Francis,

    Remove the @ symbols from your variables. ‘a’ and ‘@a’ are different variables hence the null.

    Rhys

Leave a Reply

 
«
»