powered by Jive Software

org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - Incorrect syntax near the keyword 'LEFT'

Any idea what could be causing this error to be constantly produced in all.log and error. log. Openfire 4.3.2 on Windows connected to SQL Server 2012 (SP4) - although it shouldn’t matter. Could it be a result of incorrect SQL in ofProperty table? Where to look?
We have a working web/Candy.js chat application…

2019.05.08 20:46:12 ERROR [TaskEngine-pool-16]: org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - Incorrect syntax near the keyword 'LEFT'.
java.sql.BatchUpdateException: Incorrect syntax near the keyword 'LEFT'.
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069) ~[jtds-1.3.1.jar:1.3.1]
	at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
	at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
	at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPersistenceManager.java:1893) [xmppserver-4.3.2.jar:4.3.2]
	at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPersistenceManager.java:57) [xmppserver-4.3.2.jar:4.3.2]
	at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersistenceManager.java:283) [xmppserver-4.3.2.jar:4.3.2]
	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]

I know what’s causing

org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - Incorrect syntax near the keyword 'LEFT'.
java.sql.BatchUpdateException: Incorrect syntax near the keyword 'LEFT'

error. It is incorrect or at least non-standard SQL generated by Openfire. It would be nice if Openfire developers fix it.

As SQL Server Profiler shows, Openfire tries to execute the following SQL:

DELETE FROM ofPubsubItem
   LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID= @P0  AND nodeID= @P1  ORDER BY creationDate DESC LIMIT  @P2 ) AS noDelete
            ON ofPubsubItem.id = noDelete.id
WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID =  @P3  AND nodeID =  @P4 ....'

It is incorrect (or at very least non-standard DELETE from join statement. According to https://stackoverflow.com/questions/4097260/sql-delete-based-on-condition-in-join, https://www.sqlservercentral.com/forums/topic/delete-statements-when-using-a-join-best-practice and other resources, there is no ANSI SQL standard for deleting from a join. Using sub-queries instead of joins would always work, but if you want to use join, you normally need to include FROM keyword twice like:

DELETE FROM ofPubsubItem
  FROM ofPubsubItem LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID= @P0  AND nodeID= @P1  ORDER BY creationDate DESC LIMIT  @P2 ) AS noDelete
            ON ofPubsubItem.id = noDelete.id
WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID =  @P3  AND nodeID =  @P4 ....'

According to https://github.com/candy-chat/candy/wiki/Installing-a-XMPP-server, Openfire PEP module may leak a memory. Since PEP (Personal Eventing Protocol https://xmpp.org/extensions/xep-0163.html) is kind of subset of PubSub, I wonder if that memory leakage could be a result of constant SQL exceptions?