Summary

While running Confluence with a MySQL 8 database, users are unable to create pages or spaces in Confluence.

Environment

• Confluence version: 7.2.0 or higher
• Database Version: MySQL 8
  
  

Diagnosis

The Database was upgraded prior to the start of these issues. Problems aren't seen when running in MySQL 5.7 but are observed following an upgrade to MySQL 8.

Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:

Database:
- name:MySQL
- version:8.0.31
- minor version:0
- major version:8
Driver:

- name:MySQL Connector/J

- version:mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)

java.sql.SQLException: Cannot drop index 'index_ao_9412a1_use1222319987': needed in a foreign key constraint
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:53)
at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.lambda$create$0(AbstractActiveObjectsFactory.java:73

Cause

This issue stems from changes in how the schema management for users is handled in MySQL 8. If the database user has been granted full privileges on multiple schemas, this can create issues when the application attempts to perform database changes using this user account.

To validate if this is the issue, please log in to MySQL as a DB Admin and run the following SQL (Replace <db-user> with the actual database username) to confirm whether or not the database user has privileges to any additional schema:

mysql> SHOW GRANTS FOR '<db-user>'@'localhost';

If Confluence is not running on the same server, replace localhost with the hostname or IP address of the Confluence server. 

If this query returns multiple results then Confluence is very likely being impacted by this issue, and this is preventing the space and page creation. 

Solution

To address this behavior we'll want to either:

• Create a new database user with privileges limited to only the Confluence database 
• Remove the additional privileges from the current database users via the following query (Replace <db-user> with the actual database username):

REVOKE ALL PRIVILEGES ON world.* FROM '<db-user>'@'localhost';
FLUSH PRIVILEGES; 

If Confluence is not running on the same server, replace localhost with the hostname or IP address of the Confluence server.