powered by Jive Software

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

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?

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.

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

1 Like