How to Fix the Collation and Character Set of a MySQL Database

What is Collation?

The collation determines how results are sorted and ordered. In newer versions of Atlassian applications, collation changes may become more strict - i.e, an application requires a certain collation. You must ensure your database has the correct collation for the application it will be used with.

Collation in MySQL can be complicated because you can have a separate collation set at:

1. The database level
2. The table level
3. The column level

Additionally, the information inside a column may be encoded incorrectly as well - causing the data in that column to be displayed incorrectly.

Which collation can you use?

Not all versions of Jira and Confluence support utf8mb4 (which provides support for 4-btye characters). You may need to use utf8.

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;

Changing the database 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

To change the database collation:

ALTER DATABASE <yourDB> CHARACTER SET <charset> COLLATE <collation>

Changing table collation

The following query will produce a series of ALTER TABLE statements, which you must then run against your database. 

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

To change table collation:

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>'
);

Changing column collation

The following queries (one for varchar columns, and one for non-varchar columns) will produce a series of ALTER TABLE statements, which you must then run against your database. 

In the examples below, change: 

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

To change column collation for varchar columns:

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

To change column collation for non-varchar columns:

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

Server character set considerations for Confluence

If you want to use utf8mb4, and character_set_server  is not set to utf8mb4  in the my.cnf  or my.ini  file on your MySQL Server and you can't change this (for example utf8 is required for a database used by another application) you will need to add the connectionCollation=utf8mb4_bin parameter to your connection URL in order to use utf8mb4. See the Connector/J 8.0 or Connector/J 5.1 documentation for more information. 

<property name="hibernate.connection.url">jdbc:mysql://yourhost:3306/confluence?connectionCollation=utf8mb4_bin</property>