Issue Summary

Upgrading to Jira 7.13.4 and performing a clean install using our development database. created a new user and granted it the limited permissions as specified in the install instructions. When the database is being setup the following error is present in the atlassian-jira.log

2019-07-05 15:26:22,045 http-nio-8081-exec-12 ERROR anonymous 920x6x1 fah0iu 0:0:0:0:0:0:0:1 /secure/SetupDatabase.jspa [c.a.j.p.d.u.devsummary.migration.DevSummaryMigrationHandler] Error while upgrading rows.
java.util.concurrent.ExecutionException: java.lang.RuntimeException: Unable to enquire table names available in the system

this is followed by several java stack traces and one Oracle error, "ORA-01427: single-row subquery returns more than one row."

Environment

• Oracle Database
• Existing production data

Steps to Reproduce

1. Connect Jira to an Oracle database with multiple schema
2. Upgrade Jira to any version after 7.6

Expected Results

Jira upgrades to the later version and rewrites the information in the database as needed

Actual Results

The upgrade fails. The below exception is thrown in the xxxxxxx.log file:

2019-07-05 15:26:22,045 http-nio-8081-exec-12 ERROR anonymous 920x6x1 fah0iu 0:0:0:0:0:0:0:1 /secure/SetupDatabase.jspa [c.a.j.p.d.u.devsummary.migration.DevSummaryMigrationHandler] Error while upgrading rows.
java.util.concurrent.ExecutionException: java.lang.RuntimeException: Unable to enquire table names available in the system
.........
Caused by: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

Notes

In our database we have a type installed in another schema which shares the same name as an Oracle type. This is from an install of ESRI ArcGIS software in the database.

Jira is trying to get a list of all the table names for some reason. When it does this it is accessing the Oracle view ALL_TAB_COLS which returns all the columns of tables that the Jira user can see including those in other schemas. It's not limited to just the tables that Jira itself has created. I won't repeat the whole query shown in the log file but it can be simplified to the following.

select t.owner, t.table_name, t.column_name, (select a.typecode from ALL_TYPES a where a.type_name = t.data_type) as data_type from ALL_TAB_COLS t;

In a default Oracle install that query will "work", but the query itself is poorly written. The type_name field is not a unique key for the ALL_TYPES table and the subquery will break as soon as you have the same type_name created in multiple schemas as we do. That query can be correctly written by adding a check for the owner to the subquery.

select t.owner, t.table_name, t.column_name, (select a.typecode from ALL_TYPES a where a.type_name = t.data_type and a.owner = t.data_type_owner) as data_type from ALL_TAB_COLS t;

Workaround

(untested)

1. Install Jira connected to an empty Oracle database
2. Move the data over as described in Switching Databases
3. Complete setup