Migrate a MariaDB/MySQL database from utf8 to utf8mb4

If you are viewing this guide in PDF form it is possible that quotation marks have been replaced by their typographic (i.e. curly) versions. This renders the script’s syntax incorrect. Please make sure you use the Markdown or HTML version of this document.

Ensure that:

  • The my.cnf/my.ini matches the BlueRange installation instructions for your operating system.

  • A database exists

  • The database has the correct permissions

To update:

  1. Stop the BlueRange service

  2. Execute the following Query on your 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 generates an SQL script that can be used to migrate the database.

  1. Copy the unquoted result of the query and execute it as new SQL query.

  2. Make sure that the tables have the following changed details:

    • Row Format = Compressed

    • File Format = Barracuda

    • Table collation = utf8mb4_general_ci