powered by Jive Software

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


#1

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]

#2

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 ....'

#3

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?