MySql Fetch cursor into variable return null

Question

The problem is that the FETCH INTO (in the loop) does not put the value into the variable. I've looked at MYSQL | SP | CURSOR - Fetch cursor into variable return null but the table is already populated.

The transaction table looks like this:

CREATE TABLE `transactionentry` (
    `transactionid` bigint(20) NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (`transactionid`),
    ...
) ENGINE=InnoDB AUTO_INCREMENT=651 DEFAULT CHARSET=utf8;

The stored procedure:

PROCEDURE `doTxnHouseKeeping`()
BEGIN
    -- Loop invariant
    DECLARE noEntries INTEGER DEFAULT FALSE;
    -- Error codes
    DECLARE code CHAR(5) DEFAULT '00000';
    DECLARE msg TEXT;
    -- Txn vars
    DECLARE transactionId BIGINT(20);
    DECLARE lastTransactionId BIGINT(20) DEFAULT 0;
    -- testing
    DECLARE counter INT(11) DEFAULT 0;

    DEClARE txnEntryCur CURSOR FOR 
        SELECT 
            `transactionid`
        FROM 
            `transactionentry`
        LIMIT 1;

    DECLARE CONTINUE HANDLER FOR 
        NOT FOUND SET noEntries = TRUE;  

    DECLARE EXIT HANDLER FOR
        SQLEXCEPTION
            BEGIN
                GET DIAGNOSTICS CONDITION 1
                code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
                SELECT CONCAT('Error fetching transaction entries code: ', code, ' message: ', msg);
            END;

    OPEN txnEntryCur;

    mainLoop: LOOP
        FETCH 
            txnEntryCur 
        INTO 
            transactionId;

        IF noEntries THEN 
            LEAVE  mainLoop;
        END  IF;

        IF transactionId IS NOT NULL THEN
            INSERT INTO debugTable (`bigintval`) VALUES (transactionId);
        ELSE
            INSERT INTO debugTable (`strval`) VALUES ('transactionId is NULL');
        END IF;

        SET counter = counter + 1;
    END LOOP mainLoop;

    CLOSE txnEntryCur;

    SELECT CONCAT("Count: ", counter);        
END

Running the stored procedure returns this result:

+--------------------------+
|CONCAT("Count: ", counter)|
+--------------------------+
|                  Count: 1|
+--------------------------+

The result in the debug table is:

+------------+---------+-----------------------+
|iddebugTable|bigintval|                 strval|
+------------+---------+-----------------------+
|           1|     NULL|"transactionId is NULL"|
+------------+---------+-----------------------+

Which means that the value was not copied in

When running the SQL (as it is in the stored procedure), it returns:

+-------------+
|transactionid|
+-------------+
|          591|
+-------------+

Answer 1

I found the problem and it is weird. It doesn't cause any error and / or exceptions, just doesn't put any values into the variables. The solution is to change the cursor declare statement from:

DECLARE txnEntryCur CURSOR FOR 
    SELECT 
        `transactionid`
    FROM 
        `transactionentry`
    LIMIT 1;

To:

DECLARE txnEntryCur CURSOR FOR 
    SELECT 
        `transactionentry`.`transactionid`
    FROM 
        `transactionentry`
    LIMIT 1;

Not even the documentation indicated that it might have been a problem (https://dev.mysql.com/doc/refman/5.7/en/declare-cursor.html)

I only fully qualify the SELECT (and WHERE) part of the SQL statement if I'm selecting from more than one table and thus never picked this up on more complex queries.

I hope this will save someone some time in the future.

Answer 2

Your problem is here:

DECLARE transactionId BIGINT(20);

You declare a variable named transactionId so when you do this:

DEClARE txnEntryCur CURSOR FOR 
    SELECT 
        `transactionid`
    FROM 
        `transactionentry`
    LIMIT 1;

Your cursor's select is picking up the variable you declared which is why fully qualifying the field works. However, if you don't want to fully qualify the field in your select you can rename your variable.