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

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 is unsuitable for your scenario, please follow this article to manually fix the collation on the database server side. After implementing the solution, please test the application thoroughly to ensure everything works correctly and as expected.

照合とは

The collation determines how results are sorted and ordered. In newer versions of Atlassian applications, collation changes may become more strict - that is, an application requires a specific collation. You must ensure your database has the correct collation for the application it will be used with.

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

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

Additionally, the information inside a column may be encoded incorrectly, causing the data in that column to be displayed incorrectly.

使用可能な照合

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

照合と文字セットの確認

Throughout the process, It may be necessary to re-check the database, table, and column settings.  

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

use database_name;
SELECT @@character_set_database, @@collation_database;

SELECT TABLE_SCHEMA
    , TABLE_NAME
    , TABLE_COLLATION 
FROM INFORMATION_SCHEMA.TABLES;

SELECT TABLE_NAME 
    , COLUMN_NAME 
    , COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS;

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 changing many 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

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

カラムの照合の変更

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

• <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! USING THE INCORRECT DATA TYPE IN ALTER TABLE WILL LEAD TO DATA LOSS.

Please ensure the data type in your alter table statement is correct; otherwise, fields will be truncated. If you are unsure, please don't hesitate to get in touch with Atlassian Support.

  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 のサーバーでの文字セットの考慮事項

If you want to use utf8mb4, and character_set_server  is not set to utf8mb4  in the my.cnf  or my.ini  file on your MySQL server, and you can't change this (for example, utf8 is required for a database used by another application) you will need to add the connectionCollation=utf8mb4_bin parameter to your connection URL to use utf8mb4. See the Connector/J 8.0 or Connector/J 5.1 documentation for more information. 

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

You might also need to make sure that your collation and character set are correctly defined in your my.cnf file (for example, using character-set-server = utf8mb4 collation-server = utf8mb4_bin)