問題

When setting up Jira 8.15+ with MySQL 8, or when upgrading the MySQL DB server from an earlier version to version 8, various problems are experienced in the front-end and/or in the logs Active Objects warnings can appear when initializing the startup as well as afterward.

• Cannot view a project

• Cannot view an issue

• Plugin functionalities may be failing

• Unable to connect to MySQL from the Jira setup wizard

The following errors could be present in the atlassian-jira.log:

java.sql.SQLException: Cannot drop index 'index_ao_60db71_est1680565966': needed in a foreign key constraint
2021-06-07 16:31:29,113+0300 active-objects-init-JiraTenantImpl{id='system'}-0 WARN anonymous     [n.j.ao.db.MySQLDatabaseProvider] Error in schema creation: Cannot drop index 'index_ao_60db71_est1680565966': needed in a foreign key constraint; attempting to roll back last partially generated table
java.sql.SQLException: Cannot drop index 'index_ao_60db71_est1680565966': needed in a foreign key constraint
Caused by: java.sql.SQLException: Cannot drop index 'index_ao_60db71_est1680565966': needed in a foreign key constraint
java.sql.SQLException: Cannot drop index 'index_ao_82b313_abi1495113378': needed in a foreign key constraint
2021-06-07 16:31:38,149+0300 active-objects-init-JiraTenantImpl{id='system'}-0 WARN anonymous     [n.j.ao.db.MySQLDatabaseProvider] Error in schema creation: Cannot drop index 'index_ao_82b313_abi1495113378': needed in a foreign key constraint; attempting to roll back last partially generated table
java.sql.SQLException: Cannot drop index 'index_ao_82b313_abi1495113378': needed in a foreign key constraint
Caused by: java.sql.SQLException: Cannot drop index 'index_ao_82b313_abi1495113378': needed in a foreign key constraint
java.sql.SQLException: Cannot drop index 'index_ao_d9132d_sce1227110052': needed in a foreign key constraint
2021-06-07 16:31:52,674+0300 active-objects-init-JiraTenantImpl{id='system'}-0 WARN anonymous     [n.j.ao.db.MySQLDatabaseProvider] Error in schema creation: Cannot drop index 'index_ao_d9132d_sce1227110052': needed in a foreign key constraint; attempting to roll back last partially generated table
java.sql.SQLException: Cannot drop index 'index_ao_d9132d_sce1227110052': needed in a foreign key constraint
Caused by: java.sql.SQLException: Cannot drop index 'index_ao_d9132d_sce1227110052': needed in a foreign key constraint
java.sql.SQLException: Cannot drop index 'index_ao_a415df_aos495640191': needed in a foreign key constraint
2021-06-07 16:32:00,156+0300 active-objects-init-JiraTenantImpl{id='system'}-0 WARN anonymous     [n.j.ao.db.MySQLDatabaseProvider] Error in schema creation: Cannot drop index 'index_ao_a415df_aos495640191': needed in a foreign key constraint; attempting to roll back last partially generated table

診断

環境

• Jira Version: 8.15.0+

• Database Version: MySQL 8

• MySQL 8 DB and user were created according to instructions from: https://confluence.atlassian.com/jiracore/connecting-jira-to-mysql-8-0-1018272102.html

Diagnostic Steps

• Check application logs for errors containing "Cannot drop index"
• When using the Jira setup wizard and attempting to connect to MySQL you receive the following error "You have specified a database that is not empty"

原因

• This problem happens when there are multiple schemas to which the Jira DB user has full privileges, such as giving the Jira DB user privileges to a restored separate schema or having the Jira DB user with full privileges to multiple Jira DBs 

• The problem is related to how MySQL 8 constructs the object catalogs for the privileged user

回避策

Workaround is to ensure that each Jira DB user has privileges to only one schema in MySQL 8:

• First, stop Jira and back up your MySQL system tables.
• Access MySQL server with dba permissions

• Check for user GRANTs:

  mysql> SHOW GRANTS FOR 'jira'@'localhost';

• If the 'jira' user is granted to more than one schema, remove the user from additional schemas, leaving only the current Jira DB schema privileges. N.B. Replace '<other_schema>' with the name of the other schema:

mysql> REVOKE ALL PRIVILEGES ON <other_schema>.* FROM 'jira'@'localhost';
mysql> FLUSH PRIVILEGES;

• Confirm that correct PRIVILEGES are showing for Jira DB users to only one schema. 
• Start up Jira and check the logs for any related errors

Another option: 

We strongly recommend performing the upgrade in a test environment before upgrading your production site

• Rebuilding a new Database Server
• Create new Database Users (aside from the admin user created during the Database Server creation)
  • Goal: The user connecting and executing queries should only have access to the desired DB for that environment.
• Re-import the Database from the production environment;
• Use a different Database user for each database/environment.

ソリューション

The resolution is to restrict Jira DB user privileges to only one schema. For further help with updating/verifying this in MySQL DB server, dba access and knowledge will be required.