What is collation?

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.

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

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

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

MySQL のセットアップ ガイド

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

• Bamboo
• Confluence
• Crowd
• FishEye / Crucible
• Jira

Changing database collation

Change yourDB to suit your database name:

ALTER DATABASE yourDB CHARACTER SET utf8 COLLATE utf8_bin

テーブルの照合の変更

The following query will produce a series of ALTER TABLE statements, which you must then run against your database. Change yourDB to suit your database name:

SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_bin;')
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 != 'utf8'
    OR
    C.COLLATION_NAME != 'utf8_bin'
);

カラムの照合の変更

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. Change yourDB to suit your database name:

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_bin', (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 != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET UTF8 COLLATE utf8_bin', (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 != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);

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;

It didn't work, what should I do?

In some cases when you have emojis on commits, it may be needed to change some columns to utf8mb4/utf8mb4_bin.

USE NAME-OF-BAMBOO-DB;
SELECT @@character_set_database, @@collation_database;

The workaround to MySQL is to change those columns to utf8mb4/utf8mb4_bin.

Columns that can show this issue are:

• commit_files.commit_file_name
• commit_files.commit_file_reivision
• deployment_version_commit.commit_comment_clob
• user_commit.commit_comment_clob

To verify the current charset and collation:

1. Stop Bamboo
2. Create a backup of your database.

3. 次のコマンドを実行します。

   ALTER TABLE commit_files MODIFY COMMIT_FILE_NAME VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
   
   ALTER TABLE commit_files MODIFY COMMIT_FILE_REIVISION VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
   
   ALTER TABLE deployment_version_commit MODIFY COMMIT_COMMENT_CLOB LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
   
   ALTER TABLE user_commit MODIFY COMMIT_COMMENT_CLOB LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

4. Change the bamboo.cfg.xml file to: jdbc:mysql://[host]/[database]?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8
5. Start Bamboo

6. Verify the current charset and collation:

   USE NAME-OF-BAMBOO-DB;
   SELECT @@character_set_database, @@collation_database;