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 'limit 5 10 this_.id as id7_6_, this_.avorte_id as avorte11_7_6_, this_.confident' at line 1

Hibernate JIRA | Kevin DIMEY | 8 years ago
  1. 0

    Hi, When trying to limit the number of rows returned by Criteria.list() method, exception is thrown about bad SQL grammar. The "limit" SQL statement is put just after the select keyword. It should be placed at the end of the request. Moreover, it seems there is no coma between the start row and the amount of objects to retrieve. Here is the method called : session.createCriteria(Etude.class).setFirstResult(5).setMaxResults(10).list(); I get the same issue while using only setMaxResults, it puts the "top" keyword just after the "select" one. Here is the request generated and the stack trace : Hibernate: select limit ? ? this_.id as id7_6_, this_.avorte_id as avorte11_7_6_, this_.confidentielle as confiden2_7_6_, this_.contact_id as contact12_7_6_, this_.created_at as created3_7_6_, this_.date_fin as date4_7_6_, this_.date_signature as date5_7_6_, this_.date_sollicitation as date6_7_6_, this_.entreprise_id as entreprise13_7_6_, this_.etat_code as etat7_7_6_, this_.numero as numero7_6_, this_.sujet as sujet7_6_, this_.updated_at as updated10_7_6_, avortement2_.id as id1_0_, avortement2_.created_at as created2_1_0_, avortement2_.created_by as created3_1_0_, avortement2_.raison as raison1_0_, contact3_.id as id2_1_, contact3_.adresse_id as adresse18_2_1_, contact3_.civilite_code as civilite2_2_1_, contact3_.connu_par_code as connu3_2_1_, contact3_.connu_par_detail as connu4_2_1_, contact3_.created_at as created5_2_1_, contact3_.entreprise_id as entreprise19_2_1_, contact3_.fax as fax2_1_, contact3_.fonction as fonction2_1_, contact3_.mail as mail2_1_, contact3_.news_dernier_envoi as news9_2_1_, contact3_.news_desinscrit as news10_2_1_, contact3_.nom as nom2_1_, contact3_.parti as parti2_1_, contact3_.prenom as prenom2_1_, contact3_.service_code as service14_2_1_, contact3_.tel_fixe as tel15_2_1_, contact3_.tel_portable as tel16_2_1_, contact3_.updated_at as updated17_2_1_, adresse4_.id as id0_2_, adresse4_.adresse1 as adresse2_0_2_, adresse4_.adresse2 as adresse3_0_2_, adresse4_.adresse3 as adresse4_0_2_, adresse4_.code_postal as code5_0_2_, adresse4_.ville as ville0_2_, entreprise5_.id as id5_3_, entreprise5_.adresse_id as adresse14_5_3_, entreprise5_.created_at as created2_5_3_, entreprise5_.disparue as disparue5_3_, entreprise5_.domaine_code as domaine4_5_3_, entreprise5_.effectif_code as effectif5_5_3_, entreprise5_.etat_code as etat6_5_3_, entreprise5_.fax as fax5_3_, entreprise5_.nom as nom5_3_, entreprise5_.num_siret as num9_5_3_, entreprise5_.particulier as particu10_5_3_, entreprise5_.site_web as site11_5_3_, entreprise5_.tel as tel5_3_, entreprise5_.updated_at as updated13_5_3_, adresse6_.id as id0_4_, adresse6_.adresse1 as adresse2_0_4_, adresse6_.adresse2 as adresse3_0_4_, adresse6_.adresse3 as adresse4_0_4_, adresse6_.code_postal as code5_0_4_, adresse6_.ville as ville0_4_, entreprise7_.id as id5_5_, entreprise7_.adresse_id as adresse14_5_5_, entreprise7_.created_at as created2_5_5_, entreprise7_.disparue as disparue5_5_, entreprise7_.domaine_code as domaine4_5_5_, entreprise7_.effectif_code as effectif5_5_5_, entreprise7_.etat_code as etat6_5_5_, entreprise7_.fax as fax5_5_, entreprise7_.nom as nom5_5_, entreprise7_.num_siret as num9_5_5_, entreprise7_.particulier as particu10_5_5_, entreprise7_.site_web as site11_5_5_, entreprise7_.tel as tel5_5_, entreprise7_.updated_at as updated13_5_5_ from etude this_ left outer join avortement avortement2_ on this_.avorte_id=avortement2_.id left outer join contact contact3_ on this_.contact_id=contact3_.id left outer join adresse adresse4_ on contact3_.adresse_id=adresse4_.id left outer join entreprise entreprise5_ on contact3_.entreprise_id=entreprise5_.id left outer join adresse adresse6_ on entreprise5_.adresse_id=adresse6_.id left outer join entreprise entreprise7_ on this_.entreprise_id=entreprise7_.id 29 mars 2009 00:52:29 org.hibernate.util.JDBCExceptionReporter logExceptions ATTENTION: SQL Error: 1064, SQLState: 42000 29 mars 2009 00:52:29 org.hibernate.util.JDBCExceptionReporter logExceptions GRAVE: 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 'limit 5 10 this_.id as id7_6_, this_.avorte_id as avorte11_7_6_, this_.confident' at line 1 29 mars 2009 00:52:29 org.apache.catalina.core.ApplicationDispatcher invoke GRAVE: "Servlet.service()" pour la servlet jsp a lanc� une exception 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 'limit 5 10 this_.id as id7_6_, this_.avorte_id as avorte11_7_6_, this_.confident' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.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:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1912) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1808) at org.hibernate.loader.Loader.doQuery(Loader.java:697) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) at org.hibernate.loader.Loader.doList(Loader.java:2228) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125) at org.hibernate.loader.Loader.list(Loader.java:2120) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306) at easytic.support.EtudesSupport$EtudesList.getList(EtudesSupport.java:41) I've attached my hibernate.cfg.xml. This issue seemed resolved in 3.2.5 : http://opensource.atlassian.com/projects/hibernate/browse/HHH-2954 but I got it with 3.3.1

    Hibernate JIRA | 8 years ago | Kevin DIMEY
    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 'limit 5 10 this_.id as id7_6_, this_.avorte_id as avorte11_7_6_, this_.confident' at line 1
  2. 0

    Hi, When trying to limit the number of rows returned by Criteria.list() method, exception is thrown about bad SQL grammar. The "limit" SQL statement is put just after the select keyword. It should be placed at the end of the request. Moreover, it seems there is no coma between the start row and the amount of objects to retrieve. Here is the method called : session.createCriteria(Etude.class).setFirstResult(5).setMaxResults(10).list(); I get the same issue while using only setMaxResults, it puts the "top" keyword just after the "select" one. Here is the request generated and the stack trace : Hibernate: select limit ? ? this_.id as id7_6_, this_.avorte_id as avorte11_7_6_, this_.confidentielle as confiden2_7_6_, this_.contact_id as contact12_7_6_, this_.created_at as created3_7_6_, this_.date_fin as date4_7_6_, this_.date_signature as date5_7_6_, this_.date_sollicitation as date6_7_6_, this_.entreprise_id as entreprise13_7_6_, this_.etat_code as etat7_7_6_, this_.numero as numero7_6_, this_.sujet as sujet7_6_, this_.updated_at as updated10_7_6_, avortement2_.id as id1_0_, avortement2_.created_at as created2_1_0_, avortement2_.created_by as created3_1_0_, avortement2_.raison as raison1_0_, contact3_.id as id2_1_, contact3_.adresse_id as adresse18_2_1_, contact3_.civilite_code as civilite2_2_1_, contact3_.connu_par_code as connu3_2_1_, contact3_.connu_par_detail as connu4_2_1_, contact3_.created_at as created5_2_1_, contact3_.entreprise_id as entreprise19_2_1_, contact3_.fax as fax2_1_, contact3_.fonction as fonction2_1_, contact3_.mail as mail2_1_, contact3_.news_dernier_envoi as news9_2_1_, contact3_.news_desinscrit as news10_2_1_, contact3_.nom as nom2_1_, contact3_.parti as parti2_1_, contact3_.prenom as prenom2_1_, contact3_.service_code as service14_2_1_, contact3_.tel_fixe as tel15_2_1_, contact3_.tel_portable as tel16_2_1_, contact3_.updated_at as updated17_2_1_, adresse4_.id as id0_2_, adresse4_.adresse1 as adresse2_0_2_, adresse4_.adresse2 as adresse3_0_2_, adresse4_.adresse3 as adresse4_0_2_, adresse4_.code_postal as code5_0_2_, adresse4_.ville as ville0_2_, entreprise5_.id as id5_3_, entreprise5_.adresse_id as adresse14_5_3_, entreprise5_.created_at as created2_5_3_, entreprise5_.disparue as disparue5_3_, entreprise5_.domaine_code as domaine4_5_3_, entreprise5_.effectif_code as effectif5_5_3_, entreprise5_.etat_code as etat6_5_3_, entreprise5_.fax as fax5_3_, entreprise5_.nom as nom5_3_, entreprise5_.num_siret as num9_5_3_, entreprise5_.particulier as particu10_5_3_, entreprise5_.site_web as site11_5_3_, entreprise5_.tel as tel5_3_, entreprise5_.updated_at as updated13_5_3_, adresse6_.id as id0_4_, adresse6_.adresse1 as adresse2_0_4_, adresse6_.adresse2 as adresse3_0_4_, adresse6_.adresse3 as adresse4_0_4_, adresse6_.code_postal as code5_0_4_, adresse6_.ville as ville0_4_, entreprise7_.id as id5_5_, entreprise7_.adresse_id as adresse14_5_5_, entreprise7_.created_at as created2_5_5_, entreprise7_.disparue as disparue5_5_, entreprise7_.domaine_code as domaine4_5_5_, entreprise7_.effectif_code as effectif5_5_5_, entreprise7_.etat_code as etat6_5_5_, entreprise7_.fax as fax5_5_, entreprise7_.nom as nom5_5_, entreprise7_.num_siret as num9_5_5_, entreprise7_.particulier as particu10_5_5_, entreprise7_.site_web as site11_5_5_, entreprise7_.tel as tel5_5_, entreprise7_.updated_at as updated13_5_5_ from etude this_ left outer join avortement avortement2_ on this_.avorte_id=avortement2_.id left outer join contact contact3_ on this_.contact_id=contact3_.id left outer join adresse adresse4_ on contact3_.adresse_id=adresse4_.id left outer join entreprise entreprise5_ on contact3_.entreprise_id=entreprise5_.id left outer join adresse adresse6_ on entreprise5_.adresse_id=adresse6_.id left outer join entreprise entreprise7_ on this_.entreprise_id=entreprise7_.id 29 mars 2009 00:52:29 org.hibernate.util.JDBCExceptionReporter logExceptions ATTENTION: SQL Error: 1064, SQLState: 42000 29 mars 2009 00:52:29 org.hibernate.util.JDBCExceptionReporter logExceptions GRAVE: 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 'limit 5 10 this_.id as id7_6_, this_.avorte_id as avorte11_7_6_, this_.confident' at line 1 29 mars 2009 00:52:29 org.apache.catalina.core.ApplicationDispatcher invoke GRAVE: "Servlet.service()" pour la servlet jsp a lanc� une exception 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 'limit 5 10 this_.id as id7_6_, this_.avorte_id as avorte11_7_6_, this_.confident' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.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:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1912) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1808) at org.hibernate.loader.Loader.doQuery(Loader.java:697) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) at org.hibernate.loader.Loader.doList(Loader.java:2228) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125) at org.hibernate.loader.Loader.list(Loader.java:2120) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306) at easytic.support.EtudesSupport$EtudesList.getList(EtudesSupport.java:41) I've attached my hibernate.cfg.xml. This issue seemed resolved in 3.2.5 : http://opensource.atlassian.com/projects/hibernate/browse/HHH-2954 but I got it with 3.3.1

    Hibernate JIRA | 8 years ago | Kevin DIMEY
    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 'limit 5 10 this_.id as id7_6_, this_.avorte_id as avorte11_7_6_, this_.confident' at line 1
  3. 0

    HQL and Hibernate Criteria with Having and GROUP BY clause

    Stack Overflow | 3 years ago | user3336130
    org.hibernate.exception.SQLGrammarException: could not execute query
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    GitHub comment 12#161541759

    GitHub | 1 year ago | GoogleCodeExporter
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
  6. 0

    Spring 4.3.0.RELEASE + Hibernate 5.2.0.Final - GeneratedValue on mysql

    Stack Overflow | 5 months ago | Guyb
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'searcherdb.hibernate_sequence' doesn't exist

  1. Kialandei 1 times, last 2 weeks ago
  2. davidvanlaatum 8 times, last 2 months ago
  3. AndrewProg 2 times, last 3 months ago
  4. kuldeep 2 times, last 3 months ago
  5. tvrmsmith 1 times, last 4 months ago
27 more registered users
64 unregistered visitors
Not finding the right solution?
Take a tour to get the most out of Samebug.

Tired of useless tips?

Automated exception search integrated into your IDE

Root Cause Analysis

  1. 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 'limit 5 10 this_.id as id7_6_, this_.avorte_id as avorte11_7_6_, this_.confident' at line 1

    at sun.reflect.NativeConstructorAccessorImpl.newInstance0()
  2. Java RT
    Constructor.newInstance
    1. sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    2. sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    3. sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    4. java.lang.reflect.Constructor.newInstance(Unknown Source)
    4 frames
  3. MySQL jdbc
    PreparedStatement.executeQuery
    1. com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    2. com.mysql.jdbc.Util.getInstance(Util.java:381)
    3. com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    4. com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    5. com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
    6. com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
    7. com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
    8. com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
    9. com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
    10. com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
    11. com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1912)
    11 frames
  4. Hibernate
    CriteriaLoader.list
    1. org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    2. org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
    3. org.hibernate.loader.Loader.doQuery(Loader.java:697)
    4. org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    5. org.hibernate.loader.Loader.doList(Loader.java:2228)
    6. org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
    7. org.hibernate.loader.Loader.list(Loader.java:2120)
    8. org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
    8 frames
  5. Hibernate
    CriteriaImpl.list
    1. org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
    2. org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
    2 frames
  6. easytic.support
    EtudesSupport$EtudesList.getList
    1. easytic.support.EtudesSupport$EtudesList.getList(EtudesSupport.java:41)
    1 frame