Summary

When fixing the collation and character set on MySQL DB, the following error may occur while running the queries to fix the tables' collation:

Error Code: 1366. Incorrect string value: '\xF0\x9E\xA4\x80\xF0\x9E...' for column 'COMMIT_FILE_NAME'

or when Change detection is running in Bamboo:

Error Code: 1366. Incorrect string value: '\xF0\x9F\x98\xA2' for column 'COMMIT_COMMENT_CLOB'

The tables and 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
user_comment.CONTENT

Environment

• Bamboo Server and Data Center
• MySQL 5.5+

Cause

When someone changes something in the code and does a git commit, an emoji may have been used in the message related to that commit.

On the UTF charset, the 🙂 emoji equals "F0 9F 98 80", which matches the error message.

Solution

Set those columns to utf8mb4. For that, follow the steps below:

1. Stop Bamboo
2. Backup the database

3. Run the commands below:

   USE BAMBOO_DB_NAME;
   
   ALTER TABLE commit_files CHANGE COLUMN COMMIT_FILE_NAME COMMIT_FILE_NAME VARCHAR(1000) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin';
   ALTER TABLE commit_files CHANGE COLUMN COMMIT_FILE_REIVISION COMMIT_FILE_REIVISION VARCHAR(128) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin';
   
   ALTER TABLE deployment_version_commit CHANGE COLUMN COMMIT_COMMENT_CLOB COMMIT_COMMENT_CLOB LONGTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin';
   
   ALTER TABLE user_commit CHANGE COLUMN COMMIT_COMMENT_CLOB COMMIT_COMMENT_CLOB LONGTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin';
   
   ALTER TABLE user_comment CHANGE COLUMN CONTENT CONTENT 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

   Note: UTF-8 is the Java encoding and should remain as is. The useUnicode=true property is what makes Java work with 4-byte characters.

5. Start Bamboo

USE BAMBOO_DB_NAME;

SELECT @@character_set_database, @@collation_database;