Error when trying to insert UTF-8 into Bamboo MySQL DB. Error Code: 1366
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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:
1
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:
1
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:
1
2
3
4
5
commit_files.COMMIT_FILE_NAME
commit_files.COMMIT_FILE_REIVISION
deployment_version_commit.COMMIT_COMMENT_CLOB
user_commit.COMMIT_COMMENT_CLOB
user_comment.CONTENTEnvironment
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.
git commit -m 'commit_message 🙂'Â
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:
Stop Bamboo
Backup the database
Run the commands below:
1 2 3 4 5 6 7 8 9 10USE 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';Change the
bamboo.cfg.xmlfile to:1jdbc: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.
Start Bamboo
To verify that your database is set, connect to your database using the same user used by your application and run the following commands:
1
2
3
USE BAMBOO_DB_NAME;
SELECT @@character_set_database, @@collation_database;Was this helpful?