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 'create procedure insert_into_backup_table()

General Discussion
1

I am getting this error when I try to run Stored Procedure in liquibase.

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 ‘create procedure insert_into_backup_table()

script in Mysql (ran without error)

delimiter #
create procedure insert_into_backup_table()
begin
declare orginalName varchar(255);
declare max_value int unsigned default @countAllIds;
declare counter int unsigned default 0;
  start transaction;
  while counter <= max_value do
    set orginalName = (select area_name 
		from tmp_kpi_area 
        where id = counter
	);
    update tmp_kpi_area 
		set area_id = (
			select id
			from kpi_area area
			where name = orginalName
			Limit 1
		)
     where id = counter;  
    set counter=counter + 1;
  end while;
  commit;
end #
delimiter ;

call insert_into_backup_table();

in the liquibase changset:

<changeSet id="SAM-2520-Remove-SZ-from-areas-6" author="mail.com">
		<sql dbms="mysql" splitStatements="true" end-delimiter="#">
		<createProcedure>
		<![CDATA[
		set @countAllIds = (select count(id) from tmp_kpi_area);
		drop procedure if exists insert_into_backup_table;
		#
		create procedure insert_into_backup_table()
		begin
		declare orginalName varchar(10);
		declare max_value int unsigned default 25;
		declare counter int unsigned default 0;
		  start transaction;
		  while counter <= max_value do
		    set orginalName = (select area_name 
				from tmp_kpi_area 
		        where id = counter
			);
		    update tmp_kpi_area 
				set area_id = (
					select id
					from kpi_area area
					where name = orginalName
					Limit 1
				)
		     where id = counter;  
		    set counter = counter + 1;
		  end while;
		  commit;
		end
		#
		]]>
		</createProcedure>
		</sql>
    </changeSet>

But I am getting error in here. Could you please help me to correct the liquibase query. thanks all

2

So the error is the subject of the email. I added it to your post for clarity.

I found a thread on StackOverflow that is asking a similar question: MySQL stored procedure syntax error after BEGIN

The accepted answer to that post may also resolve your error.

You are missing the delimiter definition before and after the stored proc definition:

If you use the MySQL client program to define a stored program containing semicolon characters, a problem arises. By default, MySQL itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause MySQL to pass the entire stored program definition to the server.

To redefine the MySQL delimiter, use the delimiter command. […] The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by MySQL itself.

Since the stored proc definition and body were ok, syntax chack gave you the thumbs up, but the code would not run properly in your client.

Use the following skeleton for defining a stored procedure:

delimiter //
create procedure ...
...
end
//
delimiter ;