Incorrect SQL DELETE statement in PubSub causes exception
Description
From the forums:
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:
This is causing me some serious concern as I can see the exception popping up so often (I am using OpenFire 4.4.0) , if the fix suggested above is not working I am happy to help (This should be trivial really), please let me know when will you get this released at all?
Sorry for being a bit lost but I am not sure how to submit a fix , so any pointer would be helpful.
From the forums:
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:
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:
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?