Grails Quartz tables are not found on new database

I ran an existing Grails project on a new database and encountered the below error:

ERROR context.GrailsContextLoader  -

Error executing bootstraps:
Error creating bean with name 'quartzScheduler':
Invocation of init method failed; nested exception is org.quartz.SchedulerConfigException:

Failure occured during job recovery.
[See nested exception: org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: Table 'schema.qrtz_locks' doesn't exist
[See nested exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'schema.qrtz_locks' doesn't exist]]

A Jira has already been raised for this issue [1].

The underlying reason is that Grails does not handle the order of plugin execution.  One particular resolution is to use the database-migration plugin [2].  A tutorial can be found here [3].  If the plugin is installed and you have created a schema, you can use the dbm-update command [4].

After you migrate the tables over with no data, upon running the application you will be encountered the below error:


ERROR context.GrailsContextLoader  -
Error executing bootstraps:
Error creating bean with name 'quartzScheduler':
Invocation of init method failed; nested exception is org.quartz.SchedulerConfigException: Failure occured during job recovery.
[See nested exception: org.quartz.impl.jdbcjobstore.LockException:
Failure obtaining db row lock: No row exists in table QRTZ_LOCKS for lock named: TRIGGER_ACCESS
[See nested exception: java.sql.SQLException: No row exists in table QRTZ_LOCKS for lock named: TRIGGER_ACCESS]]

To solve that problem, you need to insert the following rows into the qrtz_locks table:

INSERT INTO qrtz_locks values('TRIGGER_ACCESS'); INSERT INTO qrtz_locks values('JOB_ACCESS'); INSERT INTO qrtz_locks values('CALENDAR_ACCESS'); INSERT INTO qrtz_locks values('STATE_ACCESS'); INSERT INTO qrtz_locks values('MISFIRE_ACCESS');

[1] http://jira.grails.org/browse/GPDATABASEMIGRATION-58?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
[2] http://grails.org/plugin/database-migration
[3] http://wpgreenway.com/posts/grails-db-migration-tutorial/
[4] http://grails-plugins.github.com/grails-database-migration/docs/manual/ref/Update%20Scripts/dbm-update.html