Openfire Messages are not retrieved(4.6.7)

Narayan · August 16, 2022, 6:31pm

Hi All,
I was unable to retrieve messages from openfire. after restarting openfire service i am able to retrieve the messages

I have observed below error in openfire logs . Please suggest
com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler - An unexpected exception occurred while processing:

<iq type="set" id="af533090-167d-4ef4-b049-42f08b46802c" to="119_98_95_99_104_97_116_105_100_95_49_48_48_49_48_48@conference.openfire.mydomain.ai" from="pp@openfire.mydomain.ai/9n8xlbdgcj">
  <query xmlns="urn:xmpp:mam:2" queryid="on-initial-load">
    <x xmlns="jabber:x:data" type="submit">
      <field type="hidden" var="FORM_TYPE">
        <value>urn:xmpp:mam:2</value>
      </field>
    </x>
    <set xmlns="http://jabber.org/protocol/rsm">
      <max>100</max>
      <before />
    </set>
    <flip-page />
  </query>
</iq>

com.reucon.openfire.plugin.archive.impl.DataRetrievalException: com.reucon.openfire.plugin.archive.impl.DataRetrievalException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'LIMIT'.
at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.retrieveMessages(IQQueryHandler.java:471) ~[?:?]
at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.lambda$handleIQ$2(IQQueryHandler.java:269) ~[?:?]
  at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:1.8.0_202]
  at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_202]
  at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_202]
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_202]
  at java.lang.Thread.run(Unknown Source) [?:1.8.0_202]

totzkotz · August 18, 2022, 12:29am

yes thats seems to be a bug, because Sql Server does not know the “LIMIT” keyword. it uses “TOP” instead…
some code switches missing here:

github.com

igniterealtime/openfire-monitoring-plugin/blob/c9b40dd1a1e9bc40b7485068637384fb7ec2c063/src/java/org/jivesoftware/openfire/archive/ArchiveSearcher.java#L344

      
        
            query.append(" ORDER BY ofConversation.startDate");
            if (search.getSortOrder() == ArchiveSearch.SortOrder.descending) {
                query.append(" DESC");
            }
            else {
                query.append(" ASC");
            }
            
            
int startIndex = search.getStartIndex();
            int numResults = search.getNumResults();
            if (numResults != ArchiveSearch.NULL_INT) {
                // MySQL optimization: use the LIMIT command to tell the database how many
                // rows we need returned. The syntax is LIMIT [offset],[rows]
                if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.mysql) {
                    query.append(" LIMIT ").append(startIndex).append(",").append(numResults);
                }
                // PostgreSQL optimization: use the LIMIT command to tell the database how many
                // rows we need returned. The syntax is LIMIT [rows] OFFSET [offset]
                else if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.postgresql) {
                    query.append(" LIMIT ").append(numResults).append(" OFFSET ").append(startIndex);
                }

Narayan · August 20, 2022, 8:17am

Can you please provide bug id if any bug created for the same?

totzkotz · August 20, 2022, 10:49pm

@guus Could u open an issue ?

guus · August 24, 2022, 11:16am

Thanks for reporting this. I’ve raised this as https://github.com/igniterealtime/openfire-monitoring-plugin/issues/232

totzkotz · August 24, 2022, 11:22pm

I had overseen in dot 1 that there allready is a special method for sql server so dot 2. should be the bug.

We have to add a


else if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.sqlserver) {
              //TODO: add "OFFSET X FETCH NEXT Y ROWS ONLY" to query

    }

PR to fix it: https://github.com/igniterealtime/openfire-monitoring-plugin/pull/234

guus · October 24, 2022, 6:21pm

@totzkotz’s fixes will be part of the next release of the plugin! Prior to the release, you can already use a snapshot release of the plugin if you want to give this fix a try!