Correct Database Collation (MySQL)

Because of the Emoji and other symbols used in MP4 and later you are required to have the utf8mb4_unicode_ci collation for databases, tables and columns in MySQL.

If you didn’t set it right at the first start you will get a message when starting the controller. To correct this you can execute the following query. You have to change one part with the database name:

USE information_schema;

SELECT concat("ALTER DATABASE `",table_schema,
              "` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql
  FROM `TABLES`
 WHERE table_schema like "pyplanet"
 GROUP BY table_schema;

SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,
              "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql
  FROM `TABLES`
 WHERE table_schema like "pyplanet"
 GROUP BY table_schema, table_name;

In this code-snippet, pyplanet is the database name. Make sure you change it to your database name.