Support Health Check fail for Collation and Character Set using MySQL database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Problem
Confluence is using MySQL database and may be exhibiting the following issue:
- Healtcheck fails with the following problems
- atlassian-confluence.log logs the following errors:
WARN [HealthCheckWatchdog:thread-*] [troubleshooting.healthcheck.concurrent.SupportHealthCheckTask] healthCheckError Unable to complete execution of health check Character Set due to an exception
java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLException: Tablespace '`confluence`.`supporthealthchecks`' exists.
...
WARN [HealthCheckWatchdog:thread-*] [troubleshooting.healthcheck.concurrent.SupportHealthCheckTask] healthCheckError Unable to complete execution of health check Collation due to an exception
...
Caused by: java.lang.RuntimeException: java.sql.SQLException: Tablespace '`confluence`.`supporthealthchecks`' exists.
...
Caused by: java.sql.SQLException: Tablespace '`confluence`.`supporthealthchecks`' exists.
...
or
WARN [HealthCheckWatchdog:thread-4] [troubleshooting.healthcheck.concurrent.SupportHealthCheckTask] healthCheckError Unable to complete execution of health check Character Set due to an exception
java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLException: Can't create table 'supporthealthchecks' (errno: 13)or
WARN [HealthCheckWatchdog:thread-4] [troubleshooting.healthcheck.concurrent.SupportHealthCheckTask] healthCheckError Unable to complete execution of health check Collation due to an exception
java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLException: Can't create table 'supporthealthchecks' (errno: 13)MySQL log will show the following errors:
ERROR, InnoDB: Cannot create file './EDMconfluence/supporthealthchecks.ibd'
ERROR, InnoDB: The file './EDMconfluence/supporthealthchecks.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. Have you moved InnoDB .ibd files around without using the SQL commands DISCARD TABLESPACE and IMPORT TABLE [truncated, 435 bytes total]Diagnosis
Environment
- MySQL DB server
Diagnostic Steps
- Verify that charset/collation is correctly set on DB to "utf8/utf8_bin" or "utf8mb4 / utf8mb4_bin" (available in Confluence 7.3 and later) in accordance with Database Setup For MySQL.
- Check if there is supporthealthchecks.ibd left on MySQL server with no corresponding *.frm file
- Check that MySQL Server has the correct directory/file permissions to create new tables
Cause
A residual IBD file is left on /var/lib/ for support health checks, i.e. /var/lib/mysql/confluence/supporthealthchecks.ibd but there is no corresponding FRM file to accommodate it, so MySQL could not show the table.
MySQL does not have the correct directory/file system permissions to create the new table files on disk.
Resolution
- Move supporthealthchecks.ibd file to another directory (do not delete)
- Check and correct the MySQL data directory (SHOW VARIABLES WHERE Variable_Name="datadir") file permissions are correct for the Confluence Database
- Restart Confluence