Migrate MySQL database to UTF8MB4 character encoding

Why is this necessary?

When a user tries to insert a UTF8 character, which requires 4-byte encoding (e.g. emojis like "😀"), the “Incorrect String Value” error message is displayed.

Migration scripts

Prerequisites

• codebeamer application must be updated to v10 or above.
• codebeamer application must be stopped.
• It is highly recommended to create a backup of your database. For more information, see: MySQL.
• Apply the following changes in your database configuration files and restart the server. See also: Preparing MySQL Database for Installation
  

  [mysqld]
  character_set_server=utf8mb4
  character_set_filesystem=utf8mb4
  collation-server=utf8mb4_general_ci
  init-connect='SET NAMES utf8mb4'
  init_connect='SET collation_connection = utf8mb4_general_ci'
  skip-character-set-client-handshake
  max_allowed_packet=128M
  sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'
  disable_log_bin

• Once the upgrade has been completed, continue upgrading codebeamer to a more recent version in order the MySQL functions and procedures to be re-created using utf8mb4.

Linux & Mac

Download the script here: linux_mac_script.sh

After navigating to the folder the script is downloaded to, give proper permissions to run the script with:

chmod 755 linux_mac_script.sh

After giving proper permissions, run the downloaded script:

./linux_mac_script.sh

Check if the script was successful by running the following query in MySQL (table_schema is your database name):

SELECT COLUMN_NAME, COLUMN_TYPE, character_set_name, COLLATION_NAME FROM information_schema.`COLUMNS`
WHERE table_schema = "codebeamer"
  AND table_name = "users";

All the columns with varchar type should have utf8mb4 character_set_name and utf8mb4_general_ci collation_name.

Start the codebeamer application server.

Windows

Download the script here: windows_script.bat

To run the script from Windows Command Line:

• Navigate to the folder where the script is downloaded.
• Run the following command with your own user and database name. You will be prompted to enter your password!
  

  mysql -u $user -p $database < windows_script.bat

Check if the script was successful by running the following query in MySQL (table_schema is your database name):

SELECT COLUMN_NAME, COLUMN_TYPE, character_set_name, COLLATION_NAME FROM information_schema.`COLUMNS`
WHERE table_schema = "codebeamer"
  AND table_name = "users";

All the columns in users table with varchar type should have utf8mb4 character_set_name and utf8mb4_bin collation_name.

Start the codebeamer application server.

Missing NOT NULL and DEFAULT definitions

An earlier version of the upgrade script might caused the drop of essential NOT NULL and DEFAULT definitions on some table columns.

The easiest way to find out if your system is affected by using a MySQL console and querying the description of a table which has NOT NULL and DEFAULT statements.

For example: DESCRIBE object_notification;

In the result, the only_members and deleted columns must have Null=NO and Default=0 attributes if migrated correctly and Null=YES and Default=NULL if affected by the bad script.

Fixing the issue

• Fixing requires a "donor" database schema which has to be the same version as the affected database.
  The easiest way to achieve this is to create a secondary MySQL database and install codebeamer a second time with the same installer as your main codebeamer installation, but this time running on different ports and pointing to this new database.
  • In case installing to the same MySQL server, you might have to change the default database user created (cbroot) to something else to not conflict with your existing installation. There is a checkbox on the database connection setup page to do this.
  • You don't have to enter a license key, just start a trial period. You can safely stop and uninstall this secondary installation once the setup wizard is complete and the login screen is visible. We only need the donor database initialized by the system.
• You have to extract the correct 'UPDATE' and 'ALTER TABLE' statements to fix the corrupted database. To do this, on the donor database, please execute the following SELECT statements (don't forget to change the TABLE_SCHEMA conditions for the name of the donor database).

For the 'UPDATE' statements, run this query and save the results:

SELECT CONCAT('UPDATE ', TABLE_NAME, ' SET ', COLUMN_NAME,' = ', QUOTE(COLUMN_DEFAULT), ' WHERE ', COLUMN_NAME, ' IS NULL;') as 'script'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_SCHEMA='codebeamer'
  AND IS_NULLABLE = 'NO' AND NOT ISNULL(COLUMN_DEFAULT)
  AND (COLLATION_NAME = 'utf8mb4_general_ci' OR COLLATION_NAME = 'utf8mb4_bin')
  AND EXISTS (
        SELECT * FROM INFORMATION_SCHEMA.TABLES t
        WHERE t.TABLE_NAME = c.TABLE_NAME
          AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
          AND t.TABLE_TYPE = 'BASE TABLE'
    );

For the 'ALTER TABLE' statements, runt this query and save the results:

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE ', COLUMN_NAME,' ', COLUMN_NAME, ' ', COLUMN_TYPE, IF(ISNULL(CHARACTER_SET_NAME), '', CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME)), IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME)), IF(IS_NULLABLE = 'NO', ' NOT NULL', ''), IF(NOT ISNULL(COLUMN_DEFAULT), CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT)), ''), ';') as 'script'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_SCHEMA='codebeamer'
  AND (IS_NULLABLE = 'NO' OR NOT ISNULL(COLUMN_DEFAULT))
  AND (COLLATION_NAME = 'utf8mb4_general_ci' OR COLLATION_NAME = 'utf8mb4_bin')
  AND EXISTS (
        SELECT * FROM INFORMATION_SCHEMA.TABLES t
        WHERE t.TABLE_NAME = c.TABLE_NAME
          AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
          AND t.TABLE_TYPE = 'BASE TABLE'
    );

• With the collected statements please do the following on the corrupted database:
  • Stop codebeamer.
  • Run the result of the previously saved 'UPDATE' statements.
  • Execute SET FOREIGN_KEY_CHECKS = 0;
  • Run the result of the previously saved 'ALTER TABLE' statements.
  • Execute SET FOREIGN_KEY_CHECKS = 1;.
  • Run the results of the 'ANALYZE SCRIPTS' from the original character set migration instructions.
  • Start codebeamer.