Pub-Sub Cleanup fails

Bill_Roland · July 8, 2026, 6:02pm

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

guus · July 10, 2026, 9:09am

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.

Bill_Roland · July 10, 2026, 5:27pm

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

guus · July 11, 2026, 5:23pm

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

Bill_Roland · July 13, 2026, 1:43am

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

guus · July 15, 2026, 10:13am

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.

guus · July 15, 2026, 3:23pm

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

Vladimir_Grigoriev · July 16, 2026, 10:43am

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.

guus · July 16, 2026, 10:47am

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.

Vladimir_Grigoriev · July 16, 2026, 11:18am

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?

guus · July 16, 2026, 11:22am

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.

Bill_Roland · August 20, 2026, 2:46am

FWIW, OF-3338 is not fixed. It bombs out same as before, although I will say it fails much quicker now.

2026.08.17 11:24:45.559 ERROR \[pubsub-subscription-cleanup\]: org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance - Cleanup of redundant pubsub subscription rows failed.
java.sql.SQLSyntaxErrorException: data type cast needed for parameter or null literal in statement \[SELECT serviceID, nodeID, id FROM ofPubsubSubscription WHERE serviceID NOT IN (?) AND ((serviceID, nodeID, id) > (?, ?, ?)) ORDER BY serviceID, nodeID, id LIMIT 1000\]
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:738)
at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:738)
at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.readPage(PubSubSubscriptionMaintenance.java:598)
at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.deleteRedundantRows(PubSubSubscriptionMaintenance.java:538)
at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.runCleanup(PubSubSubscriptionMaintenance.java:492)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.hsqldb.HsqlException: data type cast needed for parameter or null literal
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.Statement.setParameterMetaData(Unknown Source)
at org.hsqldb.StatementDMQL.setDatabaseObjects(Unknown Source)
at org.hsqldb.StatementQuery.(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)

pup_zg · September 7, 2026, 3:21pm

Hello,

I am using Openfire 5.1.2 with the embedded HSQLDB database.

In Openfire 5.1.1, the “Redundant publish-subscribe subscription data” cleanup failed with:

java.sql.SQLException: General error
Caused by: org.hsqldb.HsqlException: General error

at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.readPage(...)

I upgraded to Openfire 5.1.2 because of the OF-3337 / OF-3338 fixes related to PubSub subscription cleanup on HSQLDB.

Unfortunately, the cleanup still fails, but now with a different HSQLDB error:

2026.09.07 10:34:03.673 ERROR [pubsub-subscription-cleanup]:
org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance -
Cleanup of redundant pubsub subscription rows failed.

java.sql.SQLSyntaxErrorException:
data type cast needed for parameter or null literal in statement
[SELECT serviceID, nodeID, id
 FROM ofPubsubSubscription
 WHERE serviceID NOT IN (?)
 AND ((serviceID, nodeID, id) > (?, ?, ?))
 ORDER BY serviceID, nodeID, id
 LIMIT 1000]

at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:738)
at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.readPage(PubSubSubscriptionMaintenance.java:598)
at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.deleteRedundantRows(PubSubSubscriptionMaintenance.java:538)
at org.jivesoftware.openfire.pubsub.PubSubSubscriptionMaintenance.runCleanup(PubSubSubscriptionMaintenance.java:492)
at java.base/java.lang.Thread.run(Thread.java:1583)

Caused by: org.hsqldb.HsqlException:
data type cast needed for parameter or null literal

The ofPubsubSubscription table is very large. I analyzed the embedded openfire.script file without modifying the database.

Results:

TotalRecords     : 1260051
UniqueGroups     : 8401
DuplicateGroups  : 1368
RedundantRecords : 1251650
LargestGroup     : 1980

The table schema is:

CREATE MEMORY TABLE PUBLIC.OFPUBSUBSUBSCRIPTION(
 SERVICEID VARCHAR(100) NOT NULL,
 NODEID VARCHAR(100) NOT NULL,
 ID VARCHAR(100) NOT NULL,
 JID VARCHAR(1024) NOT NULL,
 OWNER VARCHAR(1024) NOT NULL,
 STATE VARCHAR(15) NOT NULL,
 DELIVER INTEGER NOT NULL,
 DIGEST INTEGER NOT NULL,
 DIGEST_FREQUENCY INTEGER NOT NULL,
 EXPIRE CHARACTER(15),
 INCLUDEBODY INTEGER NOT NULL,
 SHOWVALUES VARCHAR(30) NOT NULL,
 SUBSCRIPTIONTYPE VARCHAR(10) NOT NULL,
 SUBSCRIPTIONDEPTH INTEGER NOT NULL,
 KEYWORD VARCHAR(200),
 CONSTRAINT OFPUBSUBSUBSCRIPTION_PK
 PRIMARY KEY(SERVICEID,NODEID,ID)
)

The embedded database file openfire.script is approximately 354 MB.

It appears that Openfire 5.1.2 now uses the row-value comparison:

(serviceID, nodeID, id) > (?, ?, ?)

but HSQLDB cannot determine the data types of these parameters and fails while preparing the statement with:

data type cast needed for parameter or null literal

Could this be another HSQLDB-specific issue in PubSubSubscriptionMaintenance.readPage() after the OF-3337 / OF-3338 changes?

Would explicit casts of the parameters (for example to VARCHAR(100)) be required for HSQLDB, or should Openfire use a different pagination query for the embedded database?

I have not manually modified the database or truncated ofPubsubSubscription, as I would prefer to use Openfire’s built-in cleanup mechanism.

Thank you.

Bill_Roland · September 22, 2026, 1:51pm

The vibe I’m getting here is probably the best idea is to just truncate and call it a day.

Bill_Roland · September 29, 2026, 7:43pm

In case anybody is interested, the actual fix is as follows:

Take a backup.
Stop Openfire.
Copy the whole embedded-db folder.
Open embedded-db-viewer.
Loop this until it reports 0 rows affected:

SQL

DELETE FROM ofPubsubSubscription
WHERE serviceID <> 'pubsub'
  AND id IN (
    SELECT id FROM (
      SELECT s.id
      FROM ofPubsubSubscription s
      WHERE s.serviceID <> 'pubsub'
        AND NOT EXISTS (
          SELECT 1
          FROM (
            SELECT serviceID, nodeID, jid, owner, subscriptionType, MAX(id) AS keep_id
            FROM ofPubsubSubscription
            WHERE serviceID <> 'pubsub'
            GROUP BY serviceID, nodeID, jid, owner, subscriptionType
          ) k
          WHERE k.serviceID = s.serviceID
            AND k.nodeID = s.nodeID
            AND k.jid = s.jid
            AND k.owner = s.owner
            AND k.subscriptionType = s.subscriptionType
            AND k.keep_id = s.id
        )
      LIMIT 5000
    ) x
  )

CHECKPOINT

SHUTDOWN COMPACT

Restart OpenFire.