MySQL Collation Repair: Table Level Changes

Still need help?

The Atlassian Community is here for you.

Ask the community

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

 

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 Database Tables with the incorrect character set or collation

Change 'database' to reflect the name of your database and 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 = 'database'
AND 
(
	C.CHARACTER_SET_NAME != 'utf8'
	OR
	C.COLLATION_NAME != 'utf8_bin'
);

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 utf8 COLLATE utf8_bin

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 utf8 COLLATE utf8_bin;')
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 = 'database'
AND 
(
	C.CHARACTER_SET_NAME != 'utf8'
	OR
	C.COLLATION_NAME != 'utf8_bin'
);
Last modified on Feb 19, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.