Various issues are experienced after configuring Jira with MySQL 8.0 or after upgrading MySQL to 8.0
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
Problem
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 tableDiagnosis
Environment
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"
Cause
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
Solution
Workaround
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:
1mysql> 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:
1
2
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.
Resolution
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.
Was this helpful?