MAM - SQL command not properly ended

Openfire Openfire Dev
1

I am currently trying to integrate a XMPP Server in our application. I tried ejabberd but it was not possible to get it to work with Oracle. Thus, I switched to OpenFire which persists what I want but I have a problem getting MAM to work.

Problem:
When a user enters a room, I try to query the last “n” messages using the following stanza:

<iq id="64a20c4b-13fc-4230-a066-a6bf5937442e" to="s7pdls8h6m@conference.cns-icm-demo-2" type="set" from="fred@cns-icm-demo-2/7fh2aaugko">
  <query xmlns="urn:xmpp:mam:2" queryid="64a20c4b-13fc-4230-a066-a6bf5937442e">
    <set xmlns="http://jabber.org/protocol/rsm">
      <max>5</max>
      <before/>
    </set>
    <x xmlns="jabber:x:data" type="submit">
      <field var="FORM_TYPE" type="hidden">
        <value>urn:xmpp:mam:2</value>
      </field>
    </x>
  </query>
</iq>

I know that Openfire could send me the last messages. However, I need the above anyway to view the whole chat log step by step backwards.

Question 1:
I tried the stanza above without the monitoring plugin and the server tells me, that I cannot query the archive from the MUC directly. The stanza above worked in ejabberd so I am a bit confused why I cannot query the MUC directly.

Question 2:
When the monitoring plugin is enabled, I get the following error in the Server Log:

2021.03.10 11:51:28 DEBUG [message-archive-handler-9]: com.reucon.openfire.plugin.archive.impl.MucMamPersistenceManager - Finding messages in archive 's7pdls8h6m@conference.cns-icm-demo-2' for user 'fred@cns-icm-demo-2' with start date 'null', end date '2021-03-10T11:51:28.984+0100' with 'null', query: 'null' and resultset 'XmppResultSet{after=null, before=null, index=null, max=5, first=null, firstIndex=null, last=null, count=null, complete=false}', useStableId 'true'.
2021.03.10 11:51:28 DEBUG [message-archive-handler-9]: com.reucon.openfire.plugin.archive.impl.MucMamPersistenceManager - Request for message archive of room 's7pdls8h6m@conference.cns-icm-demo-2' did not specify a start date. Using EPOCH.
2021.03.10 11:51:28 DEBUG [message-archive-handler-9]: com.reucon.openfire.plugin.archive.impl.MucMamPersistenceManager - Using Monitoring plugin tables
2021.03.10 11:51:28 DEBUG [message-archive-handler-9]: com.reucon.openfire.plugin.archive.impl.MucMamPersistenceManager - Request for message archive of room 's7pdls8h6m@conference.cns-icm-demo-2' resulted in the following query data: PaginatedMessageQuery{startDate=Thu Jan 01 01:00:00 CET 1970, endDate=Wed Mar 10 11:51:28 CET 2021, archiveOwner=s7pdls8h6m@conference.cns-icm-demo-2, messageOwner=fred@cns-icm-demo-2, with='null'}
2021.03.10 11:51:28 ERROR [message-archive-handler-9]: com.reucon.openfire.plugin.archive.impl.PaginatedMucMessageDatabaseQuery - SQL failure during MUC MAM for room s7pdls8h6m@conference.cns-icm-demo-2, message owner: fred@cns-icm-demo-2
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:148) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:928) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1158) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1093) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1402) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1285) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3735) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3847) ~[ojdbc8.jar:21.1.0.0.0]
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1098) ~[ojdbc8.jar:21.1.0.0.0]
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.6.0.jar:2.6.0]
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.6.0.jar:2.6.0]
        at com.reucon.openfire.plugin.archive.impl.PaginatedMucMessageDatabaseQuery.getPage(PaginatedMucMessageDatabaseQuery.java:173) ~[?:?]
        at com.reucon.openfire.plugin.archive.impl.MucMamPersistenceManager.findMessages(MucMamPersistenceManager.java:118) ~[?:?]
        at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.retrieveMessages(IQQueryHandler.java:446) ~[?:?]
        at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.lambda$handleIQ$2(IQQueryHandler.java:269) ~[?:?]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_202]
        at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_202]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_202]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_202]
        at java.lang.Thread.run(Thread.java:748) [?:1.8.0_202]
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00933: SQL command not properly ended

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632) ~[ojdbc8.jar:21.1.0.0.0]
        ... 25 more

UPDATE:
When trace is enabled, the logged SQL is spit out as well. Which looks fine?

SELECT fromJID, fromJIDResource, toJID, toJIDResource, sentDate, body, stanza, messageID 
FROM ofMessageArchive WHERE (stanza IS NOT NULL OR body IS NOT NULL) 
AND messageID IS NOT NULL AND sentDate >= ? 
AND sentDate <= ? 
AND toJID = ? 
AND (isPMforJID IS NULL OR (isPMforJID = ? OR fromJID = ?)) 
ORDER BY sentDate DESC LIMIT 5
2

The problem is, that Oracle uses ROWNUM instead of LIMIT, so the string concatenation is wrong here.

3

A PR is pending for this.

4

If anybody cares: There’s a great library for JDBC, called jOOQ, which cares about ugly stuff like this.

See here: https://www.jooq.org/doc/3.14/manual/sql-building/sql-statements/select-statement/limit-clause/

5

Thanks I know this Lib - but this is my first time comitting something to a project like OpenFire and I did not want to mess to much with the codebase.

As we plan to use OF in our environment, I may get some time for this from the company.