Openfire Messages are not retrieved(4.6.7)

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="" from="">
  <query xmlns="urn:xmpp:mam:2" queryid="on-initial-load">
    <x xmlns="jabber:x:data" type="submit">
      <field type="hidden" var="FORM_TYPE">
    <set xmlns="">
      <before />
    <flip-page />
com.reucon.openfire.plugin.archive.impl.DataRetrievalException: com.reucon.openfire.plugin.archive.impl.DataRetrievalException: Incorrect syntax near 'LIMIT'.
at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.retrieveMessages( ~[?:?]
at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.lambda$handleIQ$2( ~[?:?]
  at java.util.concurrent.Executors$ Source) [?:1.8.0_202]
  at Source) [?:1.8.0_202]
  at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_202]
  at java.util.concurrent.ThreadPoolExecutor$ Source) [?:1.8.0_202]
  at Source) [?:1.8.0_202]

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:

1 Like

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

@guus Could u open an issue ?

Thanks for reporting this. I’ve raised this as

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:

@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!