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.

Setup Guides for MySQL

To setup your MySQL database correctly, see the following resources for each product:

データベースの変更を行う場合は必ず事前にバックアップを取得してください。

Note for Confluence: Due to CONFSERVER-18509 - Getting issue details... STATUS , the utf8mb4 will not work for Confluence. Use utf8 instead as stated in this documentation.

You may wish to add all the ALTER TABLE statements to a single file for easier execution.

Changing the Database Collation

Change yourDB to suit your database name.

For MySQL version up to 5.6:

ALTER DATABASE yourDB CHARACTER SET utf8 COLLATE utf8_bin

For MySQL version 5.7 and JIRA 8.x and higher (7.x and below do not support 4 byte characters):

ALTER DATABASE yourDB CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

Changing Table Collation

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

For MySQL version up to 5.6:

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 = 'yourDB'
AND
(
    C.CHARACTER_SET_NAME != 'utf8'
    OR
    C.COLLATION_NAME != 'utf8_bin'
);

For MySQL version 5.7 and JIRA 8.x and higher (7.x and below do not support 4 byte characters):

SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_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 = 'yourDB'
AND
(
    C.CHARACTER_SET_NAME != 'utf8mb4'
    OR
    C.COLLATION_NAME != 'utf8mb4_bin'
);

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. Change yourDB to suit your database name.

For MySQL version up to 5.6:

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 = 'yourDB'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);
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 = 'yourDB'
AND DATA_TYPE != 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);

For MySQL version 5.7 and JIRA 8.x and higher (7.x and below do not support 4 byte characters):

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin', (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 != 'utf8mb4'
    OR
    COLLATION_NAME != 'utf8mb4_bin'
);
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin', (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 != 'utf8mb4'
    OR
    COLLATION_NAME != 'utf8mb4_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;
説明 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.
製品 Jira, Confluence, Bamboo, Bitbucket, Fisheye
プラットフォーム サーバー
最終更新日 2019 年 8 月 22 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.