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.

@P.Salmon Sorry! not sure how i forgot to put that, copied an outdated query. I have been using BEGIN NOT ATOMIC. — Jeff, Feb 01 '22 at 20:41

score 1 · Answer 1 · answered Feb 01 '22 at 20:33

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.

answered Feb 01 '22 at 20:33

ysth

96,171
6
121
214

Sorry! not sure how i forgot to put that, copied an outdated query. I have been using BEGIN NOT ATOMIC. i updated the snippet. – Jeff Feb 01 '22 at 20:41
do you mean you are still getting the error even with that? in my fiddle link, I see it working – ysth Feb 01 '22 at 20:54
please do make sure your question has exactly what you are trying and the exact error you are getting from that – ysth Feb 01 '22 at 20:55

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

1 Answers1