[Grails]Exception occurs when using the grails amazon s3 plugin with mysql database server

References

• Grails Amazon s3 plugin
  http://docs.codehaus.org/display/GRAILS/Amazon+S3+Plugin
• Mysql Reserved Words
  http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html
  

Description

• Following exception occurs when using the grails amazon s3 plugin with mysql database server.
  

  [19016] core.JobRunShell Job GRAILS_JOBS.S3SyncNewJob threw an unhandled Exception:
  org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'execute' on target class [class S3SyncNewJob] failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
         at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:269)
         at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
         at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
         at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529)
  Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
         at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:630)
         at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
         ......
  Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
         at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
         at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
         at org.hibernate.loader.Loader.doList(Loader.java:2216)
         at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
         at org.hibernate.loader.Loader.list(Loader.java:2099)
         at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
         at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
         at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
         at org.codehaus.groovy.grails.orm.hibernate.metaclass.FindAllByPersistentMethod$1.doInHibernate(FindAllByPersistentMethod.java:78)
         at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
         ... 83 more
  Caused by: java.sql.SQLException: Table 'test.s3asset' doesn't exist
         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
         at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
         at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
         at com.mysql.jdbc.Connection.execSQL(Connection.java:2994)
         at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936)
         at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030)
         at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
         at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
         at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
         at org.hibernate.loader.Loader.doQuery(Loader.java:674)
         at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
         at org.hibernate.loader.Loader.doList(Loader.java:2213)
         ... 90 more
  

Cause

• This error occurs because the column name in Domain class and Service classes of Amazon s3 plugin is including the Mysql's reserved keyword. After installing the amazon s3 plugin, the file named "S3Asset.groovy" stored under the plugins/amazon-s3-0.1/grails-app/domain/ directory is including the "key" keyword, which keyword is a reserved word of mysql database. Grails will try to create a new table named "s3_asset" into the mysql database, "create table s3_asset ...." statement is errored out because of the syntax error,
  and then System executes the select statement fo s3_asset, grails errors out because the syntax error.

Resolution

• Change the column name named "key" defined in domain class and service classes.
  I put the sample fixed source code, access to the http://groups.google.com/group/taapps-sourcecode-libraries and download the file grails-amazon-s3-0.1.zip.
  (This fixed source code is also changed the dependency version of the quartz plugin from 0.2 to 0.3.1.)