MySQL データベースの照合と文字セットを手動で修正する方法

1. 対象のドキュメントに従い、必要な照合を持つ新しいデータベースを作成します (例: Jira をデータベースに接続する)
2. 「データベースの切り替え」に従い、XML バックアップを使用して、古い (誤った照合を持つ) データベースから新しい適切な照合を持つデータベースに移行します

何らかの理由により、推奨される方法をご利用のシナリオでは利用できない場合、次の記事に従ってデータベース サーバー側で照合を手動で修正します。ソリューションを実装したら、アプリケーションを徹底的にテストして、すべてが期待したとおりに動作していることを確認してください。

照合とは

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

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

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

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

使用可能な照合

Jira と Confluence のすべてのバージョンが utf8mb4 (4 バイト文字をサポート) をサポートしているわけではありません。utf8 を使用する必要がある場合があります。

照合と文字セットの確認

このプロセスを通じて、データベース、テーブル、およびカラム レベルでの設定の再確認が必要になる可能性があります。  

確認に利用できるコマンドは次のとおりです。

データベース照合の確認:
use jiradb;
SELECT @@character_set_database, @@collation_database;
 
To check Table collation:
SELECT TABLE_SCHEMA
    , TABLE_NAME
    , TABLE_COLLATION 
FROM INFORMATION_SCHEMA.TABLES


To check Column collation:
SELECT TABLE_NAME 
    , COLUMN_NAME 
    , COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 

外部キー制約について

It may be necessary to ignore foreign key constraints when making changes to a large number of columns.

mysql> ALTER TABLE AO_1FA2A8_SCRUM_POKER_SESSION CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ERROR 3780 (HY000): Referencing column 'SESSION_ID' and referenced column 'ISSUE_KEY' in foreign key constraint 'fk_ao_1fa2a8_scrum_poker_vote_session_id' are incompatible.
mysql> ALTER TABLE AO_1FA2A8_SCRUM_POKER_VOTE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ERROR 3780 (HY000): Referencing column 'SESSION_ID' and referenced column 'ISSUE_KEY' in foreign key constraint 'fk_ao_1fa2a8_scrum_poker_vote_session_id' are incompatible.

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;

This should be done for each session that is opened to the database

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

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

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

  The "DATA_TYPE" query currently fails for enum columns.  When "DATA_TYPE" is replaced with "COLUMN_TYPE", the generated SQL is also valid for enum columns.

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 を使用) ことを確認する必要がある場合があります。