Pub-Sub Cleanup fails

Openfire
1

Just upgraded to 5.1.1. and was notified I had a lot of redundant rows that needed to be cleaned up. I started it and after a couple of minute get the message “The cleanup did not finish successfully. Check the server logs for details. Any rows already removed have been deleted; the operation can be safely run again.”
It doesn’t appear that anything was deleted at all.

Logs report:

2026.07.08 13:52:54.078 INFO [pubsub-subscription-cleanup]: org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance - Starting cleanup of redundant pubsub subscription rows.
2026.07.08 13:52:58.114 WARN [Jetty-QTP-AdminConsole-108]: org.jivesoftware.util.cache.CacheFactory - CacheFactory - Unable to find a Plugin that provides clustering support.
2026.07.08 13:52:58.115 WARN [Jetty-QTP-AdminConsole-108]: org.jivesoftware.util.cache.CacheFactory - Clustered cache factory strategy org.jivesoftware.openfire.plugin.util.cache.ClusteredCacheFactory not found
2026.07.08 13:54:13.008 **ERROR** [pubsub-subscription-cleanup]: org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance - Cleanup of redundant pubsub subscription rows failed.
java.sql.SQLException: General error
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.executeQuery(Unknown Source)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:123)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:123)
    at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.readPage(PubSubSubscriptionMaintenance.java:594)
    at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.deleteRedundantRows(PubSubSubscriptionMaintenance.java:521)
    at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.runCleanup(PubSubSubscriptionMaintenance.java:475)
    at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.hsqldb.HsqlException: General error
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.result.Result.newErrorResult(Unknown Source)
    at org.hsqldb.result.Result.newErrorResult(Unknown Source)
    at org.hsqldb.StatementDMQL.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 8 more
2026.07.08 13:55:15.947 INFO [Jetty-QTP-AdminConsole-108]: org.directwebremoting.log.startup - Starting: DwrServlet v3.0.3-dev on jetty/12.0.35 / JDK 25.0.3 from IBM Corporation at
2026.07.08 13:55:16.042 INFO [Jetty-QTP-AdminConsole-108]: org.directwebremoting.log.startup - Starting: Using container abstraction org.directwebremoting.server.servlet3.Servlet30ContainerAbstraction
2026.07.08 13:55:27.309 WARN [Jetty-QTP-AdminConsole-66]: org.jivesoftware.util.cache.CacheFactory - CacheFactory - Unable to find a Plugin that provides clustering support.
2026.07.08 13:55:27.309 WARN [Jetty-QTP-AdminConsole-66]: org.jivesoftware.util.cache.CacheFactory - Clustered cache factory strategy org.jivesoftware.openfire.plugin.util.cache.ClusteredCacheFactory not found
2026.07.08 13:55:38.352 INFO [pubsub-subscription-cleanup]: org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance - Starting cleanup of redundant pubsub subscription rows.
2026.07.08 13:55:42.369 WARN [Jetty-QTP-AdminConsole-226]: org.jivesoftware.util.cache.CacheFactory - CacheFactory - Unable to find a Plugin that provides clustering support.
2026.07.08 13:55:42.370 WARN [Jetty-QTP-AdminConsole-226]: org.jivesoftware.util.cache.CacheFactory - Clustered cache factory strategy org.jivesoftware.openfire.plugin.util.cache.ClusteredCacheFactory not found
2026.07.08 13:56:42.568 **ERROR** [pubsub-subscription-cleanup]: org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance - Cleanup of redundant pubsub subscription rows failed.
java.sql.SQLException: General error
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.executeQuery(Unknown Source)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:123)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:123)
    at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.readPage(PubSubSubscriptionMaintenance.java:594)
    at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.deleteRedundantRows(PubSubSubscriptionMaintenance.java:521)
    at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.runCleanup(PubSubSubscriptionMaintenance.java:475)
    at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.hsqldb.HsqlException: General error
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.result.Result.newErrorResult(Unknown Source)
    at org.hsqldb.result.Result.newErrorResult(Unknown Source)
    at org.hsqldb.StatementDMQL.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 8 more
Openfire 5.1.1 .deb — new admin page (PubSub subscription management) fails to open; file missing from package
2

Hi Bill! That’s an annoyingly generic error message that HSQLDB decided to throw there. It’s not really giving much more information than ‘something is wrong’ - which isn’t terribly helpful.

Are you comfortable sharing your database with me privately, so that I can try and reproduce this on my end? That’s probably the most pragmatic approach - but would hinge on me getting access to private data, which for very good reasons may not be acceptable to you.

3

Hey Guus, I’ve got no problem sharing it. What file(s) exactly do you need from the db folder? openfire.lobs is over 2GB. Thanks.

Bill

4

I’m not sure actually. It’s probably best to just send over the whole ‘embedded’ directory. Can you make that available somehow?

5

Sent you a PM with details on how to grab the DB.

1 Like
6

For what it’s worth: I’m still looking into this. I’m starting to wonder if we’ve somehow hit a bug in HSQLDB itself. More on this later.

7

I have raised a ticket in our bugtracker for this, here: Jira

8

I encountered the exact same issue with the PubSub cleanup error.
JVM memory usage was over 30 GB.
Based on PubSub maintenance analysis, we had more than 20 million rows.
Here is what I did:
I should mention right away that I might have broken something regarding future server operations, but I haven’t noticed any functional errors, and everything is running perfectly.

  • Opened the Openfire admin web page.
  • Opened the DB access tool.
  • Executed the command TRUNCATE TABLE ofPubsubSubscription;
  • Then executed the command CHECKPOINT;
  • After restarting the Openfire server, memory usage dropped to less than 1.2 GB.

Now, four days later, there are no PubSub errors in the server logs, and everything is working great.
This solution might help someone else, though it is possible that this approach is not the correct way to handle the database.

9

I cannot stress enough how truncating the entire table is a bad idea. The entire point of the cleanup is to remove only the ‘wrong’ rows from that table, not all of them!

A fix for the HSQLDB-related issue is in the works. It should be available in the next release of Openfire.

1 Like
10

I completely agree.
I had a full copy of the virtual machine (running Openfire + HSQLDB) before executing the commands. I just wanted to see if it might help.
Since I don’t fully understand the purpose of PubSub - and given that the service was disabled from the start, yet PubSub-related errors and warnings kept appearing constantly.
I am now considering migrating from HSQLDB to PostgreSQL.
Could you tell me if there is any up-to-date documentation on how to migrate data from HSQLDB to PostgreSQL?

11

PubSub is a publish-subscribe functionality that is used in multiple ways be clients. If PubSub is disabled, that typically refers (only) to the public Pub/Sub service that’s hosted by Openfire. However, modern clients will typically also make use of PubSub functionality through “personal eventing”, for functionality including but not limited to things like bookmarks, end-to-end encryption and avatar distribution.

Running with a stand-alone database, rather than the embedded one, can have benefits. We generally recommend doing that. I do not believe that documentation exists for a full migration of the database though.