MySQL 8.0 – Creating Stored Procedures

MySQLmysql-8.0stored-procedures

I am trying to create a SP which will run batch update on about 100 million rows in a table. I created the below SP for it:

delimiter //

CREATE PROCEDURE `bulk_update`(IN table_name varchar(30))
BEGIN
DECLARE rows INT;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET rows = 1;
WHILE rows > 0
do
SET autocommit=1;
SET @query =CONCAT('update ',table_name,' set is_summarized="N";');
PREPARE update_stmt FROM @query;
EXECUTE update_stmt;
SET rows = row_count();
select sleep(1); 
commit;
DEALLOCATE PREPARE update_stmt;
END WHILE;
END //

It gives below error >>

    ERROR 1064 (42000): 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 'rows INT;
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET rows = 1;
' at line 3

Strange part is that I am able to create the SP in MySQL-5.6, but it fails in MySQL-8 only. Can someone point me to what I am missing? Thank you

Best Answer

Either use autocommit=1 throughout, or use BEGIN and COMMIT.

And, once set, you do not need to set autocommit again.

So, get rid of COMMIT and move the SET out of the loop.

Is some other process changing the value of is_summarized while this proc is sleeping? I doubt if your technique will catch all edge cases correctly.

Instead of using a declared rows, use a variable: @rows. It does not need to be DECLAREd.

Most of the lines of the proc can be pulled out of the loop. (However, this won't really make any difference.)

As for ROWS -- Most major versions and some minor versions introduce new keywords. ROWS apparently was added between 5.6 and 8.0. I avoid some such errors by prefixing any arguments to stored procs and declared variables in stored procs with an underscore. This convention also helps avoid confusion between variables and column names.

Related Question