MySQL Collation Repair: Database Level Changes
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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
This document is part of the guide on How to Fix the Collation and Character Set of a MySQL Database. Please refer to that page for more information.
Table of Contents
- MySQL Collation Repair: Database Level Changes
- MySQL Collation Repair: Table Level Changes
- MySQL Collation Repair: Column Level Changes
- MySQL Collation Repair: Column Level Encoding Issues
- MySQL Collation Repair: Case Study - Repairing a Production Database
Which collation can you use?
Not all versions of Confluence support utf8mb4 (which provides support for 4-btye characters). You may need to use utf8.
| Can use utf8mb4 | Must use utf8 |
|---|---|
|
|
Before Proceeding
Before proceeding, ensure that you:
- Have shut down Confluence
- Have completed a full database backup
You may also wish to apply these changes in a test environment before applying them to production.
Identifying the Collation and Character set of your database
In the example below, change:
<yourDB>to your actual database name<charset>to eitherutf8orutf8mb4<collation>to eitherutf8_binorutf8mb4_bin
Run the following query:
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA S
WHERE schema_name = '<yourDB>'
AND
(
DEFAULT_CHARACTER_SET_NAME != '<charset>'
OR
DEFAULT_COLLATION_NAME != '<collation>'
);This query will return a list of each database that is not using the correct character set and collation.
Fixing the collation for the database
In the example below, change:
<yourDB>to your actual database name<charset>to eitherutf8orutf8mb4<collation>to eitherutf8_binorutf8mb4_bin
Run the following query:
ALTER DATABASE <yourDB> CHARACTER SET <charset> COLLATE <collation>Here's an example:
ALTER DATABASE confluence CHARACTER SET utf8mb4 COLLATE utf8mb4_bin