Bitbucket Server upgrade fails due to "Incorrect string value for column 'comment_text'"
Platform Notice: Data Center Only - This article only applies to Atlassian apps 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
Problem
The upgrade to Bitbucket Server 5.+ fails.
The following appears in the user interface:
The database schema could not be updatedThe following appears in the atlassian-bitbucket.log
ERROR [spring-startup] liquibase classpath:liquibase/master.xml: liquibase/r5_0/upgrade.xml::BSERVDEV-8489-12::jpalacios: Change Set liquibase/r5_0/upgrade.xml::BSERVDEV-8489-12::jpalacios failed. Error: Incorrect string value: '<character>' for column 'comment_text' at row <row_number> [Failed SQL: insert into bb_comment (
id,
author_id,
comment_text,
created_timestamp,
entity_version,
thread_id,
updated_timestamp
)
select
id,
author_id,
comment_text,
created_timestamp,
entity_version,
id,
updated_timestamp
from sta_comment
where root_id is null]Diagnosis
Environment
MySQL as database
Diagnostic Steps
Check the database encoding and collation:
USE <database_name>; SELECT @@character_set_database, @@collation_databaseThe results don't match the expected results of using utf8 as encoding and utf8_bin as collation
The database dump for the
sta_commenttable returns the expected values (utf8 as encoding and utf8_bin as collation)
Cause
When upgrading Bitbucket Server, SQL commands are run to update the database schema.
If the default encoding or collation are not correct (e.g. latin1), the new tables with the configured encoding (latin1) in this case.
If special characters are stored in the database and cannot be converted, the data migration from the sta_comment table to the bb_comment fails due to different encoding.
Solution
Resolution
Warning: Backup your database before proceeding.
Before running any SQL in this article, create a full backup of your Jira database. This is a database-level operation with no undo other than restoring from backup. Only proceed if you're comfortable running SQL directly against the Jira database, and test on a staging or cloned environment first, wherever possible.
Take a backup of Bitbucket Server (see Data recovery and backups)
Stop Bitbucket Server
Change the database encoding to utf8 by running
USE <database_name>; ALTER DATABASE <database_name> CHARACTER SET utf8;Perform the upgrade
Was this helpful?