ScriptUtils fails to split statements of a SQL script [SPR-16927]

[spring-projects-issues]

Steffen Harbich opened SPR-16927 and commented

Consider the following script (quartz tables_mysql_innodb.sql):

#
# In your Quartz properties file, you'll need to set
# org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
#
#
# By: Ron Cordell - roncordell
# I didn't see this anywhere, so I thought I'd post it here. This is the script from Quartz to create the tables in a MySQL database, modified to use INNODB instead of MYISAM.

DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;
...

It is executed using QuartzAutoConfiguration's QuartzDataSourceInitializer for mysql. The ScriptUtils class will finally parse this script as 2 statements in line 464 (https://github.com/spring-projects/spring-framework/blob/master/spring-jdbc/src/main/java/org/springframework/jdbc/datasource/init/ScriptUtils.java#L464) which is wrong I think. Executing those statements results in a syntax exception because only one statement can be executed at a time:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS; DROP TABLE IF EXISTS QRTZ_SCHEDUL' at line 9

If I remove the comments from the script then everything works fine as expected. I suspect that parsing of apostrophe in comment doesn't work as intended.

---

Affects: 5.0.6

Reference URL: https://stackoverflow.com/questions/50793308

[spring-projects-issues]

Juergen Hoeller commented

As per spring-projects/spring-boot#13041, isn't this primarily a question of which comment prefix is configured here? Our default is "--"; this would have to be explicitly set to "#" for the above comments to be properly ignored.

[spring-projects-issues]

Steffen Harbich commented

Looks like the same problem. But - considering the various SQL scripts of the quartz library - they use different comment styles. E.g. derby has '–' whereas mysql has '#'. Now if I would change the comment prefix to one of them, the other isn't working, right? So I couldn't make it work for all database quartz supports?

[sbrannen]

So I couldn't make it work for all database quartz supports?

Every time you ask Spring to execute an SQL script for you, you have to inform Spring about the syntax in use if it deviates from the default syntax configuration in Spring.

Thus you will need to use different Spring configuration for each database and syntax settings you wish to use. Spring does not automatically detect the syntax supported for the underlying database.

In light of that, I am closing this issue.

[buckett]

I ran into this issue when I was switching from H2 to MySQL with a JDBC Quartz scheduler in a small Spring Boot application and the simple config fix for me was to add:

spring.quartz.jdbc.comment-prefix=#

to the spring application.properties.