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.

Before Proceeding

Identifying Database Tables with the incorrect character set or collation

In the example below, change: 

• <yourDB> to your actual database name
• <charset> to either utf8 or utf8mb4
• <collation> to either utf8_bin or utf8mb4_bin

Run the following query:

SELECT T.TABLE_NAME, C.CHARACTER_SET_NAME, C.COLLATION_NAME
FROM information_schema.TABLES AS T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = '<yourDB>'
AND 
(
	C.CHARACTER_SET_NAME != '<charset>'
	OR
	C.COLLATION_NAME != '<collation>'
);

Adjusting the collation and character set

To fix a single table, run the following query against your database - change 'tableName' to suit:

ALTER TABLE `tableName` CHARACTER SET <charset> COLLATE <collation>

To alter all tables in the database, you'll need to generate an ALTER TABLE query for each table that isn't correctly set. The following script will produce a collection of those queries. Adjust 'database' to reflect your database name:

SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET <charset> COLLATE <collation>;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = '<yourDB>'
AND 
(
	C.CHARACTER_SET_NAME != '<charset>'
	OR
	C.COLLATION_NAME != '<collation>'
);