You are not logged in. Click here to log in.

codebeamer Application Lifecycle Management (ALM)

Search In Project

Search inClear

Tags:  not added yet

Migrate MySQL 5.7 database to UTF8MB4 character encoding


To migrate to MySQL 8.0 database please refer to: Migrate MySQL 5.7 CB database to 8.0

Problem

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.


First installation

Your MySQL database must configured to be able to handle utf8mb4.


MySQL servers

Apply the following changes in your database configuration files and restart the server:

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

AWS RDS

Change the following parameters in the Parameter groups and restart the database:

character_set_server=utf8mb4
collation_server=utf8mb4_general_ci

codeBeamer installer

During the installation process you can select your schema collation and character set.


Docker with hosted database

Use the following script to create a new database:

CREATE DATABASE database_name default CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Docker with dockerized database

Use the https://hub.docker.com/r/intland/mysql-utf8mb4 docker image.

Upgrade

Prerequisites

  • codeBeamer application must be updated to v10
  • codeBeamer application must be stopped
  • It is highly recommended to create a backup of your database
  • Apply the following changes in your database configuration files and restart the server
  • Once the upgrade has been completed, please continue upgrading codebeamer to a more recent version in order the mysql functions and procedures to be re-created using utf8mb4
    [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

Scripts

A previous version of these scripts might created errors during migrating the character set on the database. Check the Missing NOT NULL and DEFAULT definitions section to find out if the system is affected and how to fix it.

The following SQL will return all the 'ALTER SCRIPTS', after executing it, save the result.

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE ', COLUMN_NAME,' ', COLUMN_NAME, ' ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci', 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 TABLE_NAME NOT IN ('session_history')
  AND COLLATION_NAME = 'utf8_general_ci'
  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'
    );
SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE ', COLUMN_NAME,' ', COLUMN_NAME, ' ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin', 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 TABLE_NAME NOT IN ('session_history')
  AND COLLATION_NAME = 'utf8_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'
    );

Execute and save the result of the following 'ANALYZE SCRIPT'.

SELECT DISTINCT CONCAT('ANALYZE TABLE ', TABLE_NAME, ';') as 'script'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA='codebeamer'
AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.ENGINE = 'InnoDB' AND t.TABLE_NAME = c.TABLE_NAME)
AND c.DATA_TYPE='varchar'

Execute and save the result of the following 'REPAIR AND OPTIMIZE SCRIPTS'.

SELECT DISTINCT CONCAT('REPAIR TABLE ', TABLE_NAME, '; OPTIMIZE TABLE ', TABLE_NAME, ';') as 'script'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA='codebeamer'
AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.ENGINE <> 'InnoDB' AND t.TABLE_NAME = c.TABLE_NAME)
AND c.DATA_TYPE='varchar'

Execution

  1. Change character-set and collation of the database schema:
    ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
  2. Execute:
    SET FOREIGN_KEY_CHECKS = 0;
  3. Execute both previously saved 'ALTER SCRIPTS'.
  4. Execute:
    SET FOREIGN_KEY_CHECKS = 1;
  5. Execute 'ANALYZE SCRIPTS'.
  6. Execute 'REPAIR AND OPTIMIZE SCRIPTS'.
  7. Start codeBeamer application

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 at the exact 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 our 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 TABLE_NAME NOT IN ('session_history')
  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, run 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 TABLE_NAME NOT IN ('session_history')
  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 previously saved 'UPDATE' statements.
    • Execute SET FOREIGN_KEY_CHECKS = 0;
    • Run the previously saved 'ALTER TABLE' statements
    • Execute SET FOREIGN_KEY_CHECKS = 1;.
    • Run the results of the 'ANALYZE SCRIPTS', 'REPAIR AND OPTIMIZE SCRIPTS' from the original character set migration instructions.
    • Start codebeamer.