powered by Jive Software

SQL Integrity Violations in fresh install of 4.7.1

Hello,

I did a fresh install of Openfire 4.7.1 using mariadb/mysql as the DB. I imported ~400 users from our old 4.0.2 server.

As soon as I logged in to test, the logs began getting flooded with errors much like this one:

2022.05.12 16:22:56 ERROR [TaskEngine-pool-547]: org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider - An exception occurred while creating an affiliation (org.jivesoftware.openfire.pubsub.NodeAffiliate@47099879 - JID: jortego@ms1-openfire.jancer-jns.com - Affiliation: none) to a node (UniqueIdentifier{serviceId='jbrown@ms1-openfire.jancer-jns.com', nodeId='jbrown@ms1-openfire.jancer-jns.com'}) in the database.
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'jbrown@ms1-openfire.jancer-jns.com-jbrown@ms1-openfire.jancer-jn' for key 'PRIMARY'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) ~[mysql-connector-java-8.0.28.jar:8.0.28]
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.28.jar:8.0.28]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.28.jar:8.0.28]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1098) ~[mysql-connector-java-8.0.28.jar:8.0.28]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1046) ~[mysql-connector-java-8.0.28.jar:8.0.28]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1371) ~[mysql-connector-java-8.0.28.jar:8.0.28]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1031) ~[mysql-connector-java-8.0.28.jar:8.0.28]
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) ~[commons-dbcp2-2.9.0.jar:2.9.0]
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) ~[commons-dbcp2-2.9.0.jar:2.9.0]
        at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider.createAffiliation(DefaultPubSubPersistenceProvider.java:1021) ~[xmppserver-4.7.1.jar:4.7.1]
        at org.jivesoftware.openfire.pubsub.CachingPubsubPersistenceProvider.process(CachingPubsubPersistenceProvider.java:383) ~[xmppserver-4.7.1.jar:4.7.1]
        at java.util.ArrayList.forEach(ArrayList.java:1259) [?:1.8.0_322]
        at org.jivesoftware.openfire.pubsub.CachingPubsubPersistenceProvider.flushPendingNodes(CachingPubsubPersistenceProvider.java:143) [xmppserver-4.7.1.jar:4.7.1]
        at org.jivesoftware.openfire.pubsub.CachingPubsubPersistenceProvider.flushPendingChanges(CachingPubsubPersistenceProvider.java:540) [xmppserver-4.7.1.jar:4.7.1]
        at org.jivesoftware.openfire.pubsub.CachingPubsubPersistenceProvider$1.run(CachingPubsubPersistenceProvider.java:94) [xmppserver-4.7.1.jar:4.7.1]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_322]
        at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_322]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_322]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_322]
        at java.lang.Thread.run(Thread.java:750) [?:1.8.0_322]

I have tried disabling both PubSub and PEP as I do not need either function, but the errors continue to rapidly accrue.

Is this by any chance a known issue? I did some searching but did not find a conclusive answer.

Sorry to hear that you’re running into an issue. It has been reported before, as [OF-2437] - Ignite Realtime Jira

A workaround for this issue is to replace the pubsub persistence provider (which defaults to org.jivesoftware.openfire.pubsub.CachingPubsubPersistenceProvider in a non-clustered environment) with org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider.

This can be done by configuring the property provider.pubsub-persistence.className with value org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider