Prerequisites Before Upgrading to MySQL 8

Starting with MySQL 8, Scalingo enables group replication on all MySQL databases. This MySQL feature has a couple of strong prerequisites (MySQL documentation). Please make sure your database is compatible before proceeding with the upgrade:

Storage Engine

All tables must use the InnoDB storage engine. You can check which tables use a different storage engine with the SQL command:

SELECT table_schema, table_name FROM information_schema.tables \
  WHERE table_schema IN ('my-app-3030') AND engine != 'InnoDB';

Here is the output of this command if the foo table use another storage engine than InnoDB:

+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| my-app-3030  | foo        |
+--------------+------------+
1 row in set (0.00 sec)

In this situation, you need to update foo’s storage engine with the following command:

ALTER TABLE 'foo' ENGINE = 'InnoDB';

Mandatory Primary Keys

All tables must have a primary key configured. You can check which tables do not have any primary key with the SQL command:

SELECT information_schema.tables.table_schema, information_schema.tables.table_name \
  FROM information_schema.tables LEFT JOIN information_schema.key_column_usage AS c ON (\
    information_schema.tables.table_name = c.table_name AND \
    c.constraint_schema = information_schema.tables.table_schema AND \
    c.constraint_name = 'PRIMARY' \
  ) \
  WHERE information_schema.tables.table_schema IN ('my-app-3030') AND \
  c.constraint_name IS NULL;

Here is the output of this command if the foo table does not have a primary key:

+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| my-app-3030  | foo        |
+--------------+------------+
1 row in set (0.00 sec)

In this situation, you need to add a primary key to the foo table. You need to evaluate the impact of this modification before proceeding. Here is the command to add a new column and set it as primary:

ALTER TABLE 'foo' ADD COLUMN <column description> PRIMARY KEY;

Suggest edits

Prerequisites Before Upgrading to MySQL 8

©2023 Scalingo