com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException in hibernate

Question

I am pretty new to hibernate and I am stuck at this exception. As far as my understanding is, this should not happen if my dialect is correct and I am quiet sure I am using the correct dialect i.e org.hibernate.dialect.MySQLDialect, still I tried with org.hibernate.dialect.MySQLInnoDBDialect too but it still gives the same exception. I am using spring 3.1.0 with hibernate 3 and mysql 5. The dao function that is causing this error is:

@SuppressWarnings("unchecked")
protected List<T> findByCriteria(Criterion... criterion) {

    Criteria crit = getSession().createCriteria(getPersistentClass())
            .addOrder(Order.asc(getDefaultProperty()));
    for (Criterion c : criterion) {
        crit.add(c);
    }
    List<T> critList=crit.list();
    return critList;
}

I have searched a lot, and have pretty much hit a wall as to possible problems. So any help would be greatly appreciated.

EDIT Full stacktrace

I have enabled logging and posting the part that I think has the problem

14:52:26,891 DEBUG HibernateTransactionManager:504 - Preparing JDBC Connection of Hibernate Session [org.hibernate.impl.SessionImpl@7889fe65]
14:52:26,901 DEBUG HibernateTransactionManager:569 - Exposing Hibernate transaction as JDBC transaction [jdbc:mysql://localhost:3306/cricket_calendar, UserName=root@localhost, MySQL-AB JDBC Driver]
14:52:26,901 DEBUG TransactionSynchronizationManager:193 - Bound value [org.springframework.jdbc.datasource.ConnectionHolder@7378aae2] for key [org.apache.commons.dbcp.BasicDataSource@753ef605] to thread [http-bio-8080-exec-4]
14:52:26,901 DEBUG TransactionSynchronizationManager:193 - Bound value [org.springframework.orm.hibernate3.SessionHolder@771b16a7] for key [org.hibernate.impl.SessionFactoryImpl@426abd0b] to thread [http-bio-8080-exec-4]
14:52:26,901 DEBUG TransactionSynchronizationManager:272 - Initializing transaction synchronization
14:52:26,902 DEBUG TransactionInterceptor:362 - Getting transaction for [com.emumba.cricketcalendar.dao.hibernate.GenericDAOHibernate.findAll]
14:52:26,902 DEBUG TransactionSynchronizationManager:140 - Retrieved value [org.springframework.orm.hibernate3.SessionHolder@771b16a7] for key [org.hibernate.impl.SessionFactoryImpl@426abd0b] bound to thread [http-bio-8080-exec-4]
Hibernate: select this_.id as id14_10_, this_.country1_id as country4_14_10_, this_.country2_id as country5_14_10_, this_.end_date as end2_14_10_, this_.ground_id as ground6_14_10_, this_.series_id as series7_14_10_, this_.start_date as start3_14_10_, this_.umpire1_id as umpire8_14_10_, this_.umpire2_id as umpire9_14_10_, this_.umpire3rd_id as umpire10_14_10_, this_.umpireReserve_id as umpireR11_14_10_, country2_.id as id17_0_, country2_.abbreviation as abbrevia2_17_0_, country2_.name as name17_0_, country2_.status_id as status4_17_0_, cricketsta3_.id as id18_1_, cricketsta3_.description as descript2_18_1_, cricketsta3_.display_name as display3_18_1_, country4_.id as id17_2_, country4_.abbreviation as abbrevia2_17_2_, country4_.name as name17_2_, country4_.status_id as status4_17_2_, ground5_.id as id15_3_, ground5_.country_id as country4_15_3_, ground5_.description as descript2_15_3_, ground5_.name as name15_3_, country6_.id as id17_4_, country6_.abbreviation as abbrevia2_17_4_, country6_.name as name17_4_, country6_.status_id as status4_17_4_, series7_.id as id19_5_, series7_.description as descript2_19_5_, series7_.end_date as end3_19_5_, series7_.name as name19_5_, series7_.start_date as start5_19_5_, umpire8_.id as id16_6_, umpire8_.name as name16_6_, umpire9_.id as id16_7_, umpire9_.name as name16_7_, umpire10_.id as id16_8_, umpire10_.name as name16_8_, umpire11_.id as id16_9_, umpire11_.name as name16_9_ from match this_ left outer join ref_country country2_ on this_.country1_id=country2_.id left outer join ref_cricket_status cricketsta3_ on country2_.status_id=cricketsta3_.id left outer join ref_country country4_ on this_.country2_id=country4_.id left outer join ref_ground ground5_ on this_.ground_id=ground5_.id left outer join ref_country country6_ on ground5_.country_id=country6_.id left outer join series series7_ on this_.series_id=series7_.id left outer join ref_umpire umpire8_ on this_.umpire1_id=umpire8_.id left outer join ref_umpire umpire9_ on this_.umpire2_id=umpire9_.id left outer join ref_umpire umpire10_ on this_.umpire3rd_id=umpire10_.id left outer join ref_umpire umpire11_ on this_.umpireReserve_id=umpire11_.id order by this_.id asc
14:52:26,927 DEBUG TransactionInterceptor:406 - Completing transaction for [com.emumba.cricketcalendar.dao.hibernate.GenericDAOHibernate.findAll] after exception: org.hibernate.exception.SQLGrammarException: could not execute query
14:52:26,928 DEBUG RuleBasedTransactionAttribute:130 - Applying rules to determine whether transaction should rollback on org.hibernate.exception.SQLGrammarException: could not execute query
14:52:26,928 DEBUG RuleBasedTransactionAttribute:147 - Winning rollback rule is: null
14:52:26,928 DEBUG RuleBasedTransactionAttribute:152 - No relevant rollback rule found: applying default rules
14:52:26,928 DEBUG HibernateTransactionManager:935 - Triggering beforeCompletion synchronization
14:52:26,928 DEBUG HibernateTransactionManager:843 - Initiating transaction rollback
14:52:26,928 DEBUG HibernateTransactionManager:672 - Rolling back Hibernate transaction on Session [org.hibernate.impl.SessionImpl@7889fe65]
14:52:26,929 DEBUG HibernateTransactionManager:964 - Triggering afterCompletion synchronization
14:52:26,930 DEBUG TransactionSynchronizationManager:331 - Clearing transaction synchronization
14:52:26,930 DEBUG TransactionSynchronizationManager:243 - Removed value [org.springframework.orm.hibernate3.SessionHolder@771b16a7] for key [org.hibernate.impl.SessionFactoryImpl@426abd0b] from thread [http-bio-8080-exec-4]
14:52:26,930 DEBUG TransactionSynchronizationManager:243 - Removed value [org.springframework.jdbc.datasource.ConnectionHolder@7378aae2] for key [org.apache.commons.dbcp.BasicDataSource@753ef605] from thread [http-bio-8080-exec-4]
14:52:26,931 DEBUG HibernateTransactionManager:734 - Closing Hibernate Session [org.hibernate.impl.SessionImpl@7889fe65] after transaction
14:52:26,931 DEBUG SessionFactoryUtils:793 - Closing Hibernate Session
14:52:26,934 DEBUG ExceptionHandlerExceptionResolver:132 - Resolving exception from handler [public org.springframework.web.servlet.ModelAndView com.emumba.cricketcalendar.web.CalendarController.home()]: org.hibernate.exception.SQLGrammarException: could not execute query
14:52:26,935 DEBUG ResponseStatusExceptionResolver:132 - Resolving exception from handler [public org.springframework.web.servlet.ModelAndView com.emumba.cricketcalendar.web.CalendarController.home()]: org.hibernate.exception.SQLGrammarException: could not execute query
14:52:26,935 DEBUG DefaultHandlerExceptionResolver:132 - Resolving exception from handler [public org.springframework.web.servlet.ModelAndView com.emumba.cricketcalendar.web.CalendarController.home()]: org.hibernate.exception.SQLGrammarException: could not execute query
14:52:26,936 DEBUG DispatcherServlet:905 - Cleared thread-bound request context: org.apache.catalina.connector.RequestFacade@3569be56
14:52:26,937 DEBUG DispatcherServlet:910 - Could not complete request
org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2545)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
    at com.emumba.cricketcalendar.dao.hibernate.GenericDAOHibernate.findByCriteria(GenericDAOHibernate.java:114)
    at com.emumba.cricketcalendar.dao.hibernate.GenericDAOHibernate.findAll(GenericDAOHibernate.java:75)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy26.findAll(Unknown Source)
    at com.emumba.cricketcalendar.service.CalendarManagerImpl.getAllMatches(CalendarManagerImpl.java:25)
    at com.emumba.cricketcalendar.web.CalendarController.home(CalendarController.java:25)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:212)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:300)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: 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 'match this_ left outer join ref_country country2_ on this_.country1_id=country2_' at line 1
    at sun.reflect.GeneratedConstructorAccessor12.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2542)
    ... 56 more
14:52:26,941 DEBUG XmlWebApplicationContext:322 - Publishing event in WebApplicationContext for namespace 'appServlet-servlet': ServletRequestHandledEvent: url=[/cricket_calendar/calendar.do]; client=[0:0:0:0:0:0:0:1]; method=[GET]; servlet=[appServlet]; session=[null]; user=[null]; time=[60ms]; status=[failed: org.hibernate.exception.SQLGrammarException: could not execute query]
14:52:26,941 DEBUG XmlWebApplicationContext:322 - Publishing event in Root WebApplicationContext: ServletRequestHandledEvent: url=[/cricket_calendar/calendar.do]; client=[0:0:0:0:0:0:0:1]; method=[GET]; servlet=[appServlet]; session=[null]; user=[null]; time=[60ms]; status=[failed: org.hibernate.exception.SQLGrammarException: could not execute query]
14:52:26,942 DEBUG TransactionSynchronizationManager:243 - Removed value [org.springframework.orm.hibernate3.SessionHolder@611cfa21] for key [org.hibernate.impl.SessionFactoryImpl@5df86e79] from thread [http-bio-8080-exec-4]
14:52:26,942 DEBUG OpenSessionInViewFilter:207 - Closing single Hibernate Session in OpenSessionInViewFilter
14:52:26,942 DEBUG SessionFactoryUtils:793 - Closing Hibernate Session

Answer 1

First of all, your Dialect is wrong. For MySQL 5, you need to use org.hibernate.dialect.MySQL5Dialect. MySQL 5 InnoDB dialect is org.hibernate.dialect.MySQL5InnoDBDialect.

---

This exception clearly tells you the cause of your error:

Caused by: 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 'match this_ left outer join ref_country country2_ on this_.country1_id=country2_' at line 1

Your Hibernate SQL:

select this_.id as id14_10_, this_.country1_id as country4_14_10_, this_.country2_id as country5_14_10_, this_.end_date as end2_14_10_, this_.ground_id as ground6_14_10_, this_.series_id as series7_14_10_, this_.start_date as start3_14_10_, this_.umpire1_id as umpire8_14_10_, this_.umpire2_id as umpire9_14_10_, this_.umpire3rd_id as umpire10_14_10_, this_.umpireReserve_id as umpireR11_14_10_, country2_.id as id17_0_, country2_.abbreviation as abbrevia2_17_0_, country2_.name as name17_0_, country2_.status_id as status4_17_0_, cricketsta3_.id as id18_1_, cricketsta3_.description as descript2_18_1_, cricketsta3_.display_name as display3_18_1_, country4_.id as id17_2_, country4_.abbreviation as abbrevia2_17_2_, country4_.name as name17_2_, country4_.status_id as status4_17_2_, ground5_.id as id15_3_, ground5_.country_id as country4_15_3_, ground5_.description as descript2_15_3_, ground5_.name as name15_3_, country6_.id as id17_4_, country6_.abbreviation as abbrevia2_17_4_, country6_.name as name17_4_, country6_.status_id as status4_17_4_, series7_.id as id19_5_, series7_.description as descript2_19_5_, series7_.end_date as end3_19_5_, series7_.name as name19_5_, series7_.start_date as start5_19_5_, umpire8_.id as id16_6_, umpire8_.name as name16_6_, umpire9_.id as id16_7_, umpire9_.name as name16_7_, umpire10_.id as id16_8_, umpire10_.name as name16_8_, umpire11_.id as id16_9_, umpire11_.name as name16_9_ from match this_ left outer join ref_country country2_ on this_.country1_id=country2_.id left outer join ref_cricket_status cricketsta3_ on country2_.status_id=cricketsta3_.id left outer join ref_country country4_ on this_.country2_id=country4_.id left outer join ref_ground ground5_ on this_.ground_id=ground5_.id left outer join ref_country country6_ on ground5_.country_id=country6_.id left outer join series series7_ on this_.series_id=series7_.id left outer join ref_umpire umpire8_ on this_.umpire1_id=umpire8_.id left outer join ref_umpire umpire9_ on this_.umpire2_id=umpire9_.id left outer join ref_umpire umpire10_ on this_.umpire3rd_id=umpire10_.id left outer join ref_umpire umpire11_ on this_.umpireReserve_id=umpire11_.id order by this_.id asc

Match is a reserved word in MySQL 5. My suggestion is to rename your entity to another name.