SQLException: Unexpected token OFPUBSUBITEM

Openfire Openfire Dev
1

I just randomly got this in the log although I didn’t do anything with pubsub:

There’s a SQL error in the PURGE_FOR_SIZE statement.

Strangely, nobody else had it?

java.sql.SQLException: Unexpected token OFPUBSUBITEM, requires FROM in statement [DELETE ofPubsubItem FROM ofPubsubItem LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID=? AND nodeID=? ORDER BY creationDate DESC LIMIT ?) AS noDelete ON ofPubsubItem.id = noDelete.id WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID = ? AND nodeID = ?]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja va:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.logicalcobwebs.proxool.WrappedConnection.invoke(WrappedConnection.java:162)
at org.logicalcobwebs.proxool.WrappedConnection.intercept(WrappedConnection.java:8 7)
at $java.sql.Wrapper$$EnhancerByProxool$$694b7953.prepareStatement()
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPers istenceManager.java:1874)
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPers istenceManager.java:62)
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersisten ceManager.java:279)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

2

Yes, looks like a bug in the SQL delete statement for the Pubsub item purge thread (for DBs other than HSQL and Postgres). Opened OF-789 to fix.

3

You’re fast … saw your fix was already in.

Thanks.

4

Yes, it wasn’t much to do here. Creating the Jira issue probably took more time, than fixing the bug, that’s why I didn’t create one. (Although it might be useful for tracking in the changelog).

5

In Openfire 3.9.3 with mysql database this fix isn’t working. I get the following exception:

ERROR [PubSubPersistenceManager] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID=‘pubsub’ AND nodeID=‘Test’ at line 1

java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID=‘pubsub’ AND nodeID=‘Test’ at line 1

at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:19 81)

at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1393)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja va:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

at com.mysql.jdbc.Statement$$EnhancerByProxool$$f22e4875.executeBatch()

at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPers istenceManager.java:1886)

at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPers istenceManager.java:62)

at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersisten ceManager.java:279)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)

at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

at java.util.concurrent.FutureTask.run(FutureTask.java:138)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java: 886)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

at java.lang.Thread.run(Thread.java:662)

According to mysql syntax it’s necessary to specify on which tables to apply the DELETE.

So it should work if PURGE_FOR_SIZE statement looks like

DELETE ofPubsubItem FROM ofPubsubItem LEFT JOIN (SELECT id…

6

According to mysql syntax it’s necessary to specify on which tables to apply the DELETE.

So it should work if PURGE_FOR_SIZE statement looks like

DELETE ofPubsubItem FROM ofPubsubItem LEFT JOIN (SELECT id…

Actually no. I’ve never seen SQL like “DELETE tableName FROM tableName”.

See also:

http://dev.mysql.com/doc/refman/5.1/de/delete.html

Maybe it’s the LEFT JOIN + sub select which causes the trouble!? But I am no MySQL expert. maybe you have an old version?

7

this synthax is required only for delete statement with joins

See this link:

MySQL version is 5.5.28-log, the problem shouldn’t be here

8

The query was correct before, MySQL seems to be a bit weird with deletes and joins.

The original problem here looks to be something different. The trace shows he is using hsqldb which should have caused it to use the hsqldb specific query. Instead it appears to be trying to use the general query with the weird MySQL specific syntax.

OF-789 doesn’t seem to fix the underlying problem, it just replaces the general query with one which works in hsqldb.

Now it’s broken under MySQL.

9

Here a patch for the actual release (src) 9.10.2:

https://community.igniterealtime.org/servlet/JiveServlet/downloadBody/3004-102-1 -3391/openfire_3_10_2_mysql.patch.zip

— openfire_src_3_10_2/src/java/org/jivesoftware/openfire/pubsub/PubSubPersistence Manager.java 2015-06-22 19:39:18.000000000 +0200

+++ openfire_src_3_10_2_mysql_patch/src/java/org/jivesoftware/openfire/pubsub/PubSu bPersistenceManager.java 2015-07-10 13:32:03.695879473 +0200

@@ -73,6 +73,13 @@

"ON ofPubsubItem.id = noDelete.id WHERE noDelete.id IS NULL AND " +

“ofPubsubItem.serviceID = ? AND nodeID = ?”;

  • private static final String PURGE_FOR_SIZE_MYSQL =

  •           "DELETE ofPubsubItem FROM ofPubsubItem LEFT JOIN " +

  •                   "(SELECT id FROM ofPubsubItem WHERE serviceID=? AND nodeID=? " +

  •                   "ORDER BY creationDate DESC LIMIT ?) AS noDelete " +

  •                   "ON ofPubsubItem.id = noDelete.id WHERE noDelete.id IS NULL AND " +

  •                   "ofPubsubItem.serviceID = ? AND nodeID = ?";

private static final String PURGE_FOR_SIZE_POSTGRESQL =

"DELETE from ofPubsubItem where id in " +

"(select ofPubsubItem.id FROM ofPubsubItem LEFT JOIN " +

@@ -1924,6 +1931,8 @@

{

case postgresql:

return PURGE_FOR_SIZE_POSTGRESQL;

  •           case mysql:

  •                   return PURGE_FOR_SIZE_MYSQL;

case hsqldb:

return PURGE_FOR_SIZE_HSQLDB;