Migrating to MySQL8, cursor in stored procedure causes error on import

Question

I am migrating a Moodle site to MySQL8 and have had no problems with the core system. However, some of our reports depend on two custom stored procedures, one of which is as follows:

CREATE DEFINER=`someuseraccount`@`somewhere` FUNCTION `getOnlineTimeForUser`(USERID_IN int) RETURNS int(11)
BEGIN

DECLARE currenttime integer;
DECLARE nexttime integer;
DECLARE count1 integer;
DECLARE count2 integer;
DECLARE totaldedication_time integer DEFAULT 0;
DECLARE dedication integer;
DECLARE session_start integer;
DECLARE done INT DEFAULT FALSE;
    
DECLARE cur CURSOR FOR SELECT  @rownum := @rownum + 1 as row_number, timecreated
FROM moodle.mdl_logstore_standard_log
cross join (select @rownum := 0) r
where userid=USERID_IN
order by timecreated asc;

DECLARE cur2 CURSOR FOR SELECT a.* FROM (SELECT  @rownum := @rownum + 1 as row_number, timecreated
FROM moodle.mdl_logstore_standard_log
cross join (select @rownum := 0) r
where userid=USERID_IN
order by timecreated asc) a where a.row_number = count1+1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
    read_loop: LOOP

        FETCH cur INTO count1, currenttime;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF (count1 <2) THEN
            SET session_start = currenttime;
            SET totaldedication_time = 0;
        END IF;
        
        OPEN cur2;
            read_loop2: LOOP
                FETCH cur2 INTO count2, nexttime;
                IF done THEN
                    SET done = FALSE;
                    LEAVE read_loop2;
                END IF;

                IF (nexttime - currenttime) > 300 THEN
                    SET dedication = currenttime - session_start;
                    SET totaldedication_time = totaldedication_time + dedication;
                    SET session_start = nexttime;
                END IF;
            END LOOP;
        CLOSE cur2;
    END LOOP;
CLOSE cur;

SET totaldedication_time = totaldedication_time + dedication;

RETURN ceil(totaldedication_time/60);
    
END ;;

When importing these stored procedures, which were functioning fine on 5.7.x, I'm given the following error:

ERROR 1064 (42000) at line 36: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row_number, timecreated FROM moodle.mdl_logstore_standard_log cross join (se' at line 14

It appears that the issue is in the definition of the first cursor. If I comment this whole procedure out of the backup and import again, the next stored procedure fails with the same error, so unless anyone suggests otherwise I won't include it for simplicity's sake. I'm not by any stretch familiar with cursors and their syntax and I've not been able to ascertain any major differences from the articles and answers I've read on related upgrade questions as to why such syntax from 5.7 might not work on 8.x, although of course it's possible that this syntax might have been deprecated already in 5.7 and removed in 8.x - I'm really swimming around in the dark on this to be honest. It's not helped by the fact that I'm not 100% sure I understand what the original procedure was doing.

Answer 1

ROW_NUMBER is a reserved keyword in MySQL 8.0. See https://dev.mysql.com/doc/refman/8.0/en/keywords.html

If you need to use this as an identifier, enclose it in back-ticks each time you use it.

... SELECT  @rownum := @rownum + 1 as `row_number`, ...

Or else rename it something that is not a reserved keyword.

... SELECT  @rownum := @rownum + 1 as rownum, ...

Note also that in MySQL 8.0, they discourage the practice of assinging variables as side-effects within queries. https://dev.mysql.com/doc/refman/8.0/en/user-variables.html says:

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

In your case, you're just using it to generate row numbers. That's what ROW_NUMBER() is for, so you could rewrite your query as:

SELECT ROW_NUMBER() OVER (ORDER BY timecreated) AS rownum, timecreated
FROM moodle.mdl_logstore_standard_log
WHERE userid=USERID_IN
ORDER BY timecreated ASC;

ROW_NUMBER() is just one of many standard window functions implemented in MySQL 8.0. See https://dev.mysql.com/doc/refman/8.0/en/window-functions.html to read about them.

Window functions are standard SQL and should be used for most of the ways that people currently are forced to use := for inline variable assignment. Window functions are quite versatile and can do things that would be incredibly difficult using inline variable assignment techniques.