Mariadb 10.3.29 BEGIN... END statement not working with DECLARE

Question

i am having an issue getting the following statement to run in Mariadb 10.3.29 and 10.3.13

delimiter |
BEGIN NOT ATOMIC
    DECLARE finished int default 0;
    DECLARE query varchar(500) default "";
    
    DECLARE curQuery
        CURSOR FOR
            SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA ,'.', table_name, ' MODIFY COLUMN `type` tinyint(1) DEFAULT NULL NULL COMMENT "test";') 
            FROM information_schema.tables WHERE table_name = 'test_table';
        
        
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;
    
    open curQuery;
    
    executeQuery: LOOP
        FETCH curQuery INTO query;
        IF finished = 1 THEN
            LEAVE executeQuery;
        END IF;
        prepare stmt from query;
        execute stmt;
    END LOOP executeQuery;
    CLOSE curQuery;
END|

I am getting the following error:

Reason:

SQL Error [1064] [42000]: (conn=54) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE finished int default 0;

Using dbeaver 21.3.3

According to these docs here: https://mariadb.com/kb/en/using-compound-statements-outside-of-stored-programs/ i should be able to do this outside of a "stored program" on mariadb 10.1.1+. However, i can get this to run inside of a stored procedure, but i would really like to run this in plain SQL if possible.

Thanks in advance.

Answer 1

You appear to need BEGIN NOT ATOMIC explicitly, which is what the examples show.

fiddle

https://mariadb.com/kb/en/begin-end/:

NOT ATOMIC is required when used outside of a stored procedure. Inside stored procedures or within an anonymous block, BEGIN alone starts a new anonymous block.