Error when starting or creating new content - ERROR: duplicate key value violates unique constraint "<tablename>_pkey"

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Symptoms

You cannot start Confluence or create content, or perform another function. This can occur after an upgrade or randomly while the system is in use. 

Some variation on the following appears in the atlassian-confluence.log:

atlassian-confluence.log
2011-07-21 11:58:11,153 ERROR [http-8080-3] [atlassian.confluence.servlet.ConfluenceServletDispatcher] sendError Could not execute action
 -- referer: http://instance | url: /json/addlabelactivity.action | userName: admin
org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not insert: [bucket.search.persistence.IndexQueueEntry#26083335]; SQL []; ERROR: duplicate key value violates unique constraint "indexqueueentries_pkey"; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "indexqueueentries_pkey"
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "indexqueueentries_pkey"
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
...
atlassian-confluence.log
-- referer: https://confluence.mycompany.com.au/display/cspub/Page+Name | url: /pages/copypage.action | userName: userx | action: copypage
2015-09-02 21:47:44,468 ERROR [http-nio-443-exec-5] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Violation of PRIMARY KEY constraint 'PK__CONTENT__1317CAA1114E8C1D'. Cannot insert duplicate key in object 'CONTENT'. The duplicate key value is (9469953).

Cause and Diagnosis

This is caused by a problem with the hibernate_unique_key. This value is used to determine the next block of unique id numbers to create in the database. Most of Confluence's tables use an id number as the primary key, and that number is determined by using the value in the hibernate_unique_key and applying a formula to it, and then incrementing it. This error can therefore occur for one of two reasons:

  1. There are two entries for the hibernate_unique_key
  2. The hibernate_unique_key is set to a value that is too low

To determine which is these is at fault, run this query against the database;

select * from hibernate_unique_key;

There should be only one entry in this table, and the number must be higher than the highest id used in the database.

(info) If neither of those cases apply, and the hibernate_unique_key value seems fine, there's a similar problem that may produce this same behavior as well, due to a different root cause:

Resolution

The resolution depends on the cause.

Solution One

If there are two entries in hibernate_unique_key, delete the lower number by issuing this command:

delete from hibernate_unique_key where next_hi = <the lower number>;

Solution Two

If there is only one entry, we need to make Confluence calculate the correct value and reset it. There are two way to do this. Please test this in a testing environment first.

  1. Go to General Configuration > Backup & Restore and make an XML backup with attachments.
  2. Copy the XML backup zip from atlassian_home/temp to atlassian_home/restore
  3. Go to General Configuration > Backup & Restore again, and scroll to the bottom
  4. Choose the XML backup zip file and click Restore

The result will be that Confluence will regenerate a valid next_hi value.

or

  1. On the production instance, go to General Configuration > Backup & Restore and make an XML backup (no attachments.)
  2. Copy the XML backup zip from atlassian_home/temp to atlassian_home/restore on a test instance
  3. On the test instance, go to General Configuration > Backup & Restore, and scroll to the bottom
  4. Choose the XML backup zip file and click Restore
  5. Run select * from hibernate_unique_key; on the test instance database, and save the next_hi value
  6. Shut down your production Confluence instance
  7. In your production database server, run update hibernate_unique_key set next_hi = <value>; where <value> is the value from the test database server
  8. Restart Confluence.

Last modified on Jun 12, 2020

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.