How to Fix the Collation and Character Set of a 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
In newer versions of Confluence, database requirements become more and more stringent. This is to ensure that users get the most consistent experiences when working with content, regardless of the content in their database.
Collation in MySQL can be complicated because you can have a separate collation set at:
- The database level
- The table level
- The column level
Additionally, information inside a column may be encoded incorrectly as well - causing the data in that column to be displayed incorrectly.
If your MySQL database doesn't use the recommended collation and character sets, you can run these queries against your database to bring them into line with the Recommended Database Setup For MySQL.
Before Proceeding
Before proceeding, ensure that you:
- Shut down Confluence
- Complete a full database backup
You may also wish to apply these changes in a test environment before applying them to production.
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