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

Find email by attachment type in Gmail

Recently, I needed to find a spreadsheet in Gmail from one particular person who I had 100s of emails with.  The advanced search allows you to filter emails that have attachment but that is it.  Fortunately, Gmail has different search keywords that the advanced filter GUI does not have listed.  To find a spreadsheet in gmail from bob@gmail.com you would search for:

filename:xls from:bob@gmail.com

Gmail has a listing of other helpful advanced search techniques found here http://support.google.com/mail/bin/answer.py?hl=en&answer=7190

Allowing remote users on MySQL

Recently I needed to add a user account that could access a MySQL database remotely. Listed below are the steps to create a user named bob who only has read only access.

Create the user account for bob if it doesn't already exist:

create user bob; 

Set the password test123* to bob's account:

set password for bob = password('test123*');

Grant privileges to bob to run the select statement from his home computer with the IP 210.22.2.123 for all tables on the schedule database:

grant select on schedule.* for 'bob'@'210.22.2.123' with grant option; 

The with grant option is required for remote access. This item tripped me up for several minutes.

Additionally, the mysqldump command issues the lock table command which is not granted with the above command.  Lock tables prevents rows from inserted or altered.  The --lock-tables=false flag prevents the lock tables command from being issue.

For further documentation check out the MySQL documentation on user accounts at http://dev.mysql.com/doc/refman/5.5/en//adding-users.html