MySQL Collation Repair: Column Level Encoding Issues
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
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.
When to apply this fix
If you notice that your content is not displaying correctly, you should apply this fix. For example, if you have the string José that is being displayed as José, that is an example of when to apply this fix.
Repairing a column with incorrectly encoded content
You'll need to select the column converting to the previousEncoding (such as latin1) and cast it to binary; then to utf8.
Setting SQL_SAFE_UPDATES to 0 before issuing the query allows you to update each row in the table. This is potentially unsafe, and should be tested thoroughly before being applied to a production system.
SET SQL_SAFE_UPDATES=0;
UPDATE tableName SET columnName = CONVERT(CAST(CONVERT(columnName USING previousEncoding) AS BINARY) USING utf8);
SET SQL_SAFE_UPDATES=1;Depending on the character and how it's encoded, this query may not be enough to resolve the encoding issues you're seeing. You may wish to try one of the alternative methods outlined in the "Further Reading" section below.