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

お困りですか?

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

コミュニティに質問

この記事はアトラシアンのサーバー プラットフォームにのみ適用されます。クラウドとサーバー製品の違いについてはこちらをご確認ください。

What is Collation?

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

Collation in MySQL can be complicated because you can have a separate collation set at:

  1. The database level
  2. The table level
  3. The column level

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

Which collation can you use?

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

Can use utf8mb4 Must use utf8
  • Confluence 7.3 and later, running on MySQL 5.7.9 or later
  • Jira 8.x and later, running on MySQL 5.7 or later
  • Confluence 7.2 and earlier
  • Jira 7.13 and earlier
  • Any Jira and Confluence versions running on MySQL 5.6


Setup Guides for MySQL

To setup your MySQL database correctly, see the following resources for each product:

データベースの変更を行う場合は必ず事前にバックアップを取得してください。

You may wish to add all the ALTER TABLE statements to a single file for easier execution.

Dealing with Foreign Key constraints

It may be necessary to ignore foreign key constraints when making changes to a large number of columns. 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;

Changing the database collation

In the example below, change: 

  • <yourDB> to your actual database name
  • <charset> to either utf8 or utf8mb4
  • <collation> to either utf8_bin or utf8mb4_bin

To change the database collation:

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


Changing table collation

The following query will produce a series of ALTER TABLE statements, which you must then run against your database. 

In the example below, change: 

  • <yourDB> to your actual database name
  • <charset> to either utf8 or utf8mb4
  • <collation> to either utf8_bin or utf8mb4_bin

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


Changing column collation

The following queries (one for varchar columns, and one for non-varchar columns) will produce a series of ALTER TABLE statements, which you must then run against your database. 

In the examples below, change: 

  • <yourDB> to your actual database name
  • <charset> to either utf8 or utf8mb4
  • <collation> to either utf8_bin or utf8mb4_bin

To change column 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 change column 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>'
);

Server character set considerations for 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 in order to use utf8mb4. See the Connector/J 8.0 or Connector/J 5.1 documentation for more information. 

手順について

1.Confluence を停止します

2. Edit the <local-home>/confluence.cfg.xml  file

3. Update the following line to add the connectionCollation=utf8mb4_bin  parameter to your database connection URL, as in the example below. 

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

4. Restart Confluence. 

説明 The collation determines how results are sorted and ordered. In newer versions of Atlassian applications, collation changes may become more strict - i.e, an application requires a certain collation. You must ensure your database has the correct collation for the application it will be used with.
製品Jira, Confluence, Bamboo, Bitbucket, Fisheye
プラットフォームサーバー
最終更新日: 2020 年 2 月 4 日

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

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