Major version upgrade prechecks for Aurora MySQL
MySQL 8.0 includes a number of incompatibilities with MySQL 5.7. These incompatibilities can cause problems during an upgrade from Aurora MySQL version 2 to version 3. Some preparation might be required on your database for the upgrade to be successful.
When you start an upgrade from Aurora MySQL version 2 to version 3, Amazon Aurora runs prechecks automatically to detect these incompatibilities.
These prechecks are mandatory. You can't choose to skip them. The prechecks provide the following benefits:
-
They enable you to avoid unplanned downtime during the upgrade.
-
If there are incompatibilities, Amazon Aurora prevents the upgrade and provides a log for you to learn about them. You can then use the log to prepare your database for the upgrade to version 3 by reducing the incompatibilities. For detailed information about removing incompatibilities, see Preparing your installation for upgrade
in the MySQL documentation and Upgrading to MySQL 8.0? Here is what you need to know... on the MySQL Server Blog. For more information about upgrading to MySQL 8.0, see Upgrading MySQL
in the MySQL documentation.
The prechecks include some that are included with MySQL and some that were created specifically by the Aurora team. For
information about the prechecks provided by MySQL, see Upgrade checker
utility
The prechecks run before the DB instance is stopped for the upgrade, meaning that they don't cause any downtime when they run. If the prechecks find an incompatibility, Aurora automatically cancels the upgrade before the DB instance is stopped. Aurora also generates an event for the incompatibility. For more information about Amazon Aurora events, see Working with Amazon RDS event notification.
Aurora records detailed information about each incompatibility in the log file PrePatchCompatibility.log
. In most
cases, the log entry includes a link to the MySQL documentation for correcting the incompatibility. For more information about
viewing log files, see Viewing and listing database log files.
Due to the nature of the prechecks, they analyze the objects in your database. This analysis results in resource consumption and increases the time for the upgrade to complete.
Community MySQL upgrade prechecks
The following is a general list of incompatibilities between MySQL 5.7 and 8.0:
-
Your MySQL 5.7–compatible DB cluster must not use features that aren't supported in MySQL 8.0.
For more information, see Features removed in MySQL 8.0
in the MySQL documentation. -
There must be no keyword or reserved word violations. Some keywords might be reserved in MySQL 8.0 that were not reserved previously.
For more information, see Keywords and reserved words
in the MySQL documentation. -
For improved Unicode support, consider converting objects that use the
utf8mb3
charset to use theutf8mb4
charset. Theutf8mb3
character set is deprecated. Also, consider usingutf8mb4
for character set references instead ofutf8
, because currentlyutf8
is an alias for theutf8mb3
charset.For more information, see The utf8mb3 character set (3-byte UTF-8 unicode encoding)
in the MySQL documentation. -
There must be no InnoDB tables with a nondefault row format.
-
There must be no
ZEROFILL
ordisplay
length type attributes. -
There must be no partitioned table that uses a storage engine that does not have native partitioning support.
-
There must be no tables in the MySQL 5.7
mysql
system database that have the same name as a table used by the MySQL 8.0 data dictionary. -
There must be no tables that use obsolete data types or functions.
-
There must be no foreign key constraint names longer than 64 characters.
-
There must be no obsolete SQL modes defined in your
sql_mode
system variable setting. -
There must be no tables or stored procedures with individual
ENUM
orSET
column elements that exceed 255 characters in length. -
There must be no table partitions that reside in shared InnoDB tablespaces.
-
There must be no circular references in tablespace data file paths.
-
There must be no queries and stored program definitions that use
ASC
orDESC
qualifiers forGROUP BY
clauses. -
There must be no removed system variables, and system variables must use the new default values for MySQL 8.0.
-
There must be no zero (
0
) date, datetime, or timestamp values. -
There must be no schema inconsistencies resulting from file removal or corruption.
-
There must be no table names that contain the
FTS
character string. -
There must be no InnoDB tables that belong to a different engine.
-
There must be no table or schema names that are invalid for MySQL 5.7.
For more information about upgrading to MySQL 8.0, see Upgrading MySQL
Aurora MySQL upgrade prechecks
Aurora MySQL has its own specific requirements when upgrading from version 2 to version 3:
-
There must be no deprecated SQL syntax, such as
SQL_CACHE
,SQL_NO_CACHE
, andQUERY_CACHE
, in views, routines, triggers, and events. -
There must be no
FTS_DOC_ID
column present on any table without theFTS
index. -
There must be no column definition mismatch between the InnoDB data dictionary and the actual table definition.
-
All database and table names must be lowercase when the
lower_case_table_names
parameter is set to1
. -
Events and triggers must not have a missing or empty definer or an invalid creation context.
-
All trigger names in a database must be unique.
-
DDL recovery and Fast DDL aren't supported in Aurora MySQL version 3. There must be no artifacts in databases related to these features.
-
Tables with the
REDUNDANT
orCOMPACT
row format can't have indexes larger than 767 bytes. -
The prefix length of indexes defined on
tiny
text columns can't exceed 255 bytes. With theutf8mb4
character set, this limits the prefix length supported to 63 characters.A larger prefix length was allowed in MySQL 5.7 using the
innodb_large_prefix
parameter. This parameter is deprecated in MySQL 8.0. -
There must be no InnoDB metadata inconsistency in the
mysql.host
table. -
There must be no column data type mismatch in system tables.
-
There must be no XA transactions in the
prepared
state. -
Column names in views can't be longer than 64 characters.
-
Special characters in stored procedures can't be inconsistent.
-
Tables can't have data file path inconsistency.