wrong syntax to use near 'declare exists_check int'

Question

I'm writing an script to update a db schema, the script is the following:

delimiter //

begin 
    set @check_exists = 0;

    select count(*)
    into @check_exists
    from information_schema.columns
    where table_schema = 'my_app'
    and table_name = 'users'
    and column_name = 'points';
        
    if (@check_exists = 0) then
        alter table my_app.users
        add points int not null default 0
    end if;
end //

delimiter

when I run it, i get the below error:

Reason:
 SQL Error [1064] [42000]: (conn=34) 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 'set @check_exists = 0;

I have already checked the answers the below two posts but none solves my problem.

this question where the solution was to change the delimiter, and to
this question where the solution was to remove the DECLARE keyword and just declare the variable as it is in the MariaDB manual here with set @var int;

Eric Shieh · Answer 1 · 2021-07-14T22:22:44.403

0

I think you need to have a CREATE PROCEDURE before your BEGIN statement. So:

delimiter //

CREATE PROCEDURE UPDATE_SCHEMA()
begin 
    set @check_exists = 0;
    (...your other script here...)
end//

delimiter ;

call UPDATE_SCHEMA();

An additional note: check_exists in your if statement needs a @

edited Jul 14 '21 at 22:22

answered Jul 14 '21 at 22:16

Eric Shieh

697
5
11

I added the missing @ in the if statement, thanks. Regarding the `create procedure`, that would be one solution, but don't actually want to create a stored producedure. I found out now in the documentation that there's a way to keep the BEGIN END outside of the stored procedure. I posted it in a separate answer. – asa Jul 15 '21 at 09:10
@asa This is the problem when you tag a question with both mariadb and mysql. The two products are not the same. This answer is for mysql. Your solution does not apply to mysql (may do in the future), only to mariadb. If your tag your question as mysql, then you will get mysql answers. – Shadow Apr 24 '23 at 11:05

score 0 · Accepted Answer · answered Jul 15 '21 at 08:51

According to the BEGIN END documentation here, the syntax is:

BEGIN [NOT ATOMIC] [statement_list] END [end_label]

And here is what I did not know:

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.

As I'm not using the BEGIN END in a stored procedure, I have to add the NOT ATOMIC after the BEGIN keyword.

So the code should look like this:

delimiter //

begin not atomic
    set @check_exists = 0;


-- rest of the code here...

wrong syntax to use near 'declare exists_check int'

2 Answers2