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.

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, information inside a column may be encoded incorrectly as well - causing the data in that column to be displayed incorrectly.

Changing database collation

Change yourDB to suit your database name:

ALTER DATABASE yourDB CHARACTER SET utf8 COLLATE utf8_bin

Changing table collation

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

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. 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. Run the following commands:

   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;