MySQL Collation Repair: Column 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 Columns with the incorrect character set or collation:

Change 'database' to reflect the name of your database and run the following query:

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'database' 
AND 
(
	CHARACTER_SET_NAME != 'utf8'
	OR
	COLLATION_NAME != 'utf8_bin'
);

Adjusting the collation and character set of varchar columns

To adjust all varchar columns in a database, you'll need to first identify those columns. The following script will generate an alter table statement for each column that is varchar and is using the incorrect collation or character set. Change 'database' to reflect the name of your database and run the following query:

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_bin', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'database'
AND DATA_TYPE = 'varchar'
AND 
(
	CHARACTER_SET_NAME != 'utf8'
	OR
	COLLATION_NAME != 'utf8_bin'
);

Adjusting the collation and character set of non varchar columns

To adjust the non-varchar columns in the database to use the correct collation, you'll need to first identify those columns. The following script will generate an alter table statement for each column that is not varchar and is using the incorrect collation or character set. Change 'database' to reflect the name of your database and run the following query:

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET UTF8 COLLATE utf8_bin', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'database'
AND DATA_TYPE != 'varchar'
AND 
(
	CHARACTER_SET_NAME != 'utf8'
	OR
	COLLATION_NAME != 'utf8_bin'
);

Dealing with Foreign Key Constraints

It may be necessary to ignore foreign key constraints when making changes to a large number of columns. You can use the SET FOREIGN_KEY_CHECKS command to ignore foreign key constraints while you update the database.

SET FOREIGN_KEY_CHECKS=0;

-- Insert your other SQL Queries here...

SET FOREIGN_KEY_CHECKS=1;
Last modified on Feb 19, 2016

Was this helpful?

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