Quartz on H2 with MODE=MYSQL

We are using embedded H2 database for local developement, but application, when deployed, uses MySQL. H2 database has cool compatibility mode feature allowing it to mimic another RDBMS so you can use it's non standard SQL statements. Level of compatibility is not 100% of course.

While Hibernate runs nicely on H2 with MySQLInnoDBDialect configured, trouble starts when you'll try run Quartz Scheduler (2.2.1) on H2 even in MYSQL mode. You'll face exceptions like...

org.quartz.JobPersistenceException: Couldn't store job: Value too long for column "IS_DURABLE VARCHAR(1) NOT NULL": "'TRUE' (4)"; SQL statement:
INSERT INTO QRTZ_JOB_DETAILS (SCHED_NAME, JOB_NAME, JOB_GROUP, DESCRIPTION, JOB_CLASS_NAME, IS_DURABLE, IS_NONCONCURRENT, IS_UPDATE_DATA, REQUESTS_RECOVERY, JOB_DATA)  VALUES('booster-scheduler', ?, ?, ?, ?, ?, ?, ?, ?, ?) [22001-175]
 at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeJob(JobStoreSupport.java:1118) ~[quartz-2.2.1.jar:na]
 at org.quartz.impl.jdbcjobstore.JobStoreSupport$3.executeVoid(JobStoreSupport.java:1090) ~[quartz-2.2.1.jar:na]
 at org.quartz.impl.jdbcjobstore.JobStoreSupport$VoidTransactionCallback.execute(JobStoreSupport.java:3703) ~[quartz-2.2.1.jar:na]
 at org.quartz.impl.jdbcjobstore.JobStoreSupport$VoidTransactionCallback.execute(JobStoreSupport.java:3701) ~[quartz-2.2.1.jar:na]
 at org.quartz.impl.jdbcjobstore.JobStoreCMT.executeInLock(JobStoreCMT.java:245) ~[quartz-2.2.1.jar:na]
 at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeJob(JobStoreSupport.java:1086) ~[quartz-2.2.1.jar:na]
 at org.quartz.core.QuartzScheduler.addJob(QuartzScheduler.java:969) ~[quartz-2.2.1.jar:na]
 at org.quartz.core.QuartzScheduler.addJob(QuartzScheduler.java:958) ~[quartz-2.2.1.jar:na]
 at org.quartz.impl.StdScheduler.addJob(StdScheduler.java:268) ~[quartz-2.2.1.jar:na]
 at org.springframework.scheduling.quartz.SchedulerAccessor.addJobToScheduler(SchedulerAccessor.java:342) ~[spring-context-support-4.0.1.RELEASE.jar:4.0.1.RELEASE]
 at org.springframework.scheduling.quartz.SchedulerAccessor.addTriggerToScheduler(SchedulerAccessor.java:365) ~[spring-context-support-4.0.1.RELEASE.jar:4.0.1.RELEASE]
 at org.springframework.scheduling.quartz.SchedulerAccessor.registerJobsAndTriggers(SchedulerAccessor.java:303) ~[spring-context-support-4.0.1.RELEASE.jar:4.0.1.RELEASE]
 at org.springframework.scheduling.quartz.SchedulerFactoryBean.afterPropertiesSet(SchedulerFactoryBean.java:514) ~[spring-context-support-4.0.1.RELEASE.jar:4.0.1.RELEASE]
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1612) ~[spring-beans-4.0.1.RELEASE.jar:4.0.1.RELEASE]
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1549) ~[spring-beans-4.0.1.RELEASE.jar:4.0.1.RELEASE]
 ... 56 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLException: Value too long for column "IS_DURABLE VARCHAR(1) NOT NULL": "'TRUE' (4)"; SQL statement:
INSERT INTO QRTZ_JOB_DETAILS (SCHED_NAME, JOB_NAME, JOB_GROUP, DESCRIPTION, JOB_CLASS_NAME, IS_DURABLE, IS_NONCONCURRENT, IS_UPDATE_DATA, REQUESTS_RECOVERY, JOB_DATA)  VALUES('booster-scheduler', ?, ?, ?, ?, ?, ?, ?, ?, ?) [22001-175]
 at org.h2.engine.SessionRemote.done(SessionRemote.java:589) ~[h2-1.3.175.jar:1.3.175]
 at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:186) ~[h2-1.3.175.jar:1.3.175]
 at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:154) ~[h2-1.3.175.jar:1.3.175]
 at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:140) ~[h2-1.3.175.jar:1.3.175]
 at com.jolbox.bonecp.PreparedStatementHandle.executeUpdate(PreparedStatementHandle.java:205) ~[bonecp-0.8.0.RELEASE.jar:na]
 at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.insertJobDetail(StdJDBCDelegate.java:624) ~[quartz-2.2.1.jar:na]
 at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeJob(JobStoreSupport.java:1112) ~[quartz-2.2.1.jar:na]
 ... 70 common frames omitted

Reason behing this is that current H2 version (1.3.155) is unable to perform automatic conversion from boolean into VARCHAR(1) like MySQL can do. There is recent discussion about implementing this, but until released, you can make it work simply by changing VARCHAR(1) into BOOLEAN inside Quartz schema tables_mysql_innodb.sql.

Following gist shows result

We can only guess why Quartz guys are sticking with VARCHAR(1) when BOOLEAN makes much more sense.

Happy scheduling