Hello,

We got the following exception when using Hibernate with MySQL 12.22.

From the message it looks like that there's some problem on the generated SQL statement. Is there any workaround with this problem? Thanks.

Hibernate: select retry0_.c_messageId as c_messag1_, retry0_.c_retries as c_retr
ies, retry0_.c_Interval as c_Interval, retry0_.c_attemptsMade as c_attemp4_, ret
ry0_.c_nextAttemptTime as c_nextAt5_, retry0_.c_inUse as c_inUse from retry retr
y0_, outgoing outgoing1_, reliableMessage reliableme2_ where (retry0_.c_messageI
d=outgoing1_.c_messageId )AND(retry0_.c_messageId=reliableme2_.c_messageId )AND(
((reliableme2_.c_TTL IS NULL ))OR((reliableme2_.c_TTL>retry0_.c_nextAttemptTime
)))AND(outgoing1_.c_deliveryFailed IS FALSE )AND(retry0_.c_nextAttemptTime IS NO
T NULL )AND(retry0_.c_inUse IS FALSE ) order by retry0_.c_nextAttemptTime ASC
Failed : getNextRetryMessage()
MessageId : null
net.sf.hibernate.JDBCException: Could not execute query
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1478)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1454)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1446)
at hk.hku.csis.wsr.database.WSRMMessageStoreHibernateImpl.getNextRetryMe
ssage(WSRMMessageStoreHibernateImpl.java:670)
at hk.hku.csis.wsr.server.RetryServer.produce(RetryServer.java:154)
at hk.hku.csis.wsr.server.AbstractServer.run(AbstractServer.java:95)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: Syntax error or access violation: You have an
error in your SQL syntax. Check the manual that corresponds to your MySQL serve
r version for the right syntax to use near 'FALSE )AND(retry0_.c_nextAttemptTime
IS NOT NULL )AND(retry0_.c
at com.mysql.jdbc.MysqlIO.sendCommand(Unknown Source)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(Unknown Source)
at com.mysql.jdbc.Connection.execSQL(Unknown Source)
at com.mysql.jdbc.PreparedStatement.executeQuery(Unknown Source)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
(Loader.java:132)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
... 6 more
Hibernate: select retry0_.c_messageId as c_messag1_, retry0_.c_retries as c_retr
ies, retry0_.c_Interval as c_Interval, retry0_.c_attemptsMade as c_attemp4_, ret
ry0_.c_nextAttemptTime as c_nextAt5_, retry0_.c_inUse as c_inUse from retry retr
y0_, outgoing outgoing1_, reliableMessage reliableme2_ where (retry0_.c_messageI
d=outgoing1_.c_messageId )AND(retry0_.c_messageId=reliableme2_.c_messageId )AND(
((reliableme2_.c_TTL IS NULL ))OR((reliableme2_.c_TTL>retry0_.c_nextAttemptTime
)))AND(outgoing1_.c_deliveryFailed IS FALSE )AND(retry0_.c_nextAttemptTime IS NO
T NULL )AND(retry0_.c_inUse IS FALSE ) order by retry0_.c_nextAttemptTime ASC
Failed : getNextRetryMessage()
MessageId : null
net.sf.hibernate.JDBCException: Could not execute query
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1478)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1454)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1446)
at hk.hku.csis.wsr.database.WSRMMessageStoreHibernateImpl.getNextRetryMe
ssage(WSRMMessageStoreHibernateImpl.java:670)
at hk.hku.csis.wsr.server.RetryServer.produce(RetryServer.java:154)
at hk.hku.csis.wsr.server.AbstractServer.run(AbstractServer.java:95)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: Syntax error or access violation: You have an
error in your SQL syntax. Check the manual that corresponds to your MySQL serve
r version for the right syntax to use near 'FALSE )AND(retry0_.c_nextAttemptTime
IS NOT NULL )AND(retry0_.c
at com.mysql.jdbc.MysqlIO.sendCommand(Unknown Source)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(Unknown Source)
at com.mysql.jdbc.Connection.execSQL(Unknown Source)
at com.mysql.jdbc.PreparedStatement.executeQuery(Unknown Source)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
(Loader.java:132)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
... 6 more
Hibernate: select retry0_.c_messageId as c_messag1_, retry0_.c_retries as c_retr
ies, retry0_.c_Interval as c_Interval, retry0_.c_attemptsMade as c_attemp4_, ret
ry0_.c_nextAttemptTime as c_nextAt5_, retry0_.c_inUse as c_inUse from retry retr
y0_, outgoing outgoing1_, reliableMessage reliableme2_ where (retry0_.c_messageI
d=outgoing1_.c_messageId )AND(retry0_.c_messageId=reliableme2_.c_messageId )AND(
((reliableme2_.c_TTL IS NULL ))OR((reliableme2_.c_TTL>retry0_.c_nextAttemptTime
)))AND(outgoing1_.c_deliveryFailed IS FALSE )AND(retry0_.c_nextAttemptTime IS NO
T NULL )AND(retry0_.c_inUse IS FALSE ) order by retry0_.c_nextAttemptTime ASC
Failed : getNextRetryMessage()
MessageId : null
net.sf.hibernate.JDBCException: Could not execute query
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1478)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1454)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1446)
at hk.hku.csis.wsr.database.WSRMMessageStoreHibernateImpl.getNextRetryMe
ssage(WSRMMessageStoreHibernateImpl.java:670)
at hk.hku.csis.wsr.server.RetryServer.produce(RetryServer.java:154)
at hk.hku.csis.wsr.server.AbstractServer.run(AbstractServer.java:95)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: Syntax error or access violation: You have an
error in your SQL syntax. Check the manual that corresponds to your MySQL serve
r version for the right syntax to use near 'FALSE )AND(retry0_.c_nextAttemptTime
IS NOT NULL )AND(retry0_.c
at com.mysql.jdbc.MysqlIO.sendCommand(Unknown Source)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(Unknown Source)
at com.mysql.jdbc.Connection.execSQL(Unknown Source)
at com.mysql.jdbc.PreparedStatement.executeQuery(Unknown Source)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
(Loader.java:132)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
... 6 more

Looks like you are executing a crappy query. Really impossible to tell without the calling code.

Propably an error in your HQL or in your mapping

Here is the Hibernate Query:

"SELECT Retry FROM hk.hku.csis.wsr.database.table.Retry as Retry, " +
"hk.hku.csis.wsr.database.table.Outgoing as Outgoing, "+
"hk.hku.csis.wsr.database.table.ReliableMessage " +
"as ReliableMessage " +
"WHERE Retry.messageId = Outgoing.messageId " +
"AND Retry.messageId = ReliableMessage.messageId " +
"AND ((ReliableMessage.TTL IS NULL) OR " +
"(ReliableMessage.TTL > Retry.nextAttemptTime)) AND " +
"Outgoing.deliveryFailed IS FALSE AND " +
"Retry.nextAttemptTime IS NOT NULL AND " +
"Retry.inUse IS FALSE " +
"ORDER BY Retry.nextAttemptTime ASC";

And the mapping files:

Retry.hbm.xml

<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">


<hibernate-mapping>
<class name="hk.hku.csis.wsr.database.table.Retry" table="retry">
<id name="messageId" column="c_messageId" type="string" length="255" unsaved-value="null">
<generator class="assigned"/>
</id>

<property name="retries" column="c_retries" type="int" not-null="true"/>
<property name="interval" column="c_Interval" type="long" not-null="true"/>
<property name="attemptsMade" column="c_attemptsMade" type="int" not-null="true"/>
<property name="nextAttemptTime" column="c_nextAttemptTime" type="timestamp" not-null="false"/>
<property name="inUse" column="c_inUse" type="boolean" not-null="true"/>


</class>
</hibernate-mapping>

ReliableMessage.hbm.xml

<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">


<hibernate-mapping>
<class name="hk.hku.csis.wsr.database.table.ReliableMessage" table="reliableMessage">
<id name="messageId" column="c_messageId" type="string" length="255" unsaved-value="null">
<generator class="assigned"/>
</id>

<property name="replyTo" column="c_replyTo" type="string" length="255" not-null="true"/>
<property name="TTL" column="c_TTL" type="timestamp" not-null="false"/>
<property name="ackRequested" column="c_ackRequested" type="boolean" not-null="false"/>
<property name="duplicateElimination" column="c_duplicateElimination" type="boolean" not-null="false"/>


<set name="incoming" cascade="all" inverse="true" lazy="true">
<key column="c_messageId"/>
<one-to-many class="hk.hku.csis.wsr.database.table.Incoming"/>

</set>

<set name="outgoing" cascade="all" inverse="true" lazy="true">
<key column="c_messageId"/>
<one-to-many class="hk.hku.csis.wsr.database.table.Outgoing"/>

</set>

<set name="retry" cascade="all" inverse="true" lazy="true">
<key column="c_messageId"/>
<one-to-many class="hk.hku.csis.wsr.database.table.Retry"/>
</set>

<!--
<set name="messageOrder" cascade="all" inverse="true" lazy="true">
<key column="messageId"/>
<one-to-many class="hk.hku.csis.wsr.database.table.MessageOrder"/>
</set>
-->

</class>
</hibernate-mapping>

Outgoing.hbm.xml

<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">


<hibernate-mapping>
<class name="hk.hku.csis.wsr.database.table.Outgoing" table="outgoing">
<id name="messageId" column="c_messageId" type="string" length="255" unsaved-value="null">
<generator class="assigned"/>
</id>

<property name="deliveryFailed" column="c_deliveryFailed" type="boolean" not-null="true"/>

</class>
</hibernate-mapping>

Outgoing.deliveryFailed IS FALSE

is the problem. Where did you get this from? use = false and take a look at query subsitutions (to busy searching it out from a previous forum thread)