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:

  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

    (warning) 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


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;



最終更新日: 2024 年 1 月 25 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.