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

1. Create a new database with the required collation as per the appropriate documentation (for example Connecting JIRA to a Database)
2. 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.

照合とは

照合は、結果の並べ替え順を定義します。アトラシアン アプリケーションの新しいバージョンでは、アプリケーションで特定の照合を要求するため、照合の変更が厳密になる可能性があります。データベースの照合が、それを使用するアプリケーションに適切であることを確認する必要があります。

MySQL では、次のレベルで異なる照合セットがあるため、照合が複雑になる場合があります。

1. データベース レベル
2. テーブル レベル
3. カラム レベル

また、カラム内の情報が誤った方法でエンコードされ、それによってカラムのデータが誤って表示される場合もあります。

使用可能な照合

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

外部キー制約について

大量のカラムに変更を行う際に、外部キーの制約を無視する必要がある場合があります。SET FOREIGN_KEY_CHECKS コマンドを使用して、データベースを更新するときに外部キー制約を無視できます。

SET FOREIGN_KEY_CHECKS=0;
 
-- Insert your other SQL Queries here...
 
SET FOREIGN_KEY_CHECKS=1;

データベースの照合の変更

以降の例で、次のように変更します。 

• <yourDB>: 実際のデータベース名
• <charset>: utf8 または utf8mb4
• <collation>: utf8_bin または utf8mb4_bin

データベースの照合を変更するには、次のように実行します。

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

テーブルの照合の変更

以降の例で、次のように変更します。 

• <yourDB>: 実際のデータベース名
• <charset>: utf8 または utf8mb4
• <collation>: utf8_bin または utf8mb4_bin

テーブルの照合を変更するには、次のように実行します。

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

カラムの照合の変更

以降の例で、次のように変更します。 

• <yourDB>: 実際のデータベース名
• <charset>: utf8 または utf8mb4
• <collation>: utf8_bin または utf8mb4_bin

varchar カラムのカラム照合を変更するには、次のように実行します。

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

非 varchar カラムの照合を変更するには、次のように実行します。

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

Confluence のサーバーでの文字セットの考慮事項

utf8mb4 を使用しているが、MySQL Server の my.cnf または my.ini ファイルで character_set_server が utf8mb4 に設定されておらず、これを変更できない場合 (例: utf8 は別のアプリケーションが使用するデータベースで必要)、utf8mb4 を使用するには、接続 URL に connectionCollation=utf8mb4_bin パラメーターを追加する必要があります。詳細については「Connector/J 8.0」または「Connector/J 5.1」をご確認ください。

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

my.cnf ファイルで照合と文字セットが適切に定義されている (character-set-server = utf8mb4 collation-server = utf8mb4_bin を使用) ことを確認する必要がある場合があります。