We have changed our datastructure. Therefore we need to add some more colums to a table, fill them automatically with data from some existig columns and the remove some of the old columns. This should be an easy task.

For background: We are working in a Spring Boot 3 application using flyway for db migratrion. Locally (and for unit testing) we use an H2 InMemory db that will be recreated for each start. In the cloud environment we are using a MySQL8 db.

My idea for a migration script is:

1. Altering the table to add the new columns
2. Creating a stored procedure that iterates through the table and fills the new fields by using existing data
3. Executing the stp once and delete it again
4. Altering the table again to remove the obsolete columns

I implemented the script and tried it locally (H2), first. It ran into org.h2.jdbc.JdbcSQLSyntaxErrorException since flyway seems not to be able to recognise the delimiter $$ statement. But testing the script in an envirnment with MySQL db, it works fine.

The script looks like this (removed working parts):

delimiter $$
create procedure migrate_services()
begin
    declare finished boolean default false;

    -- declaring more variables

    declare cur_services cursor for select /* the fields to use */
                                    from services;
    declare continue handler for not found set finished = true;
    open cur_services;
    process_service :
    loop
        if finished = true then
            leave process_service;
        end if;
        fetch cur_services into /* the above declared variables */;
        /* computing data to be written into the new columns */
        update services
        set /* setting new data */
        where uuid = current_uuid;
    end loop;
    close cur_services;
end$$
delimiter ;

I tried some different delimiters. I tried removbeing delimieters. I spent the hole afternoon googleing the problem w/o finding a fitting solution.