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.