Tags:
not added yet
Migrate MySQL database to UTF8MB4 character encodingWhy 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
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
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.
Linux & MacDownload 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"; All the columns with varchar type should have utf8mb4 character_set_name and utf8mb4_general_ci collation_name.
If you are using SSL connection you can skip this step because you should have your useSSL variable set to true in your my.conf file. Otherwise you have to do one last step before starting up codebeamer. In your my.conf file append your ConnectionURL with useSSL=false if it is not already present.
You can start the codebeamer application. WindowsDownload 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:
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 definitionsAn 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>.
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' );
|
Fast Links
codebeamer Overview codebeamer Knowledge Base Services by Intland Software |
This website stores cookies on your computer. These cookies are used to improve your browsing experience, constantly optimize the functionality and content of our website, furthermore helps us to understand your interests and provide more personalized services to you, both on this website and through other media. With your permission we and our partners may use precise geolocation data and identification through device scanning. You may click accept to consent to our and our partners’ processing as described above. Please be aware that some processing of your personal data may not require your consent, but you have a right to object to such processing. By using our website, you acknowledge this notice of our cookie practices. By accepting and continuing to browse this site, you agree to this use. For more information about the cookies we use, please visit our Privacy Policy.Your preferences will apply to this website only.