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

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Fisheye および Crucible は除く

データベースの直接操作はアトラシアンのサポート内容に含まれておらず、データベース管理者がご自身の責任で行う必要があります。 

アトラシアンが推奨するデータベースの移行方法は次のとおりです。

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

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

照合とは

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

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

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

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

使用可能な照合

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

utf8mb4 の使用が必須utf8mb4 を使用可能 utf8 の使用が必須
  • Confluence 8.0 or later, running on MySQL 8.0 or later
  • Jira 8.12 以降
  • MySQL 5.7.9 以降で実行されている Confluence 7.3 以降
  • Jira 8.0 - 8.11, running on MySQL 5.7 or later
  • Confluence 7.2 以前
  • Jira 7.13 以前
  • MySQL 5.6 で実行されているすべての Jira および Confluence バージョン


MySQL のセットアップ ガイド

MySQL データベースを適切にセットアップするには、各製品の次のリソースをご確認ください。

データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。

簡単に実行できるよう、1 つのファイルにすべての ALTER TABLE ステートメントを追加することもできます。

照合と文字セットの確認

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

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


To check database collation
use database_name;
SELECT @@character_set_database, @@collation_database;
To check the table collation
SELECT TABLE_SCHEMA
    , TABLE_NAME
    , TABLE_COLLATION 
FROM INFORMATION_SCHEMA.TABLES;
To check the column collation
SELECT TABLE_NAME 
    , COLUMN_NAME 
    , COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS;
Aggregated list of database objects with collation and character set
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
       COLUMN_NAME,
       COLUMN_TYPE,
       C.CHARACTER_SET_NAME, CCSA.COLLATION_NAME, ENGINE
  FROM information_schema.TABLES AS T
  JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
  JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
       ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
 WHERE TABLE_SCHEMA=SCHEMA()
   AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext')
 ORDER BY TABLE_SCHEMA,
          TABLE_NAME,
          COLUMN_NAME, CCSA.COLLATION_NAME, ENGINE;



外部キー制約について

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

To create the ALTER SQLs, run the below SQL and use the ALTER SQLs in the resultset to update the 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>'
);


カラムの照合の変更

上記のクエリと同様に、次のクエリ (varchar カラム用に 1 つ、非 varchar カラム用に 1 つ) は一連の ALTER TABLE ステートメントを生成します。これをデータベースに対して実行する必要があります。


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

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

To create the ALTER SQLs, run the below SQL and use the ALTER SQLs in the resultset to update the 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 create the ALTER SQLs, run the below SQL and use the ALTER SQLs in the resultset to update the 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>'
);

(warning)  WARNING! USING THE INCORRECT DATA TYPE IN ALTER TABLE WILL LEAD TO DATA LOSS.

Please ensure that the data type that is included in your alter table statement is correct, otherwise fields will be truncated. If you are unsure please contact Atlassian Support.

任意のステップ

上記のクエリを実行すると、各テーブルおよびカラムについて、個々の 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, ... 

(warning)  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_serverutf8mb4 に設定されておらず、これを変更できない場合 (例: utf8 は別のアプリケーションが使用するデータベースで必要)、utf8mb4 を使用するには、接続 URL に connectionCollation=utf8mb4_bin パラメーターを追加する必要があります。詳細については「Connector/J 8.0」または「Connector/J 5.1」をご確認ください。

手順について

1. Confluence を停止します

2. <local-home>/confluence.cfg.xml ファイルを編集します

3. 次の行を編集して、以下の例のようにデータベース接続 URL に connectionCollation=utf8mb4_bin パラメーターを追加します。 

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

4. Confluence を再起動します。 


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


説明 照合は、結果の並べ替え順を定義します。アトラシアン アプリケーションの新しいバージョンでは、アプリケーションで特定の照合を要求するため、照合の変更が厳密になる可能性があります。データベースの照合が、それを使用するアプリケーションに適切であることを確認する必要があります。
製品Jira、Confluence、Bamboo、Bitbucket、Fisheye
プラットフォームServer
最終更新日 2023 年 11 月 1 日

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

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