MySQL データベースの照合と文字セットを手動で修正する方法
プラットフォームについて: サーバーと Data Center のみ。この記事は、サーバーおよび Data Center プラットフォームのアトラシアン製品にのみ適用されます。
データベースの直接操作はアトラシアンのサポート内容に含まれておらず、データベース管理者がご自身の責任で行う必要があります。
アトラシアンが推奨するデータベースの移行方法は次のとおりです。
- 対象のドキュメントに従い、必要な照合を持つ新しいデータベースを作成します (例: Jira をデータベースに接続する)
- 「データベースの切り替え」に従い、XML バックアップを使用して、古い (誤った照合を持つ) データベースから新しい適切な照合を持つデータベースに移行します
何らかの理由により、推奨される方法をご利用のシナリオでは利用できない場合、次の記事に従ってデータベース サーバー側で照合を手動で修正します。ソリューションを実装したら、アプリケーションを徹底的にテストして、すべてが期待したとおりに動作していることを確認してください。
照合とは
照合は、結果の並べ替え順を定義します。アトラシアン アプリケーションの新しいバージョンでは、アプリケーションで特定の照合を要求するため、照合の変更が厳密になる可能性があります。データベースの照合が、それを使用するアプリケーションに適切であることを確認する必要があります。
MySQL では、次のレベルで異なる照合セットがあるため、照合が複雑になる場合があります。
- データベース レベル
- テーブル レベル
- カラム レベル
また、カラム内の情報が誤った方法でエンコードされ、それによってカラムのデータが誤って表示される場合もあります。
使用可能な照合
Jira と Confluence のすべてのバージョンが utf8mb4 (4 バイト文字をサポート) をサポートしているわけではありません。utf8 を使用する必要がある場合があります。
utf8mb4 の使用が必須 | utf8mb4 を使用可能 | utf8 の使用が必須 |
---|---|---|
|
|
|
MySQL のセットアップ ガイド
MySQL データベースを適切にセットアップするには、各製品の次のリソースをご確認ください。
データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。
簡単に実行できるよう、1 つのファイルにすべての ALTER TABLE ステートメントを追加することもできます。
照合と文字セットの確認
このプロセスを通じて、データベース、テーブル、およびカラム レベルでの設定の再確認が必要になる可能性があります。
確認に利用できるコマンドは次のとおりです。
データベース照合の確認: 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>
テーブルの照合の変更
次のクエリは一連の ALTER TABLE
ステートメントを生成し、これをデータベースに対して実行する必要がある点にご注意ください。
以降の例で、次のように変更します。
<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>'
);
カラムの照合の変更
上記のクエリと同様に、次のクエリ (varchar
カラム用に 1 つ、非 varchar
カラム用に 1 つ) は一連の ALTER TABLE
ステートメントを生成します。これをデータベースに対して実行する必要があります。
以降の例で、次のように変更します。
<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>'
);
任意のステップ
上記のクエリを実行すると、各テーブルおよびカラムについて、個々の ALTER ステートメントの一覧が生成されます。特に変更されるテーブルに大量の行がある場合、パフォーマンス上の理由により、結果のクエリをデータベースでの実行前に手動で最適化することができます。同じテーブル (別のカラム) を対象とする複数の ALTER ステートメントを 1 つのステートメントに組み合わせ、MySQL がテーブル全体を複数回処理するのを避けるようにすることで、クエリの実行時間を軽減できます。例:
ALTER TABLE `changeitem` MODIFY `FIELDTYPE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `changeitem` MODIFY `FIELD` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
...
これらが次のようになります。
ALTER TABLE `changeitem`
MODIFY `FIELDTYPE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
MODIFY `FIELD` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, ...
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」をご確認ください。
my.cnf ファイルで照合と文字セットが適切に定義されている (character-set-server = utf8mb4 collation-server = utf8mb4_bin を使用) ことを確認する必要がある場合があります。