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 14

Have been fighting with this for a a little , still cannot figure out what is wrong

The below is sql

-- liquibase formatted sql

-- changeset XXXX:YYYY-0.2.0


-- after insert
-- end-delimiter:$$

CREATE trigger tbl_timeseries_ai
    AFTER INSERT
    ON tbl_timeseries FOR EACH ROW
BEGIN

    INSERT INTO tbl_timeseries_audit (id , action , name , description , datasource , ticker , field , frequency , currency , lag , deleted , version , updated_by ,updated_dt)  values ( NEW.id , 'INSERT' , NEW.name , NEW.description , NEW.datasource , NEW.ticker , NEW.field , NEW.frequency , NEW.currency , NEW.lag , NEW.deleted , NEW.version , NEW.updated_by , New.update_dt);
END 
$$

-- rollback drop trigger  tbl_timeseries_ai;

-- after udpate
CREATE trigger tbl_timeseries_au
    AFTER UPDATE
    on tbl_timeseries  FOR EACH ROW
BEGIN

    INSERT INTO tbl_timeseries_audit (id , action , name , description , datasource , ticker , field , frequency , currency , lag , deleted , version , updated_by ,updated_dt) values ( NEW.id , 'UPDATE' , NEW.name , NEW.description , NEW.datasource , NEW.ticker , NEW.field , NEW.frequency , NEW.currency , NEW.lag , NEW.deleted , NEW.version , NEW.updated_by , New.update_dt);
END

$$

-- rollback drop trigger  tbl_timeseries_au;


-- end-delimiter:;

Got the error “Caused by: java.sql.SQLSyntaxErrorException: 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 14” ,

Line 14 is

INSERT INTO tbl_timeseries_audit (id , action , name , description , datasource , ticker , field , frequency , currency , lag , deleted , version , updated_by ,updated_dt)  values ( NEW.id , 'INSERT' , NEW.name , NEW.description , NEW.datasource , NEW.ticker , NEW.field , NEW.frequency , NEW.currency , NEW.lag , NEW.deleted , NEW.version , NEW.updated_by , New.update_dt);
END ;

Could anyone please shed some light? Where is that ‘’ coming from?

Thanks

To troubleshoot this kind of error, you can log into the MySQL console and try writing the query directly there. That could help you find the syntax that needs to be fixed.

This is the second time I post this question. My original post yesterday was detected by the robot as a potential spam and has been released by admin

This time I used the official end-delimiter sample from end-delimiter SQL attribute | Liquibase Docs as the template to build my file as below

I am using mysql 5.7 and liquibase is 4.10.0 . To be more specific it is aws aurora mysql

-- changeset your.name:101 end-delimiter:/

CREATE FUNCTION isEligible(
    age INTEGER
)
    RETURNS VARCHAR(20)
    DETERMINISTIC
BEGIN
IF age > 18 THEN
RETURN ("yes");
ELSE
RETURN ("No");
END IF;
END/

The function creation works well on mysql workbench. I was expecting it should work

However when it is deployed I am still getting the below exception. Apparently after the delimiter is specified whenever it meets semicolon the error was thrown.

There were a few similar questions posted earlier. Unfortunately I have seen a clear analysis on this issue and the issue raiser confirmed his/her issue was resolved.

Could anyone please shed me some light how to address this issue?

Thanks

Caused by: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set classpath:db/changelog/aurora-mysql/changelog.yml::2::N627756:
     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 10 [Failed SQL: (1064) -- changeset your.name:101 end-delimiter:/

CREATE FUNCTION isEligible(
    age INTEGER
)
    RETURNS VARCHAR(20)
    DETERMINISTIC
BEGIN
IF age > 18 THEN
RETURN ("yes")]
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:126)
        at liquibase.Liquibase.lambda$null$0(Liquibase.java:265)
        at liquibase.Scope.lambda$child$0(Scope.java:180)
        at liquibase.Scope.child(Scope.java:189)
        at liquibase.Scope.child(Scope.java:179)
        at liquibase.Scope.child(Scope.java:158)
        at liquibase.Scope.child(Scope.java:243)
        at liquibase.Liquibase.lambda$update$1(Liquibase.java:264)
        at liquibase.Scope.lambda$child$0(Scope.java:180)
        at liquibase.Scope.child(Scope.java:189)
        at liquibase.Scope.child(Scope.java:179)
        at liquibase.Scope.child(Scope.java:158)
        at liquibase.Liquibase.runInScope(Liquibase.java:2405)
        at liquibase.Liquibase.update(Liquibase.java:211)
        at liquibase.Liquibase.update(Liquibase.java:197)
        at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:314)
        at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:269)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800)
        ... 26 common frames omitted
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set classpath:db/changelog/aurora-mysql/changelog.yml::2::N627756:
     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 10 [Failed SQL: (1064) -- changeset your.name:101 end-delimiter:/

This error is very common, it’s true. Mostly that’s because we are only human, and people make mistakes in their SQL syntax often. This can be due to using reserved words, missing data in the database, or mistyped/obsolete commands.
InMotion Hosting has a great troubleshooting guide here:
MySQL 1064 Error: You have an error in your SQL syntax – InMotion Hosting Support Center.

Thanks tabbyfoo. The sql is correct. The second post merged below has a lot more details regarding how was it built , version etc. I think it is related to how does liquibase handle multiline sql on mysql. You should be able to reproduce it. I build a function intently without any dependencies to show it.

Hi @springland
Move the end delimiter to its own line.
move the / so that the code looks like this.

RETURN ("No");
END IF;
END
/

My assumption is that the first line in the file also includes
-- liquibase formatted sql

Hello @springland ,

I’m not sure if this will work, but from the documentation here end-delimeter it says the end-delimeter $$ should be escaped \$\$.

When setting an end-delimiter , note that certain DBMS and operating systems require delimiter values to be escaped. For example, a $ end-delimiter with mysql requires escaping as: end-delimiter="\$\$"

Could you please give this a try once by escaping the end-delimeter in your sql changeset. Please let us know if it helps.

Thanks,
Rakhi Agrawal

Thanks MikeOliva and Rakhi

@MikeOlivas

Changed it to below as suggested , the issue is still the same

– changeset your.name:101 end-delimiter:/

CREATE FUNCTION isEligible(
age INTEGER
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
IF age > 18 THEN
RETURN (“yes”);
ELSE
RETURN (“No”);
END IF;
END
/

Caused by: 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 10 [Failed SQL: (1064) – changeset your.name:101 end-delimiter:/

CREATE FUNCTION isEligible(
age INTEGER
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
IF age > 18 THEN
RETURN (“yes”)]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:397)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:83)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:151)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1279)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1261)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:661)
… 64 common frames omitted

@rakhi

Did the change with the below combinations ( totally 4 cases)

– changeset your.name:101 end-delimiter:"$$"
and
– changeset your.name:101 end-delimiter:$$

END
$$
and
END $$

The below is the one based on your suggestion

– changeset your.name:101 end-delimiter:"$$"

CREATE FUNCTION isEligible(
age INTEGER
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
IF age > 18 THEN
RETURN (“yes”);
ELSE
RETURN (“No”);
END IF;
END $$

@rakhi
Forgot to mention, the issue is still there

Hi @springland I copied the exact change that you gave into a file and have this as my testcase.

--liquibase formatted sql
-- changeset your.name:101 end-delimiter:/
CREATE FUNCTION isEligible(
age INTEGER
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
IF age > 18 THEN
RETURN ("yes");
ELSE
RETURN ("No");
END IF;
END
/

This worked for me.
Version of mysql that I tested this with was 5.7.33 and liquibase 4.10
Can you try with the above and let us know if it still fails for you?

Hi @MikeOlivas , Thanks for the help. I run it again with what you posted. Still face the issue.

There is one thing might be overlooked. I am using aws aurora mysql instead of mysql directly. Per AWS aurora mysql should be 100% compatible with mysql. The engine is developed based on mysql community version. Since you run it successfully without an issue but I keep running into issue I am suspecting if there is a difference between mysql and aurora mysql on this.

Let’s review the setting on my side again

Liquidbase version
Aurora Driver

software.aws.rds
aws-mysql-jdbc
1.0.0

liquibase

<dependency>
  <groupId>org.liquibase</groupId>
  <artifactId>liquibase-core</artifactId>
  <version>4.10.0</version>
</dependency>

Change log

  • changeSet:
    id: 3
    author: N627756
    dbms: mysql
    labels: gtaa
    changes:
    - sqlFile:
    path: ./test.sql
    relativeToChangelogFile: true

Do I need to change the dbms to something else for aurora mysql?

At this time I have some issue with IAM authentication with mysql-connector-java driver. Cannot test it to confirm it is AWS aurora driver issue.

I will setup new aurora mysql and mysql with password authentication. Then I switch the drivers to study more

Thanks for the update @springland . I am guessing that it is environment related as well.
I am using the 8.0.23 driver from mysql.
https://dev.mysql.com/downloads/connector/j/
I could also try with the latest 8.0.29 locally.
Setting up an Aurora system will take some time for me to test as I don’t have access to the aws-mysql-jdbc driver either.
I will use this to setup my connection.

Could be through the IAM role or the driver. Looks like there are multiple differences.

Hi @MikeOlivas,

I invetigated more on this. This is what has been done. All of them are using username/password mode

  1. setup AWS RDS MySQL 5.7
  2. setup AWS RDS MySQL 8.0.28
  3. setup Local MySQL 8.0.28

Driver is

	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>8.0.29</version>
	</dependency>

Run my test against each. All of them return me the same error as before

CREATE FUN’ at line 1 [Failed SQL: (1064) --liquibase formatted sql
– changeset your.name:101 end-delimiter:/

CREATE FUNCTION isEligible(
age INTEGER
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
IF age > 18 THEN
RETURN (“yes”)]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:397) ~[liquibase-core-4.9.1.jar:na]
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:83) ~[liquibase-core-4.9.1.jar:na]
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:151) ~[liquibase-core-4.9.1.jar:na]
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1279) ~[liquibase-core-4.9.1.jar:na]
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1261) ~[liquibase-core-4.9.1.jar:na]
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:661) ~[liquibase-core-4.9.1.jar:na]
… 56 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: 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 '–liquibase formatted sql
– changeset your.name:101 end-delimiter:/

Since you did run it sucssfully last time , I am wondering if it is the way I am using liquibase causing this issue.

I have spring boot app server. liquibase is called when the app server is deployed ( started ). How did you run it? Are you using liquibase command line?

Thanks

This run well from mysql workbench in each test

delimiter /
CREATE FUNCTION isEligible(
age INTEGER
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
IF age > 18 THEN
RETURN (“yes”);
ELSE
RETURN (“No”);
END IF;
END
/

Hi @MikeOlivas ,

I did some debug on this issue. What I found is when AbstractSQLChange.generateSQLStatements() was called the getEndDelimiter() returns null. That means end-delimiter:/ setting in the sql file is ignored. Then it runs into issue whenver it encounters semi-colon

In which case this can happen?

Thanks

Hi @springland
cc @rakhi @tabbyfoo
I have recreated the issue. It’s not environment. We are mixing types.
You are using a sqlFile type in your YAML changelog.
The parameters for that changeset are in the yml file.
If you remove the header --changeSet in the ./test.sql file
And add endDelimiter:/ to the yaml portion of the changeset it should work just fine.

The other inconsistencies were in the versions you supplied.
liquibase-core-4.9.1.jar in the error message but earlier stating using version 4.10.0.
I figured those were just typos.
Something like this should work

databaseChangeLog:
- changeSet:
    id: 1
    author: mikeo
    changes:
    - sqlFile:
        path: ./isEligible.sql
        relativeToChangelogFile: true
        endDelimiter: /

@MikeOlivas ,

Yeah , it is making sense now.

Can you please shed me some more light? I am wondering how does liquibase pick up the control setting in sqlFile like

– liquibase formatted sql
– changeset your.name:101 end-delimiter:/

Originally I thought those are control setting , when liquibase parse the sqlfile it matches pattern then pick up the information to populate the ChangeSet object. However I did not find the related code in SQLFileChange , And when I trace the code I saw those comment controls are passed as part of SQL statement to jdbc driver.

My question is how does liquibase check if a release has been deployed
Origianlly I thought if I had the below in my sql file , then this sql file belongs to release your.name:101
– changeset your.name:101

yoru.name.101 is recorded in databasechangelog , so we know if a change set has been released. However when I took a close look of databasechangelog it is using MD5. When you mentioned above that we have mixed types I was wondering how does liquibase work in this case.

Does liquibase generate changeset based on yml file and ingore the comments in sql file in this case? Is the MD5 calcualted based on all sql files in a change set? If a new sql file is added into an existing changset in the future will it cause MD5 change and redeoloy the changeset? The assumption is the sql file is idempotent.

One thing I guess I misunderstood before was this sentence under SQL Example

Currently, there is no SQL Example for the <sqlFile> Change Type because you can just put SQL directly into a SQL formatted changelog.

I thought it means there is no sql change log file and I need to leverage other types such as yaml , xml or json to include multiple sql files.

However based on above it seems what should be uses is something like below

spring.liquibase.change-log=classpath:db/changelog/db-changelog.sql

In this case the changelog setting etc will be picked up by liquibase. However it does not support mutiple change logs. Each time I should replace it with the lastest sql file for a new release.

Please let me know if I am wrong again :slight_smile:
Thanks

I think I got the answer.
The checksum is based on the content of database change. if anyone of the sql files was updated even with non material change the liquibase validation will fail

Although yaml format change log file allows multiple chnage sets with multiple sql files for each it only deploys the one has not been deployed and validate those have been deployed. sql changelog file just does not have a chance to validate pervious release. From this perspective there is no difference between sql or yaml changelog.