Error retrieving archived messages

Openfire Plugins
1

Openfire 8.4.1
Oracle 11c
Message Archiving 2.5.0

Hi, by requesting archived messages, i get this error.
Any help?
Thanks

15:20:34.797 [message-archive-handler-9] ERROR com.reucon.openfire.plugin.archive.impl.PaginatedMessageDatabaseQuery - SQL failure during MAM for owner: xxxxxx
java.sql.SQLSyntaxErrorException: ORA-00933: comando SQL terminato erroneamente

https://docs.oracle.com/error-help/db/ora-00933/
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:702) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:608) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1330) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1102) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:456) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:272) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1198) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1390) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1269) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1794) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1592) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3754) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3940) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1103) ~[ojdbc11.jar:23.3.0.23.09]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at com.reucon.openfire.plugin.archive.impl.PaginatedMessageDatabaseQuery.getPage(PaginatedMessageDatabaseQuery.java:157) ~[?:?]
	at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.findMessages(JdbcPersistenceManager.java:338) ~[?:?]
	at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.retrieveMessages(IQQueryHandler.java:464) ~[?:?]
	at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.lambda$handleIQ$2(IQQueryHandler.java:287) ~[?:?]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572) ~[?:?]
	at java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
	at java.lang.Thread.run(Thread.java:1570) [?:?]
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00933: comando SQL terminato erroneamente

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:710) ~[ojdbc11.jar:23.3.0.23.09]
	... 25 more
15:20:34.799 [message-archive-handler-9] ERROR com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler - An exception has occurred while retrieving messages: 
com.reucon.openfire.plugin.archive.impl.DataRetrievalException: java.sql.SQLSyntaxErrorException: ORA-00933: comando SQL terminato erroneamente

https://docs.oracle.com/error-help/db/ora-00933/
	at com.reucon.openfire.plugin.archive.impl.PaginatedMessageDatabaseQuery.getPage(PaginatedMessageDatabaseQuery.java:168) ~[?:?]
	at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.findMessages(JdbcPersistenceManager.java:338) ~[?:?]
	at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.retrieveMessages(IQQueryHandler.java:464) ~[?:?]
	at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.lambda$handleIQ$2(IQQueryHandler.java:287) ~[?:?]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572) ~[?:?]
	at java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
	at java.lang.Thread.run(Thread.java:1570) [?:?]
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: comando SQL terminato erroneamente

https://docs.oracle.com/error-help/db/ora-00933/
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:702) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:608) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1330) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1102) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:456) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:272) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1198) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1390) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1269) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1794) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1592) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3754) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3940) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1103) ~[ojdbc11.jar:23.3.0.23.09]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at com.reucon.openfire.plugin.archive.impl.PaginatedMessageDatabaseQuery.getPage(PaginatedMessageDatabaseQuery.java:157) ~[?:?]
	... 8 more
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00933: comando SQL terminato erroneamente

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:710) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:608) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1330) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1102) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:456) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:272) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1198) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1390) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1269) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1794) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1592) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3754) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3940) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1103) ~[ojdbc11.jar:23.3.0.23.09]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at com.reucon.openfire.plugin.archive.impl.PaginatedMessageDatabaseQuery.getPage(PaginatedMessageDatabaseQuery.java:157) ~[?:?]
	... 8 more
15:20:34.800 [message-archive-handler-9] ERROR com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler - An unexpected exception occurred while processing: 
<iq type="set" id="7761-501-3458-583" from="xxxxxx" to="xxxxxx">
  <query xmlns="urn:xmpp:mam:2">
    <x xmlns="jabber:x:data" type="submit">
      <field var="FORM_TYPE" type="hidden">
        <value>urn:xmpp:mam:2</value>
      </field>
    </x>
    <set xmlns="http://jabber.org/protocol/rsm"/>
  </query>
</iq>
com.reucon.openfire.plugin.archive.impl.DataRetrievalException: com.reucon.openfire.plugin.archive.impl.DataRetrievalException: java.sql.SQLSyntaxErrorException: ORA-00933: comando SQL terminato erroneamente

https://docs.oracle.com/error-help/db/ora-00933/
	at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.retrieveMessages(IQQueryHandler.java:489) ~[?:?]
	at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.lambda$handleIQ$2(IQQueryHandler.java:287) ~[?:?]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572) ~[?:?]
	at java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
	at java.lang.Thread.run(Thread.java:1570) [?:?]
Caused by: com.reucon.openfire.plugin.archive.impl.DataRetrievalException: java.sql.SQLSyntaxErrorException: ORA-00933: comando SQL terminato erroneamente

https://docs.oracle.com/error-help/db/ora-00933/
	at com.reucon.openfire.plugin.archive.impl.PaginatedMessageDatabaseQuery.getPage(PaginatedMessageDatabaseQuery.java:168) ~[?:?]
	at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.findMessages(JdbcPersistenceManager.java:338) ~[?:?]
	at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.retrieveMessages(IQQueryHandler.java:464) ~[?:?]
	... 6 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: comando SQL terminato erroneamente

https://docs.oracle.com/error-help/db/ora-00933/
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:702) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:608) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1330) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1102) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:456) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:272) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1198) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1390) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1269) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1794) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1592) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3754) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3940) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1103) ~[ojdbc11.jar:23.3.0.23.09]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at com.reucon.openfire.plugin.archive.impl.PaginatedMessageDatabaseQuery.getPage(PaginatedMessageDatabaseQuery.java:157) ~[?:?]
	at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.findMessages(JdbcPersistenceManager.java:338) ~[?:?]
	at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.retrieveMessages(IQQueryHandler.java:464) ~[?:?]
	... 6 more
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00933: comando SQL terminato erroneamente

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:710) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:608) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1330) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1102) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:456) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:272) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1198) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1390) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1269) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1794) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1592) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3754) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3940) ~[ojdbc11.jar:23.3.0.23.09]
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1103) ~[ojdbc11.jar:23.3.0.23.09]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at com.reucon.openfire.plugin.archive.impl.PaginatedMessageDatabaseQuery.getPage(PaginatedMessageDatabaseQuery.java:157) ~[?:?]
	at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.findMessages(JdbcPersistenceManager.java:338) ~[?:?]
	at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.retrieveMessages(IQQueryHandler.java:464) ~[?:?]
	... 6 more
2

Hi, by looking at source code, i’ve found the problem:
in PaginatedMessageDatabaseQuery.java, buildQueryForMessages it uses

        sql += " LIMIT " + maxResults;

but the LIMIT clause is not supported in Oracle.
Can you please fix this issue?
Thanks

3

Ah, is this fixed by This should make MAM in Oracle >= 12 work by smu-cns · Pull Request #180 · igniterealtime/openfire-monitoring-plugin · GitHub perhaps?

Are you in a position to test that fix?

4

Yes i can try, but i haven’t a Java building environment, so would it be possible to get a compiled JAR?
Thanks

5

If you scroll down on the plugin’s archive page, then you’ll find a list of ‘snapshot’ releases. Any snapshot release that was created on or after april 17 2024 should include the fix. Please let us know if it solves the issue!

6

Hi, thanks, it seems to be working! In next days i will do more extensive tests.

1 Like