Error when trying to insert UTF-8 into Bamboo MySQL DB. Error Code: 1366
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
要約
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
環境
- Bamboo Server および Data Center
- MySQL 5.5+
原因
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.
ソリューション
Set those columns to utf8mb4. For that, follow the steps below:
- Stop Bamboo
- Backup the database
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';
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.
- 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:
USE BAMBOO_DB_NAME;
SELECT @@character_set_database, @@collation_database;