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:

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?

Environment

None

Activity

Show:
wroot
September 21, 2019, 12:10 PM

If you are familiar with Git/GitHub, you can propose a PR here https://github.com/igniterealtime/Openfire

Or you can post proposed patch in the forums https://discourse.igniterealtime.org/latest

Khaled Jamoos
September 21, 2019, 4:26 AM
Edited

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.

KJ

 

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

Assignee

Dave Cridland

Reporter

wroot

Labels