Uploaded image for project: 'Openfire'
  1. OF-900

PubSubManager: DELETE FROM ofPubsubItem LEFT JOIN breaks MySQL

    Details

    • Type: Bug
    • Status: Resolved (View workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects versions: 3.9.3
    • Fix versions: 4.0.0
    • Components: PubSub
    • Labels:
    • Expected Effort:
      Minimal

      Description

      Using fresh install of OpenFire 3.9.3 (but the database has been taken from another machine, running pretty recent OF version)

      DB server is MariaDB 10

      Exception

      2015.03.28 20:51:59 org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1
      java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1
      at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1981)
      at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1393)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:606)
      at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
      at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
      at $java.sql.PreparedStatement$$EnhancerByProxool$$3661bae7.executeBatch(<generated>)
      at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPersistenceManager.java:1886)
      at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPersistenceManager.java:62)
      at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersistenceManager.java:279)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
      at java.util.concurrent.FutureTask.run(FutureTask.java:262)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
      at java.lang.Thread.run(Thread.java:745)
      Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
      at com.mysql.jdbc.Util.getInstance(Util.java:384)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
      at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1933)
      ... 16 more

      Manual test:

      MariaDB command line

      MariaDB [xmpp]> DELETE FROM ofPubsubItem LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceI
      -> D='saper@saper.info' AND nodeID='urn:xmpp:inbox' ORDER BY creationDate DESC LIMIT 1000000) AS noDelete ON ofPu
      -> bsubItem.id = noDelete.id WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID = 'saper@saper.info' AND nodeID
      -> = 'urn:xmpp:inbox'
      -> ;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceI
      D='saper@saper.info' AND n' at line 1

      Looks like 7029145857a07bce16b8f1b18ce625d6a1378157 broke this query for MySQL (it needs {{DELETE tablename FROM tablename LEFT JOIN .... }}

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                Dave Cridland
                Reporter:
                Marcin Marcin
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: