DB Conversion to UTF8MB4

Introduction

UTF8MB4 is an extension of the UTF-8 character encoding format. The original UTF-8 format uses a variable number of bytes to represent Unicode characters. However, it only supports the characters of the original Unicode standard, which had a maximum of 65,536 characters.

Converting the DB

Make sure that:

  • The my.cnf/my.ini matches the one specified in the Relution installation guide for your operating system.
  • A database exists.
  • The database has the correct permissions.

To update:

  1. stop the Relution service.
  2. run this query on the database.
USE information_schema;
SELECT "SET SESSION innodb_strict_mode=OFF, NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';" as _sql
UNION
SELECT concat("SET foreign_key_checks = 0;") as _sql
UNION
SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;") as _sql FROM `TABLES` where table_schema like "relution" group by table_schema
UNION
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;") as _sql
FROM `TABLES` where table_schema like "relution" group by table_schema, table_name
UNION
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") as _sql
FROM `TABLES` where table_schema like "relution" group by table_schema, table_name
UNION
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") as _sql
FROM `COLUMNS` where table_schema like "relution" and data_type in ('varchar')
UNION
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") as _sql
FROM `COLUMNS` where table_schema like "relution" and data_type in ('text','tinytext','mediumtext','longtext')
UNION
SELECT concat("SET foreign_key_checks = 1;") as _sql;

This will generate an SQL script that can be used to migrate the database.
3. copy the result of the query and execute it as a new SQL query 4. make sure that the tables have the following modified specifications: - Row Format = Compressed - File Format = Barracuda - Table collation = utf8mb4_general_ci.