How to Fix the Collation and Character Set of a MySQL Database manually
Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.
Direct database manipulation is not covered by our Atlassian Support Offerings and should be up to your DBAs discretion.
Our recommended method for migrating databases is as follows
- Create a new database with the required collation as per the appropriate documentation (for example Connecting JIRA to a Database)
- Follow our Switching Databases using an XML backup to migrate from the old database (with the incorrect collation) to the new one, with the correct collation.
If the recommended method for some reason is not suitable for your scenario, please follow this article to manually fix the collation at the database server side. After the solution is implemented, please test the application thoroughly to ensure everything works correctly and as expected.
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:
- The database level
- The table level
- 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.
Can use utf8mb4 | Must use utf8 |
---|---|
|
|
Setup Guides for MySQL
To setup your MySQL database correctly, see the following resources for each product:
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
You may wish to add all the ALTER TABLE statements to a single file for easier execution.
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 eitherutf8
orutf8mb4
<collation>
to eitherutf8_bin
orutf8mb4_bin
To change the database collation:
ALTER DATABASE <yourDB> CHARACTER SET <charset> COLLATE <collation>
Changing table collation
Please note, the query below 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 eitherutf8
orutf8mb4
<collation>
to eitherutf8_bin
orutf8mb4_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
Please note, similar to the query above, the queries below (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 eitherutf8
orutf8mb4
<collation>
to eitherutf8_bin
orutf8mb4_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.
You might also need to make sure that your collation and character set are properly defined at your my.cnf file (e.g. using character-set-server = utf8mb4 collation-server = utf8mb4_bin)