Various issues are experienced after configuring Jira with MySQL 8.0 or after upgrading MySQL to 8.0
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
問題
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.