Unable to archive message data due to "Cannot insert duplicate key in object 'dbo.ofMessageArchive"

Openfire Plugins
1

We using open fire with monitoring service / message archive plugin. All worked good but suddenly message archiving failed due to following error,

2019.12.03 10:08:23 org.jivesoftware.openfire.archive.ConversationManager - Unable to archive message data!
java.sql.BatchUpdateException: Violation of PRIMARY KEY constraint 'PK__ofMessag__4808B873CDE18978'. Cannot insert duplicate key in object 'dbo.ofMessageArchive'. The duplicate key value is (668).
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069)
        at sun.reflect.GeneratedMethodAccessor70.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
        at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
        at $java.sql.Statement$$EnhancerByProxool$$2689cc0d.executeBatch(<generated>)
        at org.jivesoftware.openfire.archive.ConversationManager$MessageArchivingRunnable.store(ConversationManager.java:1226)
        at org.jivesoftware.openfire.archive.ConversationManager$ArchivingRunnable.run(ConversationManager.java:1111)
        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:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

we completely stuck. This is production set up so i can’t do try and error kinda thing. What will be solution of this problem? And why it occur suddenly?

2

What version of the Monitoring plugin is this? The stack trace doesn’t appear to correspond with a few of the versions that I’ve checked.

I’m thinking this is a result of data having being deleted from the ofMessageArchive table. The code naively uses the message count as the ID for new rows.

3

Thanks guus. I also checked database records and reorder primary keys and resolved issue.

1 Like