Jira Database Operations Fail with PostgreSQL Table Index Corruption
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Jira database update operations fail with PostgreSQL table index corruption. Ldap/AD synchronization, issue creation, update, commenting fails with PostgreSQL index corruption errors.
Environment
Jira Data Center
PostgreSQL
Diagnosis
Database updates fails and PostgreSQL errors logged in the atlassian-jira.log (ERROR: table tid from new index tuple (5035,13) overlaps with invalid duplicate tuple at offset 56 of block 255 in index "idx_first_name"))
Below is an error log from LDAP/AD synchronization process.
2025-03-19 10:21:06,116+0100 Caesium-1-4 ERROR ServiceRunner [c.a.crowd.directory.DbCachingRemoteDirectory] Exception occured when performing full synchronization
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while updating: [GenericEntity:User][lastName, surname][lowerDisplayName,dispnam, name surname][displayName,surname, name surname][lowerLastName,surname][active,1][externalId,05abshhsaslwa22484893jaadaa][updatedDate,2024-05-01 13:51:56.454][userName,sn.name@atlas.com][lowerEmailAddress,sn.name@atlas.com][firstName,name][emailAddress,sn.name@atlas.com][lowerUserName,sn.name@atlas.com][createdDate,2023-04-23 03:50:17.123][credential,X][deletedExternally,null][directoryId,10100][lowerFirstName,name][id,1234567] (SQL Exception while executing the following:UPDATE public.cwd_user SET directory_id=?, user_name=?, lower_user_name=?, active=?, created_date=?, updated_date=?, first_name=?, lower_first_name=?, last_name=?, lower_last_name=?, display_name=?, lower_display_name=?, email_address=?, lower_email_address=?, CREDENTIAL=?, deleted_externally=?, EXTERNAL_ID=? WHERE ID=? (ERROR: table tid from new index tuple (5035,13) overlaps with invalid duplicate tuple at offset 56 of block 255 in index "idx_first_name"))
at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.store(DefaultOfBizDelegator.java:283)
at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.store(WrappingOfBizDelegator.java:166)
at com.atlassian.jira.crowd.embedded.ofbiz.OfBizUserDao.storeUser(OfBizUserDao.java:538)
at com.atlassian.jira.crowd.embedded.ofbiz.OfBizUserDao.update(OfBizUserDao.java:522)
at com.atlassian.jira.crowd.embedded.ofbiz.OfBizUserDao.update(OfBizUserDao.java:506)Cause
Database error is caused by a corrupt index (i.e. "idx_first_name" ) in the Error log. Look for all corrupted indexes in the Error logs.
(ERROR: table tid from new index tuple (5035,13) overlaps with invalid duplicate tuple at offset 56 of block 255 in index "idx_first_name"))Same errors should be in PostgreSQL as index corrupted
XX002 | index_corrupted |
Solution
Stop your Jira, reach out to your DBA and fix the corrupted indexes on the database.
For details please refer to PostgreSQL documentation.
Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.