Incorrect SQL DELETE statement in PubSub causes exception


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, 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, Openfire PEP module may leak a memory. Since PEP (Personal Eventing Protocol is kind of subset of PubSub, I wonder if that memory leakage could be a result of constant SQL exceptions?




September 21, 2019, 12:10 PM

If you are familiar with Git/GitHub, you can propose a PR here

Or you can post proposed patch in the forums

Khaled Jamoos
September 21, 2019, 4:26 AM

Hi there:

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.



Your pinned fields
Click on the next to a field label to start pinning.


Dave Cridland