There is a system error when accessing System Information or Scheduled Jobs in Confluence
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
When accessing System Information or Scheduled Jobs in Confluence the following system error page is shown
Environment
7.13.0
Diagnosis
Confluence log shows errors relating to a problem with the start_time_outcome_idx index:
2022-10-06 12:32:45,002 ERROR Caesium-1-1 engine.jdbc.spi.SqlExceptionHelper logExceptions ERROR: index "start_time_outcome_idx" is not a btree
2022-10-06 12:32:45,003 ERROR Caesium-1-1 org.hibernate.internal.ExceptionMapperStandardImpl mapManagedFlushFailure HHH000346: Error during managed flush could not execute statement
2022-10-06 12:32:45,003 WARN Caesium-1-1 confluence.impl.hibernate.ConfluenceHibernateTransactionManager doCommit Commit failed. Rolling back. Error: Hibernate operation: could not execute statement; uncategorized SQLException; SQL state XX002; error code 0; ERROR: index "start_time_outcome_idx" is not a btree; nested exception is org.postgresql.util.PSQLException: ERROR: index "start_time_outcome_idx" is not a btree
2022-10-06 12:32:45,003 WARN Caesium-1-1 confluence.impl.hibernate.ConfluenceHibernateTransactionManager doRollback Performing rollback. Transactions:
->com.atlassian.confluence.impl.schedule.caesium.SchedulerRunDetailsDao.addRunDetails: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #1297521148)
2022-10-06 12:32:45,003 ERROR Caesium-1-1 scheduler.caesium.impl.SchedulerQueueWorker executeJob Unhandled exception thrown by job QueuedJobjobId=conversionQueueMonitor,deadline=1665048765000
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute statement; uncategorized SQLException; SQL state XX002; error code 0; ERROR: index "start_time_outcome_idx" is not a btree; nested exception is org.postgresql.util.PSQLException: ERROR: index "start_time_outcome_idx" is not a btree
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
at org.springframework.orm.hibernate.HibernateTransactionManager.convertJdbcAccessException(HibernateTransactionManager.java:619)
Cause
A corrupt start_time_outcome_idx index is the cause of this issue. Although the reason for the corruption was unknown it has been seen to occur after an upgrade.
Solution
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
- Shutdown Confluence.
Run the following query to drop the start_time_outcome_idx index:
drop index start_time_outcome_idx;
Recreate the index:
create index start_time_outcome_idx on scheduler_run_details (start_time, outcome);
- Once complete restart confluence.
- Check if "System Information" and "Scheduled Jobs" now displays without error.