Uploaded image for project: 'Openfire'
  1. Openfire
  2. OF-1779

Incorrect SQL DELETE statement in PubSub causes exception

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.3.2
    • Fix Version/s: 4.5.0
    • Component/s: Database, PubSub
    • Labels:

      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:

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

      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?

        Attachments

          Activity

            People

            Assignee:
            dwd Dave Cridland
            Reporter:
            wroot wroot
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: