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 '' at line 3 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

General Discussion
1

I have a stored procedure as below:

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();

It is working fine in workbranch
I want to automate with Liquibase and that is how I changed:

		<sql dbms="mysql" splitStatements="true" end-delimiter="#" stripComments="true">
		<![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(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 #
		
		call insert_into_backup_table();
		]]>
		</sql>
    </changeSet>

As a result I am getting this error:

Reason: liquibase.exception.DatabaseException: 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 '' at line 3 [Failed SQL: create procedure `insert_into_backup_table`() begin declare `orginalName` varchar(255)] at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessPropertyValues(CommonAnnotationBeanPostProcessor.java:321) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1257) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:551) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:756) at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542) at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:443) at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:325) at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107) at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5118) at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5634) at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:145) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1571) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1561) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)

Would really appreciate any advice on it!!!

2

It looks like you have a typo in the code, and that may be why you’re getting that SQL Syntax error. Here is a link to the Oracle documentation for the originalName object. Please try correcting that and running it again. If you continue to encounter errors, you can reply to them on this post for further help.
https://docs.oracle.com/cd/E11882_01/win.112/e17727/sermthod143.htm#OOFOL612

3

I think your error is caused by not using the defined endDelimiter after these two statements:

set @countAllIds = (select count(id) from tmp_kpi_area);
drop procedure if exists `insert_into_backup_table`;