Jira operations may fail with an SQL exception
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 using Jira with an Oracle database with multiple schemas, some operations may persistently fail with an SQL exception.
This problem has been reported as occurring during an upgrade from Jira 7.13 to Jira 8, and has also been seen when transitioning issues in Jira 8. In this latter case, the following error is observed in the UI: org.ofbiz.core.entity.GenericTransactionException: Commit failed, rollback previously requested by nested transaction.
Environment
- Jira 7.x
- Jira 8.x
- Oracle database with 12.1.0.2 or 12.2.0.1 driver
Diagnosis
This specific problem can be diagnosed by the presence of these messages in the atlassian-jira.log
java.lang.RuntimeException: Unable to enquire table names available in the system
at com.atlassian.pocketknife.internal.querydsl.schema.DefaultSchemaProvider.tableColumnNames(DefaultSchemaProvider.java:170)
at com.atlassian.pocketknife.internal.querydsl.util.MemoizingResettingReference.lambda$get$6(MemoizingResettingReference.java:59)
at com.atlassian.pocketknife.internal.querydsl.util.MemoizingResettingReference$SmarterMemoizingSupplier.get(MemoizingResettingReference.java:150)
at com.atlassian.pocketknife.internal.querydsl.util.MemoizingResettingReference.safelyGetT(MemoizingResettingReference.java:71)
at com.atlassian.pocketknife.internal.querydsl.util.MemoizingResettingReference.get(MemoizingResettingReference.java:63)
at com.atlassian.pocketknife.internal.querydsl.schema.DefaultSchemaProvider.prime(DefaultSchemaProvider.java:43)
at com.atlassian.pocketknife.spi.querydsl.DefaultDialectConfiguration.getDialectConfig(DefaultDialectConfiguration.java:67)
at com.atlassian.pocketknife.internal.querydsl.DatabaseConnectionConverterImpl.getDialectConfig(DatabaseConnectionConverterImpl.java:51)
at com.atlassian.pocketknife.internal.querydsl.DatabaseConnectionConverterImpl.convertImpl(DatabaseConnectionConverterImpl.java:44)
at com.atlassian.pocketknife.internal.querydsl.DatabaseConnectionConverterImpl.convertExternallyManaged(DatabaseConnectionConverterImpl.java:39)
at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$18(DatabaseAccessorImpl.java:54)
at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:87)
at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.lambda$execute$0(DefaultTransactionalExecutor.java:43)
at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.lambda$borrowConnectionAndExecute$0(JiraHostConnectionAccessor.java:95)
at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:74)Caused by: java.sql.SQLException: ORA-01427: single-row subquery returns more than one rowCause
The same Oracle object type name is in use in multiple schemas. The Oracle driver performs a global search of type names. If duplicate type names are returned the driver throws an SQL exception. See this Oracle support document for more information.
Solution
The problem can be resolved by renaming or removing duplicate object type names. Please contact Oracle to identify other resolutions.