java.sql.SQLException

There are no available Samebug tips for this exception. Do you have an idea how to solve this issue? A short tip would help users who saw this issue last week.

  • bad SQL for filter expression
    via by 3004,
  • Parameters and sqlExp
    via by 3004,
  • Here's a brief view of the data model and the associations ManagedElement-->Chassis (many-to-one) Chassis---->Memory (many-to-one) as ramMemory Chassis---->Memory (many-to-one) as flashMemory IOSManagedElement is a joined subclass to ManagedElement IOSChassis is a joined subclass to Chassis Saving and cascading this object graph works fine. The root object being ManagedElement, I build a criteria as follows: Criteria crit = s.createCriteria(ManagedElement.class) .createCriteria("chassis") .createCriteria("flashMemory") .add(Expression.gt("size", new Long(64000))); which generates sql exactly as: select ...... from managedelement this left outer join IOSManagedElement this_1_ on this.id=this_1_.ManagedElement inner join Chassis x0_ on this.mge=x0_.id left outer join IOSChassis x0__1_ on x0_.id=x0__1_.Chassis inner join memory x1_ on x0_.flashMemory=x1_.id left outer join memory memoryimpl3_ on x0_.ramMemory=memoryimpl3_.id inner join Chassis x0_ on this_1_.mge=x0_.id left outer join IOSChassis x0__1_ on x0_.id=x0__1_.Chassis inner join memory x1_ on x0_.flashMemory=x1_.id left outer join memory memoryimpl6_ on x0_.ramMemory=memoryimpl6_.id where x1_.size>? I notice it tries to perform a join separately with each subclass of ManagedElement but with the same aliases which causes: java.sql.SQLException: Invalid argument value, message from server: "Not unique table/alias: 'x0_'" at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956) at com.mysql.jdbc.Connection.execSQL(Connection.java:1874) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538) at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87) at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:795) at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189) at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133) at net.sf.hibernate.loader.Loader.doList(Loader.java:950) at net.sf.hibernate.loader.Loader.list(Loader.java:941) at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:118) at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3571) at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:223) at net.sf.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:84) hope this information provides a basis to investigate the problem. i didn't want to clutter this by including all the configuration files. will be glad to add more details.
    via by Ravi K,
  • I've acknowledged an error that happens using "select count (*) ..." together to a where clause "... where alias.class = ClassName ". Suppose to have two classes: public class Foo { String id; String attr1; public String getId(){ return id; } public void setId(String id){ this.id = id; } public String getAttr1(){ return attr1; } public void setAttr1(String attr1){ this.attr1 = attr1; } } public class Bar extends Foo { String attr2; public String getAttr2(){ return attr2; } public void setAttr2(String attr2){ this.attr2 = attr2; } } and the corresponding mapping file: <hibernate-mapping> <class name="test.persistents.Foo" table="Foo"> <id name="id" type="string"> <column name="id" length="32"/> <generator class="uuid.hex"/> </id> <property name="attr1" column="attr1" type="string"/> <joined-subclass name="test.persistents.Bar" table="Bar"> <key column="foo_id"/> <property name="attr2" column="attr2" type="string"/> </joined-subclass> </class> </hibernate-mapping> When I try to make the following query: Query query = session.createQuery("select count(*) from Foo foo where foo.class = Foo"); I get an error of this type: java.sql.SQLException: General error, message from server: "Unknown table 'foo0__1' in where clause" at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956) at com.mysql.jdbc.Connection.execSQL(Connection.java:1874) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538) at com.p6spy.engine.logging.P6LogPreparedStatement.executeQuery(P6LogPreparedStatement.java:171) at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:71) at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:551) at net.sf.hibernate.loader.Loader.doFind(Loader.java:140) at net.sf.hibernate.loader.Loader.find(Loader.java:620) at net.sf.hibernate.hql.QueryTranslator.find(QueryTranslator.java:928) at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1343) at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:76) at test.Test.main(Test.java:42) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) while if I perform the same query without the select count(*) (select foo from Foo foo where foo.class = Foo) all works fine. Comparing the two queries produced by hibernate in both cases, it seems that the error derives from the lack of the "left outer join" with the Bar table in the "from" clause: with select count(*): select count(*) as x0_0_ from Foo foo0_ where (case when foo0__1.foo_id is not null then 1 when foo0_.id is not null then 0 end=0 ) without select count(*): select foo0_.id as id, case when foo0__1.foo_id is not null then 1 when foo0_.id is not null then 0 end as clazz_, foo0_.attr1 as attr10_, foo0__1.attr2 as attr21_ from Foo foo0_ left outer join Bar foo0__1 on foo0_.id=foo0__1.foo_id where (case when foo0__1.foo_id is not null then 1 when foo0_.id is not null then 0 end=0 ) in fact the query should be the following: select count(*) as x0_0_ from Foo foo0_ left outer join Bar foo0__1 on foo0_.id=foo0__1.foo_id where (case when foo0__1.foo_id is not null then 1 when foo0_.id is not null then 0 end=0 )
    via by Simone Ricciardi,
  • Here's a brief view of the data model and the associations ManagedElement-->Chassis (many-to-one) Chassis---->Memory (many-to-one) as ramMemory Chassis---->Memory (many-to-one) as flashMemory IOSManagedElement is a joined subclass to ManagedElement IOSChassis is a joined subclass to Chassis Saving and cascading this object graph works fine. The root object being ManagedElement, I build a criteria as follows: Criteria crit = s.createCriteria(ManagedElement.class) .createCriteria("chassis") .createCriteria("flashMemory") .add(Expression.gt("size", new Long(64000))); which generates sql exactly as: select ...... from managedelement this left outer join IOSManagedElement this_1_ on this.id=this_1_.ManagedElement inner join Chassis x0_ on this.mge=x0_.id left outer join IOSChassis x0__1_ on x0_.id=x0__1_.Chassis inner join memory x1_ on x0_.flashMemory=x1_.id left outer join memory memoryimpl3_ on x0_.ramMemory=memoryimpl3_.id inner join Chassis x0_ on this_1_.mge=x0_.id left outer join IOSChassis x0__1_ on x0_.id=x0__1_.Chassis inner join memory x1_ on x0_.flashMemory=x1_.id left outer join memory memoryimpl6_ on x0_.ramMemory=memoryimpl6_.id where x1_.size>? I notice it tries to perform a join separately with each subclass of ManagedElement but with the same aliases which causes: java.sql.SQLException: Invalid argument value, message from server: "Not unique table/alias: 'x0_'" at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956) at com.mysql.jdbc.Connection.execSQL(Connection.java:1874) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538) at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87) at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:795) at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189) at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133) at net.sf.hibernate.loader.Loader.doList(Loader.java:950) at net.sf.hibernate.loader.Loader.list(Loader.java:941) at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:118) at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3571) at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:223) at net.sf.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:84) hope this information provides a basis to investigate the problem. i didn't want to clutter this by including all the configuration files. will be glad to add more details.
    via by Ravi K,
  • I've acknowledged an error that happens using "select count (*) ..." together to a where clause "... where alias.class = ClassName ". Suppose to have two classes: public class Foo { String id; String attr1; public String getId(){ return id; } public void setId(String id){ this.id = id; } public String getAttr1(){ return attr1; } public void setAttr1(String attr1){ this.attr1 = attr1; } } public class Bar extends Foo { String attr2; public String getAttr2(){ return attr2; } public void setAttr2(String attr2){ this.attr2 = attr2; } } and the corresponding mapping file: <hibernate-mapping> <class name="test.persistents.Foo" table="Foo"> <id name="id" type="string"> <column name="id" length="32"/> <generator class="uuid.hex"/> </id> <property name="attr1" column="attr1" type="string"/> <joined-subclass name="test.persistents.Bar" table="Bar"> <key column="foo_id"/> <property name="attr2" column="attr2" type="string"/> </joined-subclass> </class> </hibernate-mapping> When I try to make the following query: Query query = session.createQuery("select count(*) from Foo foo where foo.class = Foo"); I get an error of this type: java.sql.SQLException: General error, message from server: "Unknown table 'foo0__1' in where clause" at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956) at com.mysql.jdbc.Connection.execSQL(Connection.java:1874) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538) at com.p6spy.engine.logging.P6LogPreparedStatement.executeQuery(P6LogPreparedStatement.java:171) at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:71) at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:551) at net.sf.hibernate.loader.Loader.doFind(Loader.java:140) at net.sf.hibernate.loader.Loader.find(Loader.java:620) at net.sf.hibernate.hql.QueryTranslator.find(QueryTranslator.java:928) at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1343) at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:76) at test.Test.main(Test.java:42) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) while if I perform the same query without the select count(*) (select foo from Foo foo where foo.class = Foo) all works fine. Comparing the two queries produced by hibernate in both cases, it seems that the error derives from the lack of the "left outer join" with the Bar table in the "from" clause: with select count(*): select count(*) as x0_0_ from Foo foo0_ where (case when foo0__1.foo_id is not null then 1 when foo0_.id is not null then 0 end=0 ) without select count(*): select foo0_.id as id, case when foo0__1.foo_id is not null then 1 when foo0_.id is not null then 0 end as clazz_, foo0_.attr1 as attr10_, foo0__1.attr2 as attr21_ from Foo foo0_ left outer join Bar foo0__1 on foo0_.id=foo0__1.foo_id where (case when foo0__1.foo_id is not null then 1 when foo0_.id is not null then 0 end=0 ) in fact the query should be the following: select count(*) as x0_0_ from Foo foo0_ left outer join Bar foo0__1 on foo0_.id=foo0__1.foo_id where (case when foo0__1.foo_id is not null then 1 when foo0_.id is not null then 0 end=0 )
    via by Simone Ricciardi,
  • exception 1 « Exception « JPA Q&A
    via by Unknown author,
    • java.sql.SQLException: Syntax error or access violation, message from server: "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 'WHERE NOT (t0.PK = t1.channel AND t0.supporting = '101/DS3/LOC0" at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956) at com.mysql.jdbc.Connection.execSQL(Connection.java:1874) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538) at com.solarmetric.datasource.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:93) at com.solarmetric.kodo.impl.jdbc.SQLExecutionManagerImpl.executePreparedQueryInternal(SQLExecutionManagerImpl.java:707) at com.solarmetric.kodo.impl.jdbc.SQLExecutionManagerImpl.executeQueryInternal(SQLExecutionManagerImpl.java:600) at com.solarmetric.kodo.impl.jdbc.SQLExecutionManagerImpl.executeQuery(SQLExecutionManagerImpl.java:355) at com.solarmetric.kodo.impl.jdbc.ormapping.ClassMapping.selectPrimaryMappings(ClassMapping.java:1743) at com.solarmetric.kodo.impl.jdbc.runtime.JDBCStoreManager.executeQuery(JDBCStoreManager.java:1136) at com.solarmetric.kodo.impl.jdbc.query.JDBCQuery.executeQuery(JDBCQuery.java:126) at com.solarmetric.kodo.query.QueryImpl$DatastoreQueryExecutor.executeQuery(QueryImpl.java:1565) at com.solarmetric.kodo.query.QueryImpl$AggregateQueryExecutor.executeQuery(QueryImpl.java:1401) at com.solarmetric.kodo.query.QueryImpl.executeQueryWithMap(QueryImpl.java:685) at com.solarmetric.kodo.query.QueryImpl.executeWithMap(QueryImpl.java:545) at com.solarmetric.kodo.query.QueryImpl.executeWithArray(QueryImpl.java:531) at com.solarmetric.kodo.query.QueryImpl.execute(QueryImpl.java:513) at com.metasolv.resources.queries.ChannelAvailabilityQueryBean.execute(ChannelAvailabilityQueryBean.java:55) at com.metasolv.oss.inventory.InventorySessionBean.queryInventory(InventorySessionBean.java:1343) at com.metasolv.oss.inventory.InventorySessionBean_xf0gis_EOImpl.queryInventory(InventorySessionBean_xf0gis_EOImpl.java:3998) at com.metasolv.oss.inventory.InventorySessionBean_xf0gis_EOImpl_WLSkel.invoke(Unknown Source) at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:362)

    Users with the same issue

    ivanszkypeter
    470 times, last one,
    slash
    1 times, last one,
    musketyr
    1 times, last one,
    mauritius
    1 times, last one,
    Kialandei
    11 times, last one,
    20 more bugmates