MySQL Collation Repair: Table Level Changes
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, 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 Database Tables with the incorrect character set or collation
In the example below, change:
<yourDB>to your actual database name
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>' );