Major version upgrade prechecks for Aurora MySQL - Amazon Aurora
Community MySQL upgrade prechecksAurora MySQL upgrade prechecks
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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 the utf8mb4 charset. The utf8mb3 character set is deprecated. Also, consider using utf8mb4 for character set references instead of utf8, because currently utf8 is an alias for the utf8mb3 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 or display 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 or SET 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 or DESC qualifiers for GROUP 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 in the MySQL documentation.

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, and QUERY_CACHE, in views, routines, triggers, and events.

  • There must be no FTS_DOC_ID column present on any table without the FTS 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 to 1.

  • 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 or COMPACT 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 the utf8mb4 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.