CODEWITHSUNDEEP
javamysqlspringspring-bootquartz-scheduler
Steffen Harbich
Steffen Harbich

Reputation: 2759

Automatic schema creation for quartz and mysql fails with MySQLSyntaxException

I have a Spring Boot 2 application with a mysql database running and wanted to add the Quartz scheduler. Configuration:

spring.quartz.job-store-type=jdbc
spring.quartz.jdbc.initialize-schema=always

spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false

But on start-up it doesn't create the database tables and fails when accessing the QRTZ_* tables. I debugged to get the cause which I found in ScriptUtils class called by Spring to execute the quartz script tables_mysql_innodv.sql. The exception is suppressed intentionally:

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

I cannot see any syntax error and when I execute the same script in MySQL Workbench directly then it runs fine, i.e. creates the tables etc.

The source of the executed start-up script is available here. Exception is encountered in this line. There is one observation that makes me confused: The code in the same method tries to split the SQL statements but only 2 statements are extracted - the first line # and the remaining script. Maybe this is the actual problem.

So, how can I fix this?

Version info:

Upvotes: 3

Views: 9536

Answers (4)

Kepler Hu
Kepler Hu

Reputation: 1

the method 'ScriptUtils.containsSqlScriptDelimiters' can not resolve the scripts properly. because the mysql scripts's comments contain some single quotes. you can replace the single quotes with blank.

Upvotes: -1

TexAmicoMio
TexAmicoMio

Reputation: 41

In YAML configuration properties file, as suggest by Mouad EL Fakir I added:

   quartz:
      job-store-type: jdbc
      jdbc:
         initialize-schema: always
         comment-prefix: '#'

Upvotes: 4

Mouad EL Fakir
Mouad EL Fakir

Reputation: 3759

Add the following property to your configuration, to specify the comment character :

spring.quartz.jdbc.comment-prefix=#

Upvotes: 8

Steffen Harbich
Steffen Harbich

Reputation: 2759

There is a workaround:

  1. Copy the script file tables_mysql_innodb.sql to your resources folder main/resources/org/quartz/impl/jdbcjobstore/tables_mysql_innodb.sql. This will be picked up by spring boot instead of the original file.
  2. Remove all comment lines at the start of the file. These are the problematic lines for parsing in Spring's ScriptUtils class.

Upvotes: 3

Related Questions