-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]
1
  • It's definitely the result of incorrect SQL somewhere...
    – womble
    May 9, 2019 at 6:08

1 Answer 1

0

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

It would be nice if Openfire developers fix that error in a next bug-fixing release.

2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .