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 (for example, emojis like 
), the Incorrect String Value error message is displayed.
), the Incorrect String Value error message is displayed.Migration scripts
IMPORTANT! This upgrade has to be executed in conjunction with a Codebeamer version upgrade to work correctly. Collation and character set upgrade affects stored procedures/functions and Codebeamer only refreshes these when a version upgrade occurs.
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
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
 | MySQL 8.0.x comes with binary logging enabled by default (unlike previous releases). This must be disabled for successfull post-installation.The following option must be added/uncommented in the MySQL configuration file, in addition to the other Codebeamer related options: disable_log_bin. |
| | When editing the my.ini file on Windows, make sure to save the file with ANSI encoding, otherwise the MySQL service will not start. |
• 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
| | The script is written for a database named codebeamer, with user root. If the user or the database is different you can edit the variables in the script's first few lines. For security purposes the password is not hardcoded in the script. After running it, you will be prompted to type the password for the MySQL database. If there is no password set for the database simply press Enter when prompted. If users cannot insert their password, edit the script and add the password to the password variable. |
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";
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
| | The script is written for database named codebeamer. If you are using another database the script must be edited before being executed! Firstly in the first line the @db variable must be set to your database name, secondly in line 59 the ALTER DATABASE must also be set to ALTER your database. |
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";
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
| | The scirpts are written for a database named codebeamer, if users have a database with a different name, the following filter have to be updated accordingly: TABLE_SCHEMA=<database name>. |
• 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'
);
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'
);
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.