Precheck descriptions reference for Aurora MySQL

Precheck level: Error

Issues reported by the check table x for upgrade command for the mysql schema

Before starting the upgrade to Aurora MySQL version 3, check table for upgrade is run on each table in the mysql schema on the DB instance. The check table for upgrade command examines tables for any potential issues that might arise during an upgrade to a newer version of MySQL. Running this command before attempting an upgrade can help identify and resolve any incompatibilities ahead of time, making the actual upgrade process smoother.

This command performs various checks on each table, such as the following:

For more information, see CHECK TABLE statement in the MySQL documentation.

Example output:

{
  "id": "checkTableMysqlSchema",
  "title": "Issues reported by 'check table x for upgrade' command for mysql schema.",
  "status": "OK",
  "detectedProblems": []
}

The output for this precheck depends on the error encountered, and when it's encountered, because check table for upgrade performs multiple checks.

If you encounter any errors with this precheck, open a case with AWS Support to request that the metadata inconsistency be resolved. Alternatively, you can retry the upgrade by performing a logical dump, then restoring to a new Aurora MySQL version 3 DB cluster.

Precheck level: Error

Circular directory references in tablespace data file paths

As of MySQL 8.0.17, the CREATE TABLESPACE ... ADD DATAFILE clause no longer permits circular directory references. To avoid upgrade issues, remove any circular directory references from tablespace data file paths before upgrading to Aurora MySQL version 3.

Example output:

{
  "id": "circularDirectory",
  "title": "Circular directory references in tablespace data file paths",
  "status": "OK",
  "description": "Error: Following tablespaces contain circular directory references (e.g. the reference '/../') in data file paths which as of MySQL 8.0.17 are not permitted by the CREATE TABLESPACE ... ADD DATAFILE clause. An exception to the restriction exists on Linux, where a circular directory reference is permitted if the preceding directory is a symbolic link. To avoid upgrade issues, remove any circular directory references from tablespace data file paths before upgrading.",
  "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-innodb-changes",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "ts2",
        "description": "circular reference in datafile path: '/home/ec2-user/dbdata/mysql_5_7_44/../ts2.ibd'",
        "dbObjectType": "Tablespace"
      }
  ]
}

If you receive this error, rebuild your tables using a file-per-table tablespace. Use default file paths for all tablespace and table definitions.

After rebuilding, the precheck passes, allowing the upgrade to proceed.

{
  "id": "circularDirectoryCheck",
  "title": "Circular directory references in tablespace data file paths",
  "status": "OK",
  "detectedProblems": []
},

Precheck level: Error

Columns that can't have default values

Before MySQL 8.0.13, BLOB, TEXT, GEOMETRY, and JSON columns can't have default values. Remove any default clauses on these columns before upgrading to Aurora MySQL version 3. For more information on changes to the default handling for these data types, see the Data type default values in the MySQL documentation.

Example output:

{
  "id": "columnsWhichCannotHaveDefaultsCheck",
  "title": "Columns which cannot have default values",
  "status": "OK",
  "description": "Error: The following columns are defined as either BLOB, TEXT, GEOMETRY or JSON and have a default value set. These data types cannot have default values in MySQL versions prior to 8.0.13, while starting with 8.0.13, the default value must be specified as an expression. In order to fix this issue, please use the ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT statement.",
  "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html#data-type-defaults-explicit",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.test_blob_default.geo_col",
        "description": "geometry"
      }
  ]
},

The precheck returns an error because the geo_col column in the test.test_blob_default table is using a BLOB, TEXT, GEOMETRY, or JSON data type with a default value specified.

Looking at the table definition, we can see that the geo_col column is defined as geo_col geometry NOT NULL default ''.

mysql> show create table test_blob_default\G
*************************** 1. row ***************************
       Table: test_blob_default
Create Table: CREATE TABLE `test_blob_default` (
  `geo_col` geometry NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Removing this default clause to allow the precheck to pass.

mysql> ALTER TABLE test_blob_default modify COLUMN geo_col geometry NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test_blob_default\G
*************************** 1. row ***************************
       Table: test_blob_default
Create Table: CREATE TABLE `test_blob_default` (
  `geo_col` geometry NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The precheck passes, and you can retry the upgrade.

{
  "id": "columnsWhichCannotHaveDefaultsCheck",
  "title": "Columns which cannot have default values",
  "status": "OK",
  "detectedProblems": []
},

Precheck level: Error

Usage of depreciated keywords in definition

MySQL 8.0 has removed the query cache. As a result, some query cache–specific SQL syntax has been removed. If any of your database objects contain the QUERY CACHE, SQL_CACHE, or SQL_NO_CACHE keywords, a precheck error is returned. To resolve this issue, re-create these objects, removing the mentioned keywords.

Example output:

{
  "id": "depreciatedSyntaxCheck",
  "title": "Usage of depreciated keywords in definition",
  "status": "OK",
  "description": "Error: The following DB objects contain keywords like 'QUERY CACHE', 'SQL_CACHE', 'SQL_NO_CACHE' which are not supported in major version 8.0. It is recommended to drop these DB objects or rebuild without any of the above keywords before upgrade.",
  "detectedProblems": [
      {
"level": "Error",
"dbObject": "test.no_query_cache_check",
"description": "PROCEDURE uses depreciated words in definition"
      }
  ]
}

The precheck reports that the test.no_query_cache_check stored procedure is using one of the removed keywords. Looking at the procedure definition, we can see that it uses SQL_NO_CACHE.

mysql> show create procedure test.no_query_cache_check\G
*************************** 1. row ***************************
           Procedure: no_query_cache_check
            sql_mode:
    Create Procedure: CREATE DEFINER=`reinvent`@`%` PROCEDURE `no_query_cache_check`()
BEGIN
    SELECT SQL_NO_CACHE k from sbtest1 where id > 10 and id < 20 group by k asc;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

Remove the keyword.

mysql> drop procedure test.no_query_cache_check;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //

mysql> CREATE DEFINER=`reinvent`@`%` PROCEDURE `no_query_cache_check`() BEGIN     SELECT k from sbtest1 where id > 10 and id < 20 group by k asc; END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

After removing the keyword, the precheck completes successfully.

{
  "id": "depreciatedSyntaxCheck",
  "title": "Usage of depreciated keywords in definition",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Tables recognized by InnoDB that belong to a different engine

Similar to schemaInconsistencyCheck, this precheck verifies that table metadata in MySQL is consistent before proceeding with the upgrade.

If you encounter any errors with this precheck, open a case with AWS Support to request that the metadata inconsistency be resolved. Alternatively, you can retry the upgrade by performing a logical dump, then restoring to a new Aurora MySQL version 3 DB cluster.

Example output:

{
  "id": "engineMixupCheck",
  "title": "Tables recognized by InnoDB that belong to a different engine",
  "status": "OK",
  "description": "Error: Following tables are recognized by InnoDB engine while the SQL layer believes they belong to a different engine. Such situation may happen when one removes InnoDB table files manually from the disk and creates e.g. a MyISAM table with the same name.\n\nA possible way to solve this situation is to e.g. in case of MyISAM table:\n\n1. Rename the MyISAM table to a temporary name (RENAME TABLE).\n2. Create some dummy InnoDB table (its definition does not need to match), then copy (copy, not move) and rename the dummy .frm and .ibd files to the orphan name using OS file commands.\n3. The orphan table can be then dropped (DROP TABLE), as well as the dummy table.\n4. Finally the MyISAM table can be renamed back to its original name.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "mysql.general_log_backup",
        "description": "recognized by the InnoDB engine but belongs to CSV"
      }
  ]
}

Precheck level: Error

ENUM and SET column definitions containing elements longer than 255 characters

Tables and stored procedures must not have ENUM or SET column elements exceeding 255 characters or 1020 bytes. Before MySQL 8.0, the maximum combined length was 64K, but 8.0 limits individual elements to 255 characters or 1020 bytes (supporting multibyte). If you get a precheck failure for enumSetElementLengthCheck, modify any elements exceeding these new limits before retrying the upgrade.

Example output:

{
  "id": "enumSetElementLengthCheck",
  "title": "ENUM/SET column definitions containing elements longer than 255 characters",
  "status": "OK",
  "description": "Error: The following columns are defined as either ENUM or SET and contain at least one element longer that 255 characters. They need to be altered so that all elements fit into the 255 characters limit.",
  "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/string-type-overview.html",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.large_set.s",
        "description": "SET contains element longer than 255 characters"
      }
  ]
},

The precheck reports an error because the column s in the test.large_set table contains a SET element larger than 255 characters.

After reducing the SET size for this column, the precheck passes, allowing the upgrade to proceed.

{
  "id": "enumSetElementLenghtCheck",
  "title": "ENUM/SET column definitions containing elements longer than 255 characters",
  "status": "OK",
  "detectedProblems": []
},

Precheck level: Error

Foreign key constraint names longer than 64 characters

In MySQL, the length of identifiers is limited to 64 characters, as outlined in the MySQL documentation. Due to issues identified where foreign key lengths could equal or exceed this value, leading to upgrade failures, this precheck was implemented. If you encounter errors with this precheck you should alter or rename your constraint so that it is less than 64 characters before retrying the upgrade.

Example output:

{
  "id": "foreignKeyLength",
  "title": "Foreign key constraint names longer than 64 characters",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

All trigger names in a database must be unique.

Due to changes in the data dictionary implementation, MySQL 8.0 doesn't support case-sensitive triggers within a database. This precheck validates that your DB cluster doesn’t have one or more databases containing duplicate triggers. For more information, see Identifier case sensitivity in the MySQL documentation.

Example output:

{
  "id": "getDuplicateTriggers",
  "title": "MySQL pre-checks that all trigger names in a database are unique or not.",
  "status": "OK",
  "description": "Error: You have one or more database containing duplicate triggers. Mysql 8.0 does not support case sensitive triggers within a database https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html. To upgrade to MySQL 8.0, drop the triggers with case-insensitive duplicate names and recreate with distinct names.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test",
        "description": "before_insert_product"
      },
      {
        "level": "Error",
        "dbObject": "test",
        "description": "before_insert_PRODUCT"
      }
  ]
}

The precheck reports an error that the database cluster has two triggers with the same name, but using different cases: test.before_insert_product and test.before_insert_PRODUCT.

Before upgrading, rename the triggers or drop and re-create them with a new name.

After renaming test.before_insert_PRODUCT to test.before_insert_product_2, the precheck succeeds.

{
  "id": "getDuplicateTriggers",
  "title": "MySQL pre-checks that all trigger names in a database are unique or not.",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

The definer column for mysql.event can't be null or blank.

The DEFINER attribute specifies the MySQL account that owns a stored object definition, such as a trigger, stored procedure, or event. This attribute is particularly useful in situations where you want to control the security context under which the stored object runs. When creating a stored object, if a DEFINER isn't specified, the default is the user who created the object.

When upgrading to MySQL 8.0, you can't have any stored objects that have a null or blank definer in the MySQL data dictionary. If you have such stored objects, a precheck error is raised. You must fix it before the upgrade can proceed.

Example error:

{
  "id": "getEventsWithNullDefiner",
  "title": "The definer column for mysql.event cannot be null or blank.",
  "status": "OK",
  "description": "Error: Set definer column in mysql.event to a valid non-null definer.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.get_version",
        "description": "Set definer for event get_version in Schema test"
      }
  ]
}

The precheck returns an error for the test.get_version event because it has a null definer.

To resolve this you can check the event definition. As you can see, the definer is null or blank.

mysql> select db,name,definer from mysql.event where name='get_version';
+------+-------------+---------+
| db   | name        | definer |
+------+-------------+---------+
| test | get_version |         |
+------+-------------+---------+
1 row in set (0.00 sec)

Drop or re-create the event with a valid definer.

mysql> drop event test.get_version;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> delimiter $$
mysql> CREATE EVENT get_version
    ->     ON SCHEDULE
    ->       EVERY 1 DAY
    ->     DO
    ->      ///DO SOMETHING //
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

mysql> select db,name,definer from mysql.event where name='get_version';
+------+-------------+------------+
| db   | name        | definer    |
+------+-------------+------------+
| test | get_version | reinvent@% |
+------+-------------+------------+
1 row in set (0.00 sec)

Now the precheck passes.

{
  "id": "getEventsWithNullDefiner",
  "title": "The definer column for mysql.event cannot be null or blank.",
  "status": "OK",
  "detectedProblems": []},

Precheck level: Error

Column definition mismatch between InnoDB data dictionary and actual table definition

Similar to schemaInconsistencyCheck, this precheck verifies that table metadata in MySQL is consistent before proceeding with the upgrade. In this case, the precheck verifies that the column definitions match between the InnoDB data dictionary and the MySQL table definition. If a mismatch if detected, the upgrade doesn't proceed.

If you encounter any errors with this precheck, open a case with AWS Support to request that the metadata inconsistency be resolved. Alternatively, you can retry the upgrade by performing a logical dump, then restoring to a new Aurora MySQL version 3 DB cluster.

Example output:

{
  "id": "getMismatchedMetadata",
  "title": "Column definition mismatch between InnoDB Data Dictionary and actual table definition.",
  "status": "OK",
  "description": "Error: Your database has mismatched metadata. The upgrade to mysql 8.0 will not succeed until this is fixed.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.mismatchTable",
        "description": "Table `test/mismatchTable` column names mismatch with InnoDb dictionary column names: iD <> id"
      }
  ]
}

The precheck reports a mismatch in the metadata for the id column in the test.mismatchTable table. Specifically, the MySQL metadata has the column name as iD, while InnoDB has it as id.

Precheck level: Error

The definer column for information_schema.triggers can't be null or blank.

The precheck validates that your database has no triggers defined with null or blank definers. For more information on definer requirements for stored objects, see getEventsWithNullDefiner.

Example output:

{
  "id": "getTriggersWithNullDefiner",
  "title": "The definer column for information_schema.triggers cannot be null or blank.",
  "status": "OK",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.example_trigger",
        "description": "Set definer for trigger example_trigger in Schema test"
      }
  ]
}

The precheck returns an error because the example_trigger trigger in the test schema has a null definer. To correct this issue, fix the definer by re-creating the trigger with a valid user, or drop the trigger. For more information, see the example in getEventsWithNullDefiner.

Precheck level: Error

All database or table names must be lowercase when the lower_case_table_names parameter is set to 1.

Before MySQL 8.0, database names, table names and other objects corresponded to files in the data directory, such as file-based metadata (.frm). The lower_case_table_names system variable allows users to control how the server handles identifier case sensitivity for database objects, and the storage of such metadata objects. This parameter could be changed on an already initialized server following a reboot.

However, in MySQL 8.0, while this parameter still controls how the server handles identifier case sensitivity, it can't be changed after the data dictionary is initialized. When upgrading or creating a MySQL 8.0 database, the value set for lower_case_table_names the first time the data dictionary is started on MySQL, is used for the lifetime of that database. This restriction was put in place as part of the implementation of the Atomic Data Dictionary implementation, where database objects are migrated from file-based metadata to internal InnoDB tables in the mysql schema.

For more information, see Data dictionary changes in the MySQL documentation.

To avoid issues during upgrade when updating file-based metadata to the new Atomic Data Dictionary, this precheck validates that when lower_case_table_names = 1, all tables are stored on disk in lower case. If they aren’t, a precheck error is returned, and you must correct the metadata before proceeding with the upgrade.

Example output:

{
  "id": "getValueOfVariablelower_case_table_names",
  "title": "MySQL pre-checks that all database or table names are lowercase when the lower_case_table_names parameter is set to 1.",
  "status": "OK",
  "description": "Error: You have one or more databases or tables with uppercase letters in the names, but the lower_case_table_names parameter is set to 1. To upgrade to MySQL 8.0, either change all database or table names to lowercase, or set the parameter to 0.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.TEST",
        "description": "Table test.TEST contains one or more capital letters in name while lower_case_table_names = 1"
      }
  ]
}

An error is returned because the table test.TEST contains uppercase letters, but lower_case_table_names is set to 1.

To resolve this issue, you can rename the table to use lowercase, or modify the lower_case_table_names parameter on the DB cluster before starting the upgrade.

Precheck level: Error

Usage of removed GROUP BY ASC/DESC syntax

As of MySQL 8.0.13, the deprecated ASC or DESC syntax for GROUP BY clauses has been removed. Queries relying on GROUP BY sorting might now produce different results. To get a specific sort order, use an ORDER BY clause instead. If any objects exist in your database using this syntax, you must re-create them using an ORDER BY clause before retrying the upgrade. For more information, see SQL changes in the MySQL documentation.

Example output:

{
  "id": "groupbyAscSyntaxCheck",
  "title": "Usage of removed GROUP BY ASC/DESC syntax",
  "status": "OK",
  "description": "Error: The following DB objects use removed GROUP BY ASC/DESC syntax. They need to be altered so that ASC/DESC keyword is removed from GROUP BY clause and placed in appropriate ORDER BY clause.",
  "documentationLink": "https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-sql-syntax",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.groupbyasc",
        "description": "PROCEDURE uses removed GROUP BY ASC syntax",
        "dbObjectType": "Routine"
      }
  ]
}

Precheck level: Error

Check for deprecated .<table> syntax used in routines.

In MySQL 8.0, routines can no longer contain the deprecated identifier syntax (\".<table>\"). If any stored routines or triggers contain such identifiers, the upgrade fails. For example, the following .dot_table reference is no longer permitted:

mysql> show create procedure incorrect_procedure\G
*************************** 1. row ***************************
           Procedure: incorrect_procedure
            sql_mode:
    Create Procedure: CREATE DEFINER=`reinvent`@`%` PROCEDURE `incorrect_procedure`()
BEGIN delete FROM .dot_table; select * from .dot_table where 1=1; END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

After you re-create the routines and triggers to use the correct identifier syntax and escaping, the precheck passes, and the upgrade can proceed. For more information on identifiers, see Schema object names in the MySQL documentation.

Example output:

{
  "id": "mysqlEmptyDotTableSyntaxCheck",
  "title": "Check for deprecated '.<table>' syntax used in routines.",
  "status": "OK",
  "description": "Error: The following routines contain identifiers in deprecated identifier syntax (\".<table>\"), and should be corrected before upgrade:\n",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.incorrect_procedure",
        "description": " routine body contains deprecated identifiers."
      }
  ]
}

The precheck returns an error for the incorrect_procedure routine in the test database because it contains deprecated syntax.

After you correct the routine, the precheck succeeds, and you can retry the upgrade.

Precheck level: Error

Check for indexes that are too large to work on MySQL versions higher than 5.7

For compact or redundant row formats, it shouldn't be possible to create an index with a prefix larger than 767 bytes. However, before MySQL version 5.7.35 this was possible. For more information, see the MySQL 5.7.35 release notes.

Any indexes that were affected by this bug will become inaccessible after upgrading to MySQL 8.0. This precheck identifies offending indexes that have to be rebuilt before the upgrade is allowed to proceed.

 {
  "id": "mysqlIndexTooLargeCheck",
  "title": "Check for indexes that are too large to work on higher versions of MySQL Server than 5.7",
  "status": "OK",
  "description": "Error: The following indexes ware made too large for their format in an older version of MySQL (older than 5.7.34). Normally those indexes within tables with compact or redundant row formats shouldn't be larger than 767 bytes. To fix this problem those indexes should be dropped before upgrading or those tables will be inaccessible.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.table_with_large_idx",
        "description": "IDX_2"
      }
  ]
}

The precheck returns an error because the test.table_with_large_idx table contains an index on a table using a compact or redundant row format that's larger than 767 bytes. These tables would become unaccessible after upgrading to MySQL 8.0. Before proceeding with the upgrade, do one of the following:

Here we rebuild the table to resolve the precheck failure. Before rebuilding the table, make sure that the innodb_file_format is set to Barracuda, and the innodb_default_row_format is set to dynamic. These are the defaults in MySQL 5.7. For more information, see InnoDB row formats and InnoDB file-format management in the MySQL documentation.

mysql > select @@innodb_file_format,@@innodb_default_row_format;
+----------------------+-----------------------------+
| @@innodb_file_format | @@innodb_default_row_format |
+----------------------+-----------------------------+
| Barracuda            | dynamic                     |
+----------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> optimize table table_with_large_idx;
+---------------------------+----------+----------+-------------------------------------------------------------------+
| Table                     | Op       | Msg_type | Msg_text                                                          |
+---------------------------+----------+----------+-------------------------------------------------------------------+
| test.table_with_large_idx | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.table_with_large_idx | optimize | status   | OK                                                                |
+---------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.02 sec)

# Verify FILE_FORMAT and ROW_FORMAT
mysql>  select * from information_schema.innodb_sys_tables where name like 'test/table_with_large_idx';
+----------+---------------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME                      | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+---------------------------+------+--------+-------+-------------+------------+---------------+------------+
|       43 | test/table_with_large_idx |   33 |      4 |    26 | Barracuda   | Dynamic    |             0 | Single     |
+----------+---------------------------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.00 sec)

After rebuilding the table, the precheck passes, and the upgrade can proceed.

{
  "id": "mysqlIndexTooLargeCheck",
  "title": "Check for indexes that are too large to work on higher versions of MySQL Server than 5.7",
  "status": "OK",
  "detectedProblems": []
},

Precheck level: Error

Check for invalid table and schema names used in MySQL 5.7

When migrating to the new data dictionary in MySQL 8.0, your database instance can't contain schemas or tables prefixed with #mysql50#. If any such objects exist, the upgrade fails. To resolve this issue, run mysqlcheck against the returned schemas and tables.

Example output:

{
  "id": "mysqlInvalid57NamesCheck",
  "title": "Check for invalid table names and schema names used in 5.7",
  "status": "OK",
  "description": "The following tables and/or schemas have invalid names. In order to fix them use the mysqlcheck utility as follows:\n\n  $ mysqlcheck --check-upgrade --all-databases\n  $ mysqlcheck --fix-db-names --fix-table-names --all-databases\n\nOR via mysql client, for eg:\n\n  ALTER DATABASE `#mysql50#lost+found` UPGRADE DATA DIRECTORY NAME;",
  "documentationLink": "https://dev.mysql.com/doc/refman/5.7/en/identifier-mapping.html https://dev.mysql.com/doc/refman/5.7/en/alter-database.html https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "#mysql50#fix_db_names",
        "description": "Schema name"
      }
  ]
}

The precheck reports that the schema #mysql50#fix_db_names has an invalid name.

After fixing the schema name, the precheck passes, allowing the upgrade to proceed.

{
  "id": "mysqlInvalid57NamesCheck",
  "title": "Check for invalid table names and schema names used in 5.7",
  "status": "OK",
  "detectedProblems": []
},

Precheck level: Error

Check for orphaned routines in 5.7

When migrating to the new data dictionary in MySQL 8.0, if there are any stored procedures in the database where the schema no longer exists, the upgrade fails. This precheck verifies that all schemas referenced in stored procedures on your DB instance still exist. To allow the upgrade to proceed, drop these stored procedures.

Example output:

{
  "id": "mysqlOrphanedRoutinesCheck",
  "title": "Check for orphaned routines in 5.7",
  "status": "OK",
  "description": "Error: The following routines have been orphaned. Schemas that they are referencing no longer exists.\nThey have to be cleaned up or the upgrade will fail.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "dropped_db.get_version",
        "description": "is orphaned"
      }
  ]
},

The precheck reports that the get_version stored procedure in the dropped_db database is orphaned.

To clean up this procedure, you can re-create the missing schema.

mysql> create database dropped_db;
Query OK, 1 row affected (0.01 sec)

After the schema is re-created, you can drop the procedure to allow the upgrade to proceed.

{
  "id": "mysqlOrphanedRoutinesCheck",
  "title": "Check for orphaned routines in 5.7",
  "status": "OK",
  "detectedProblems": []
},

Precheck level: Error

Table names in the mysql schema conflicting with new tables in MySQL 8.0

The new Atomic Data Dictionary introduced in MySQL 8.0 stores all metadata in a set of internal InnoDB tables in the mysql schema. During the upgrade, the new internal data dictionary tables are created in the mysql schema. To avoid naming collisions, which would result in upgrade failures, the precheck examines all table names in the mysql schema to ensure that none of the new table names are already in use. If they are, an error is returned, and the upgrade isn't allowed to proceed.

Example output:

{
  "id": "mysqlSchema",
  "title": "Table names in the mysql schema conflicting with new tables in the latest MySQL.",
  "status": "OK",
  "description": "Error: The following tables in mysql schema have names that will conflict with the ones introduced in the latest version. They must be renamed or removed before upgrading (use RENAME TABLE command). This may also entail changes to applications that use the affected tables.",
  "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/upgrade-before-you-begin.html",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "mysql.tablespaces",
        "description": "Table name used in mysql schema.",
        "dbObjectType": "Table"
      }
  ]
}

An error is returned because there is a table named tablespaces in the mysql schema. This is one of the new internal data dictionary table names in MySQL 8.0. You must rename or remove any such tables before upgrading, by using the RENAME TABLE command.

Precheck level: Error

Partitioned tables using engines with non-native partitioning

According to the MySQL 8.0 documentation, two storage engines currently provide native partitioning support: InnoDB and NDB. Of these, only InnoDB is supported in Aurora MySQL version 3, compatible with MySQL 8.0. Any attempt to create partitioned tables in MySQL 8.0 using any other storage engine fails. This precheck looks for tables in your DB cluster that are using non-native partitioning. If any are returned, you must remove the partitioning or convert the storage engine to InnoDB.

Example output:

{
  "id": "nonNativePartitioning",
  "title": "Partitioned tables using engines with non native partitioning",
  "status": "OK",
  "description": "Error: In the latest MySQL storage engine is responsible for providing its own partitioning handler, and the MySQL server no longer provides generic partitioning support. InnoDB and NDB are the only storage engines that provide a native partitioning handler that is supported in the latest MySQL. A partitioned table using any other storage engine must be altered—either to convert it to InnoDB or NDB, or to remove its partitioning—before upgrading the server, else it cannot be used afterwards.",
  "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-configuration-changes",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.partMyisamTable",
         "description": "MyISAM engine does not support native partitioning",
         "dbObjectType": "Table"
      }
  ]
}

Here a MyISAM table is using partitioning, which requires action before the upgrade can proceed.

Precheck level: Error

Usage of old temporal type

"Old temporals" refer to the temporal type columns (such as TIMESTAMP and DATETIME) created in MySQL versions 5.5 and lower. In MySQL 8.0, support for these old temporal data types is removed, meaning that in-place upgrades from MySQL 5.7 to 8.0 aren't possible if the database contains these old temporal types. To fix this, you must rebuild any tables containing such old temporal date types, before proceeding with the upgrade.

For more information on the deprecation of old temporal data types in MySQL 5.7, see this blog. For more information on the removal of old temporal data types in MySQL 8.0, see this blog.

Example output:

{
  "id": "oldTemporalCheck",
  "title": "Usage of old temporal type",
  "status": "OK",
  "description": "Error: Following table columns use a deprecated and no longer supported temporal disk storage format. They must be converted to the new format before upgrading. It can by done by rebuilding the table using 'ALTER TABLE <table_name> FORCE' command",
  "documentationLink": "https://dev.mysql.com/blog-archive/mysql-8-0-removing-support-for-old-temporal-datatypes/",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.55_temporal_table.timestamp_column",
        "description": "timestamp /* 5.5 binary format */",
        "dbObjectType": "Column"
      }
  ]
},

An error is reported for the column timestamp_column in the table test.55_temporal_table, because it uses an old temporal disk storage format that's no longer supported.

To resolve this issue and allow the upgrade to proceed, rebuild the table to convert the old temporal disk storage format to the new one introduced in MySQL 5.6. For more information and prerequisites before doing so, see Converting between 3-byte and 4-byte Unicode character sets in the MySQL documentation.

Running the following command to rebuild the tables mentioned in this precheck converts the old temporal types to the newer format with fractional-second precision.

ALTER TABLE ... ENGINE=InnoDB;

For more information on rebuilding tables, see ALTER TABLE statement in the MySQL documentation.

After rebuilding the table in question and restarting the upgrade, the compatibility check passes, allowing the upgrade to proceed.

{
  "id": "oldTemporalCheck",
  "title": "Usage of old temporal type",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Usage of partitioned tables in shared tablespaces

As of MySQL 8.0.13, support for placing table partitions in shared tablespaces is removed. Before upgrading, move any such tables from shared tablespaces to file-per-table tablespaces.

Example output:

{
  "id": "partitionedTablesInSharedTablespaceCheck",
  "title": "Usage of partitioned tables in shared tablespaces",
  "status": "OK",
  "description": "Error: The following tables have partitions in shared tablespaces. They need to be moved to file-per-table tablespace before upgrading. You can do this by running query like 'ALTER TABLE table_name REORGANIZE PARTITION X INTO (PARTITION X VALUES LESS THAN (30) TABLESPACE=innodb_file_per_table);'",
  "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.partInnoDBTable",
        "description": "Partition p1 is in shared tablespace innodb",
        "dbObjectType": "Table"
      }
  ]
}

The precheck fails because partition p1 from table test.partInnoDBTable is in the system tablespace.

To resolve this issue, rebuild the test.partInnodbTable table, placing the offending partition p1 in a file-per-table tablespace.

mysql > ALTER TABLE partInnodbTable REORGANIZE PARTITION p1
    ->   INTO (PARTITION p1 VALUES LESS THAN ('2014-01-01') TABLESPACE=innodb_file_per_table);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

After doing so, the precheck passes.

{
  "id": "partitionedTablesInSharedTablespaceCheck",
  "title": "Usage of partitioned tables in shared tablespaces",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Usage of functions that were removed from the latest MySQL version

In MySQL 8.0, a number of built-in functions have been removed. This precheck examines your database for objects that might use these functions. If they're found, an error is returned. You must resolve the issues before retrying the upgrade.

The majority of functions removed are spatial functions, which have been replaced with equivalent ST_* functions. In these cases, you modify the database objects to use the new procedure naming. For more information, see Features removed in MySQL 8.0 in the MySQL documentation.

Example output:

{
  "id": "removedFunctionsCheck",
  "title": "Usage of removed functions",
  "status": "OK",
  "description": "Error: The following DB objects use functions that were removed in the latest MySQL version. Please make sure to update them to use supported alternatives before upgrade.",
  "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.GetLocationsInPolygon",
        "description": "PROCEDURE uses removed function POLYGONFROMTEXT (consider using ST_POLYGONFROMTEXT instead)",
        "dbObjectType": "Routine"
      },
      {
        "level": "Error",
        "dbObject": "test.InsertLocation",
        "description": "PROCEDURE uses removed function POINTFROMTEXT (consider using ST_POINTFROMTEXT instead)",
        "dbObjectType": "Routine"
      }
  ]
},

The precheck reports that the test.GetLocationsInPolygon stored procedure is using two removed functions: POLYGONFROMTEXT and POINTFROMTEXT. It also suggests that you use the new ST_POLYGONFROMTEXT and ST_POINTFROMTEXT as replacements. After re-creating the procedure using the suggestions, the precheck completes successfully.

{
  "id": "removedFunctionsCheck",
  "title": "Usage of removed functions",
  "status": "OK",
  "detectedProblems": []
},

Precheck level: Error

MySQL syntax check for routine-like objects

MySQL 8.0 introduced reserved keywords that were not reserved previously. The upgrade prechecker evaluates the usage of reserved keywords in the names of database objects and in their definitions and body. If it detects reserved keywords being used in database objects, such as stored procedures, functions, events, and triggers, the upgrade fails and an error is published to the upgrade-prechecks.log file. To resolve the issue, you must update these object definitions and enclose any such references in single quotes (') before upgrading. For more information on escaping reserved words in MySQL, see String literals in the MySQL documentation.

Alternatively, you can change the name to a different name, which may require application changes.

Example output:

{
  "id": "routineSyntaxCheck",
  "title": "MySQL syntax check for routine-like objects",
  "status": "OK",
  "description": "The following objects did not pass a syntax check with the latest MySQL grammar. A common reason is that they reference names that conflict with new reserved keywords. You must update these routine definitions and `quote` any such references before upgrading.",
  "documentationLink": "https://dev.mysql.com/doc/refman/en/keywords.html",
  "detectedProblems": [
      {
         "level": "Error",
         "dbObject": "test.select_res_word",
         "description": "at line 2,18: unexpected token 'except'",
         "dbObjectType": "Routine"
      }
  ]
}

To fix this issue, check the routine definition.

SHOW CREATE PROCEDURE test.select_res_word\G

*************************** 1. row ***************************
           Procedure: select_res_word
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE PROCEDURE 'select_res_word'()
BEGIN
    SELECT * FROM except;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

The procedure uses a table named except, which is a new keyword in MySQL 8.0. Re-create the procedure by escaping the string literal.

> drop procedure if exists select_res_word;
Query OK, 0 rows affected (0.00 sec)

> DELIMITER $$
 > CREATE PROCEDURE select_res_word()
    -> BEGIN
    ->     SELECT * FROM 'except';
    -> END$$
Query OK, 0 rows affected (0.00 sec)

 > DELIMITER ;

The precheck now passes.

{
  "id": "routineSyntaxCheck",
  "title": "MySQL syntax check for routine-like objects",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Schema inconsistencies resulting from file removal or corruption

As described previously, MySQL 8.0 introduced the Atomic Data Dictionary, which stores all metadata in a set of internal InnoDB tables in the mysql schema. This new architecture provides a transactional, ACID-compliant way to manage database metadata, solving the "atomic DDL" problem from the old file-based approach. Before MySQL 8.0, it was possible for schema objects to become orphaned if a DDL operation was unexpectedly interrupted. The migration of file-based metadata to the new Atomic Data Dictionary tables during upgrade ensures that there are no such orphaned schema objects in the DB instance. If any orphaned objects are encountered, the upgrade fails.

To help detect these orphaned objects before initiating the upgrade, the schemaInconsistencyCheck precheck is run to ensure that all data dictionary metadata objects are in sync. If any orphaned metadata objects are detected, the upgrade doesn't proceed. To proceed with the upgrade, clean up these orphaned metadata objects.

If you encounter any errors with this precheck, open a case with AWS Support to request that the metadata inconsistency be resolved. Alternatively, you can retry the upgrade by performing a logical dump, then restoring to a new Aurora MySQL version 3 DB cluster.

Example output:

{
  "id": "schemaInconsistencyCheck",
  "title": "Schema inconsistencies resulting from file removal or corruption",
  "status": "OK",
  "description": "Error: Following tables show signs that either table datadir directory or frm file was removed/corrupted. Please check server logs, examine datadir to detect the issue and fix it before upgrade",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.schemaInconsistencyCheck_failure",
        "description": "present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table"
      }
  ]
}

The precheck reports that the test.schemaInconsistencyCheck_failure table has inconsistent metadata. In this case, the table exists in the InnoDB storage engine metadata (information_schema.INNODB_SYS_TABLES), but not in the MySQL metadata (information_schema.TABLES).

Precheck level: Error

Check for artifacts related to Aurora DDL recovery feature

As part of the Data Definition Language (DDL) recovery implementation in Aurora MySQL, metadata on inflight DDL statements is maintained in the ddl_log_md_table and ddl_log_table tables in the mysql schema. Aurora's implementation of DDL recovery isn't supported for version 3 onward, because the functionality is part of the new Atomic Data Dictionary implementation in MySQL 8.0. If you have any DDL statements running during the compatibility checks, this precheck might fail. We recommend that you try the upgrade while no DDL statements are running.

If this precheck fails without any running DDL statements, open a case with AWS Support to request that the metadata inconsistency be resolved. Alternatively, you can retry the upgrade by performing a logical dump, then restoring to a new Aurora MySQL version 3 DB cluster.

If any DDL statements are running, the precheck output prints the following message:

“There are DDL statements in process. Please allow DDL statements to finish before upgrading.”

Example output:

{
  "id": "auroraCheckDDLRecovery",
  "title": "Check for artifacts related to Aurora DDL recovery feature",
  "status": "OK",
  "description": "Aurora implementation of DDL recovery is not supported from 3.x onwards. This check verifies that the database do not have artifacts realted to the feature",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "mysql.ddl_log_md_table",
        "description": "Table mysql.ddl_log_md_table is not empty. Your database has pending DDL recovery operations. Reachout to AWS support for assistance."
      },
      {
        "level": "Error",
        "dbObject": "mysql.ddl_log_table",
        "description": "Table mysql.ddl_log_table is not empty. Your database has pending DDL recovery operations. Reachout to AWS support for assistance."
      },
      {
        "level": "Error",
        "dbObject": "information_schema.processlist",
        "description": "There are DDL statements in process. Please allow DDL statements to finish before upgrading."
      }
  ]
}

The precheck returned an error due to an inflight DDL running concurrently with the compatibility checks. We recommend that you retry the upgrade without any DDLs running.

Precheck level: Error

Check existence of mysql.rds_upgrade_prechecks table

This is an internal-only precheck carried out by the RDS service. Any errors will be automatically handled on upgrade and can be safely ignored.

If you encounter any errors with this precheck, open a case with AWS Support to request that the metadata inconsistency be resolved. Alternatively, you can retry the upgrade by performing a logical dump, then restoring to a new Aurora MySQL version 3 DB cluster.

{
  "id": "auroraCheckRdsUpgradePrechecksTable",
  "title": "Check existence of mysql.rds_upgrade_prechecks table",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Check for artifacts related to Aurora fast DDL feature

The Fast DDL optimization was introduced in lab mode on Aurora MySQL version 2 to improve the efficiency of some DDL operations. In Aurora MySQL version 3, lab mode has been removed, and the Fast DDL implementation has been superseded by the MySQL 8.0 feature called Instant DDL.

Before upgrading to Aurora MySQL version 3, any tables that use Fast DDL in lab mode will have to be rebuilt by running the OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB command to ensure compatibility with Aurora MySQL version 3.

This precheck returns a list of any such tables. After the returned tables have been rebuilt, you can retry the upgrade.

Example output:

{
  "id": "auroraFODUpgradeCheck",
  "title": "Check for artifacts related to Aurora fast DDL feature",
  "status": "OK",
  "description": "Aurora fast DDL is not supported from 3.x onwards. This check verifies that the database does not have artifacts related to the feature",
  "documentationLink": "https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managing.FastDDL.html#AuroraMySQL.Managing.FastDDL-v2",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.test",
        "description": "Your table has pending Aurora fast DDL operations. Run 'OPTIMIZE TABLE <table name>' for the table to apply all the pending DDL updates. Then try the upgrade again."
      }
  ]
}

The precheck reports that the table test.test has pending Fast DDL operations.

To allow the upgrade to proceed, you can rebuild the table, then retry the upgrade.

mysql> optimize table test.test;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.test | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.04 sec)

After rebuilding the table, the precheck succeeds.

{
  "id": "auroraFODUpgradeCheck",
  "title": "Check for artifacts related to Aurora fast DDL feature",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Tables with dangling FULLTEXT index reference

Before MySQL 5.6.26, it was possible that after dropping a full-text search index, the hidden FTS_DOC_ID and FTS_DOC_ID_INDEX columns would become orphaned. For more information, see Bug #76012.

If you have any tables created on earlier versions of MySQL where this has occurred, it can cause upgrades to Aurora MySQL version 3 to fail. This precheck verifies that no such orphaned, or “dangling” full-text indexes exist on your DB cluster before upgrading to MySQL 8.0. If this precheck fails, rebuild any tables that contain such dangling full-text indexes.

Example output:

{
  "id": "auroraGetDanglingFulltextIndex",
  "title": "Tables with dangling FULLTEXT index reference",
  "status": "OK",
  "description": "Error: The following tables contain dangling FULLTEXT index which is not supported. It is recommended to rebuild the table before upgrade.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.table_with_fts_index",
        "description": "Table `test.table_with_fts_index` contains dangling FULLTEXT index. Kindly recreate the table before upgrade."
      }
  ]
},

The precheck reports an error for the test.table_with_fts_index table because it contains a dangling full-text index. To allow the upgrade to proceed, rebuild the table to clean up the full-text index auxiliary tables. Use OPTIMIZE TABLE test.table_with_fts_index or ALTER TABLE test.table_with_fts_index, ENGINE=INNODB.

After rebuilding the table, the precheck passes.

{
  "id": "auroraGetDanglingFulltextIndex",
  "title": "Tables with dangling FULLTEXT index reference",
  "status": "OK",
  "detectedProblems": []
},

Precheck level: Error

Check for inconsistency related to ibd file path

This precheck applies only to Aurora MySQL version 3.03.0 and lower. If you encounter an error with this precheck, upgrade to Aurora MySQL version 3.04 or higher.

Example output:

{
  "id": "auroraUpgradeCheckForDatafilePathInconsistency",
  "title": "Check for inconsistency related to ibd file path.",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Check for full-text index with space ID as zero

In MySQL, when you add a full-text index to an InnoDB table, a number of auxiliary index tablespaces are created. Due to a bug in earlier versions of MySQL, which was fixed in version 5.6.20, it was possible that these auxiliary index tables were created in the system tablespace, rather than their own InnoDB tablespace.

If any such auxiliary tablespaces exist, the upgrade will fail. Re-create the full-text indexes mentioned in the precheck error, then retry the upgrade.

Example output:

{
  "id": "auroraUpgradeCheckForFtsSpaceIdZero",
  "title": "Check for fulltext index with space id as zero",
  "status": "OK",
  "description": "The auxiliary tables of FTS indexes on the table are created in system table-space. Due to this DDL queries executed on MySQL8.0 shall cause database unavailability. To avoid that, drop and recreate all the FTS indexes on the table or rebuild the table using ALTER TABLE query before the upgrade.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.fts_space_zero_check",
        "description": " The auxiliary tables of FTS indexes on the table 'test.fts_space_zero_check' are created in system table-space due to https://bugs.mysql.com/bug.php?id=72132. In MySQL8.0, DDL queries executed on this table shall cause database unavailability. To avoid that, drop and recreate all the FTS indexes on the table or rebuild the table using ALTER TABLE query before the upgrade."
      }
  ]
},

The precheck reports an error for the test.fts_space_zero_check table, because it has auxiliary full-text search (FTS) tables in the system tablespace.

After you drop and re-create the FTS indexes associated with this table, the precheck succeeds.

{
 "id": "auroraUpgradeCheckForFtsSpaceIdZero",
 "title": "Check for fulltext index with space id as zero",
 "status": "OK",
 "detectedProblems": []
}

Precheck level: Error

Check for XA transactions in prepared state

While running the major version upgrade process, it is essential that the Aurora MySQL version 2 DB instance undergo a clean shutdown. This ensures that all transactions are committed or rolled back, and that InnoDB has purged all undo log records. Because transaction rollback is necessary, if your database has any XA transactions in a prepared state, it can block the clean shutdown from proceeding. For this reason, if any prepared XA transactions are detected, the upgrade will be unable to proceed until you take action to commit or roll them back.

For more information on finding XA transactions in a prepared state using XA RECOVER, see XA transaction SQL statements in the MySQL documentation. For more information on XA transaction states, see XA transaction states in the MySQL documentation.

Example output:

{
  "id": "auroraUpgradeCheckForIncompleteXATransactions",
  "title": "Pre-checks for XA Transactions in prepared state.",
  "status": "OK",
  "description": "Your cluster currently has XA transactions in the prepared state. To proceed with the upgrade, commit or rollback these transactions.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "all",
        "description": "Your cluster currently has XA transactions in the prepared state. To proceed with the upgrade, commit or rollback these transactions."
      }
  ]
}

This precheck reports an error because there are transactions in a prepared state that should be committed or rolled back.

After logging into the database, you can check the information_schema.innodb_trx table and the XA RECOVER output for more information.

mysql> select trx_started,
    trx_mysql_thread_id,
    trx_id,trx_state,
    trx_operation_state,
    trx_rows_modified,
    trx_rows_locked 
from 
    information_schema.innodb_trx;
+---------------------+---------------------+---------+-----------+---------------------+-------------------+-----------------+
| trx_started         | trx_mysql_thread_id | trx_id  | trx_state | trx_operation_state | trx_rows_modified | trx_rows_locked |
+---------------------+---------------------+---------+-----------+---------------------+-------------------+-----------------+
| 2024-08-12 01:09:39 |                   0 | 2849470 | RUNNING   | NULL                |                 1 |               0 |
+---------------------+---------------------+---------+-----------+---------------------+-------------------+-----------------+
1 row in set (0.00 sec)

mysql> xa recover;
+----------+--------------+--------------+--------+
| formatID | gtrid_length | bqual_length | data   |
+----------+--------------+--------------+--------+
|        1 |            6 |            0 | xatest |
+----------+--------------+--------------+--------+
1 row in set (0.00 sec)

In this case, we roll back the prepared transaction.

mysql> XA ROLLBACK 'xatest';
Query OK, 0 rows affected (0.00 sec)
v
mysql> xa recover;
Empty set (0.00 sec)

After the XA transaction is rolled back, the precheck succeeds.

{
  "id": "auroraUpgradeCheckForIncompleteXATransactions",
  "title": "Pre-checks for XA Transactions in prepared state.",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Check whether upgrade is supported on the current instance class

Running an in-place upgrade from Aurora MySQL version 2.12.0 or 2.12.1, where the writer DB instance class is db.r6i.32xlarge, is currently not supported. In this case, the precheck returns an error. To allow the upgrade to proceed, you can either change your DB instance class to db.r6i.24xlarge or smaller. Or you can upgrade to Aurora MySQL version 2.12.2 or higher, where in-place upgrade to Aurora MySQL version 3 is supported on db.r6i.32xlarge.

Example output:

{
  "id": "auroraUpgradeCheckForInstanceLimit",
  "title": "Checks if upgrade is supported on the current instance class",
  "status": "OK",
  "description": "Upgrade from Aurora Version 2.12.0 and 2.12.1 may fail for 32.xl and above instance class.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "all",
        "description": "Upgrade is not supported on this instance size for Aurora MySql Version 2.12.1. Before upgrading to Aurora MySql 3, please consider either: 1. Changing the instance class to 24.xl or lower. -or- 2. Upgrading to patch version 2.12.2 or higher."
      }
  ]
},

The precheck returns an error because the writer DB instance is using the db.r6i.32xlarge instance class, and is running on Aurora MySQL version 2.12.1.

Precheck level: Error

Check for 8.0 internal users

This precheck applies only to Aurora MySQL version 3.03.0 and lower. If you encounter an error with this precheck, upgrade to Aurora MySQL version 3.04 or higher.

Example output:

{
  "id": "auroraUpgradeCheckForInternalUsers",
  "title": "Check for 8.0 internal users.",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Check whether RDS master user exists

MySQL 8 has added a new privilege model with support for role and dynamic privileges to make privilege management easier and more fine grained. As part of this change, Aurora MySQL has introduced the new rds_superuser_role, which is automatically granted to the database’s master user on upgrade from Aurora MySQL version 2 to version 3.

For more information on the roles and privileges assigned to the master user in Aurora MySQL, see Master user account privileges. For more information on the role-based privilege model in Aurora MySQL version 3, see Role-based privilege model.

This precheck verifies that the master user exists in the database. If the master user doesn't exist, the precheck fails. To allow the upgrade to proceed, re-create the master user by resetting the master user password, or by manually creating the user. Then retry the upgrade. For more information on resetting the master user password, see Changing the password for the database master user.

Example output:

{
  "id": "auroraUpgradeCheckForMasterUser",
  "title": "Check if master user exists",
  "status": "OK",
  "description": "Throws error if master user has been dropped!",
  "documentationLink": "https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.MasterAccounts.html",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "all",
        "description": "Your Master User on host '%' has been dropped. To proceed with the upgrade, recreate the master user `reinvent` on default host '%'"
      }
  ]
}

After you reset your master user password, the precheck will pass, and you can retry the upgrade.

The following example uses the AWS CLI to reset the password. Password changes are applied immediately.

aws rds modify-db-cluster \
    --db-cluster-identifier my-db-cluster \
    --master-user-password my-new-password

Then the precheck succeeds.

{
  "id": "auroraUpgradeCheckForMasterUser",
  title": "Check if master user exists",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Check for geometry columns on prefix indexes

As of MySQL 8.0.12 , you can no longer create a prefixed index on a column using the GEOMETRY data type. For more information, see WL#11808.

If any such indexes exist, your upgrade will fail. To resolve the issue, drop the prefixed GEOMETRY indexes on the tables mentioned in the precheck failure.

Example output:

{
  "id": "auroraUpgradeCheckForPrefixIndexOnGeometryColumns",
  "title": "Check for geometry columns on prefix indexs",
  "status": "OK",
  "description": "Consider dropping the prefix Indexes of geometry columns and restart the upgrade.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.geom_index_prefix",
        "description": "Table `test`.`geom_index_prefix` has an index `LatLon` on geometry column/s. Mysql 8.0 does not support this type of index on a geometry column https://dev.mysql.com/worklog/task/?id=11808. To upgrade to MySQL 8.0, Run 'DROP INDEX `LatLon` ON `test`.`geom_index_prefix`;"
      }
  ]
}

The precheck reports an error because the test.geom_index_prefix table contains an index with a prefix on a GEOMETRY column.

After you drop this index, the precheck succeeds.

{
  "id": "auroraUpgradeCheckForPrefixIndexOnGeometryColumns",
  "title": "Check for geometry columns on prefix indexs",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Check for inconsistency related to special characters in stored procedures

Before MySQL 8.0, database names, table names, and other objects corresponded to files in the data directory, that is, file-based metadata. As part of the upgrade to MySQL 8.0, all database objects are migrated to the new internal data dictionary tables that are stored in the mysql schema to support the newly implemented Atomic Data Dictionary. As part of migrating stored procedures, the procedure definition and body for each procedure is validated as it's ingested into the new data dictionary.

Before MySQL 8, in some cases it was possible to create stored routines, or insert directly into the mysql.proc table, procedures that contained special characters. For example, you could create a stored procedure that contained a comment with the noncompliant, non-breaking space character \xa0. If any such procedures are encountered, the upgrade fails.

This precheck validates that the bodies and definitions of your stored procedures don't contain any such characters. To allow the upgrade to proceed, re-create these stored procedures without any hidden or special characters.

Example output:

{
  "id": "auroraUpgradeCheckForSpecialCharactersInProcedures",
  "title": "Check for inconsistency related to special characters in stored procedures.",
  "status": "OK",
  "description": "Following procedure(s) has special characters inconsistency.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "information_schema.routines",
        "description": "Data Dictionary Metadata is inconsistent for the procedure `get_version_proc` in the database `test` due to usage of special characters in procedure body. To avoid that, drop and recreate the procedure without any special characters before proceeding with the Upgrade."
      }
  ]
}

The precheck reports that the DB cluster contains a procedure called get_version_proc in the test database that contains special characters in the procedure body.

After dropping and re-creating the stored procedure, the precheck succeeds, allowing the upgrade to proceed.

{
  "id": "auroraUpgradeCheckForSpecialCharactersInProcedures",
  "title": "Check for inconsistency related to special characters in stored procedures.",
  "status": "OK",
  "detectedProblems": []
},

Precheck level: Error

Check object type mismatch for sys schema

The sys schema is a set of objects and views in a MySQL database that helps users troubleshoot, optimize, and monitor their DB instances. When performing a major version upgrade from Aurora MySQL version 2 to version 3, the sys schema views are re-created and updated to the new Aurora MySQL version 3 definitions.

As part of the upgrade, if any objects in the sys schema are defined using storage engines (sys_config/BASE TABLE in INFORMATION_SCHEMA.TABLES), rather than views, the upgrade will fail. Such tables can be found in the information_schema.tables table. This is not an expected behavior, but in some cases can occur due to user error.

This precheck validates all sys schema objects to ensure that they use the correct table definitions, and that views aren't mistakenly defined as InnoDB or MyISAM tables. To resolve the issue, manually fix any returned objects by renaming or dropping them. Then retry the upgrade.

Example output:

{
  "id": "auroraUpgradeCheckForSysSchemaObjectTypeMismatch",
  "title": "Check object type mismatch for sys schema.",
  "status": "OK",
  "description": "Database contains objects with type mismatch for sys schema.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "sys.waits_global_by_latency",
        "description": "Your object sys.waits_global_by_latency has a type mismatch. To fix the inconsistency we recommend to rename or remove the object before upgrading (use RENAME TABLE command). "
      }
  ]
}

The precheck reports that the sys.waits_global_by_latency view in the sys schema has a type mismatch that is blocking the upgrade from proceeding.

After logging into the DB instance, you can see that this object is defined as a InnoDB table, when it should be a view.

mysql> show create table sys.waits_global_by_latency\G
*************************** 1. row ***************************
       Table: waits_global_by_latency
Create Table: CREATE TABLE `waits_global_by_latency` (
  `events` varchar(128) DEFAULT NULL,
  `total` bigint(20) unsigned DEFAULT NULL,
  `total_latency` text,
  `avg_latency` text,
  `max_latency` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

To resolve this issue we can either drop and re-create the view with the correct definition or rename it. During the upgrade process, it will be automatically created with the correct table definition.

mysql> RENAME TABLE sys.waits_global_by_latency to sys.waits_global_by_latency_old;
Query OK, 0 rows affected (0.01 sec)

After doing this, the precheck passes.

{
  "id": "auroraUpgradeCheckForSysSchemaObjectTypeMismatch",
  "title": "Check object type mismatch for sys schema.",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Check upper limit for column name in view

The maximum permitted length of a column name in MySQL is 64 characters. Before MySQL 8.0, in some cases it was possible to create a view with a column name larger than 64 characters. If any such views exist on your database instance, a precheck error is returned, and the upgrade will fail. To allow the upgrade to proceed, you must re-create the views in question, making sure that their column lengths are less than 64 characters. Then retry the upgrade.

Example output:

{
  "id": "auroraUpgradeCheckForViewColumnNameLength",
  "title": "Check for upperbound limit related to column name in view.",
  "status": "OK",
  "description": "Following view(s) has column(s) with length greater than 64.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.colname_view_test.col2_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad",
        "description": "View `test`.`colname_view_test`has column `col2_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad` with invalid column name length. To avoid Upgrade errors, view should be altered by renaming the column name so that its length is not 0 and doesn't exceed 64."
      }
  ]
}

The precheck reports that the test.colname_view_test view contains a column col2_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad that exceeds the max permitted column length of 64 characters.

Looking at the view definition, we can see the offending column.

mysql> desc `test`.`colname_view_test`;
+------------------------------------------------------------------+-------------+------+-----+---------+-------+
| Field                                                            | Type        | Null | Key | Default | Extra |
+------------------------------------------------------------------+-------------+------+-----+---------+-------+
| col1                                                             | varchar(20) | YES  |     | NULL    |       |
| col2_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad_pad | int(11)     | YES  |     | NULL    |       |
+------------------------------------------------------------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

To allow the upgrade to proceed, re-create the view, making sure that the column length doesn't exceed 64 characters.

mysql> drop view `test`.`colname_view_test`;
Query OK, 0 rows affected (0.01 sec)

mysql> create view `test`.`colname_view_test`(col1, col2_nopad) as select inf, fodcol from test;
Query OK, 0 rows affected (0.01 sec)

mysql> desc `test`.`colname_view_test`;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| col1       | varchar(20) | YES  |     | NULL    |       |
| col2_nopad | int(11)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

After doing this, the precheck succeeds.

{
  "id": "auroraUpgradeCheckForViewColumnNameLength",
  "title": "Check for upperbound limit related to column name in view.",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Check for tables with indexes defined with a prefix length greater than 255 bytes on tiny columns

When creating an index on a column using a binary data type in MySQL, you must add a prefix length in the index definition. Before MySQL 8.0, in some cases it was possible to specify a prefix length larger than the maximum allowed size of such data types. An example is TINYTEXT and TINYBLOB columns, where the maximum permitted data size is 255 bytes, but index prefixes larger than this were permitted. For more information, see InnoDB limits in the MySQL documentation.

If this precheck fails, drop the offending index or reduce the prefix length of TINYTEXT and TINYBLOB columns of the index to less than 255 bytes. Then retry the upgrade.

Example output:

{
  "id": "auroraUpgradeCheckIndexLengthLimitOnTinyColumns",
  "title": "Check for the tables with indexes defined with prefix length greater than 255 bytes on tiny columns",
  "status": "OK",
  "description": "Prefix length of the indexes defined on tiny columns cannot exceed 255 bytes. With utf8mb4 char set, this limits the prefix length supported upto 63 characters only. A larger prefix length was allowed in MySQL5.7 using innodb_large_prefix parameter. This parameter is deprecated in MySQL 8.0.",
  "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html, https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.tintxt_prefixed_index.col1",
        "description": "Index 'PRIMARY' on tinytext/tinyblob column `col1` of table `test.tintxt_prefixed_index` is defined with prefix length exceeding 255 bytes. Reduce the prefix length to <=255 bytes depending on character set used. For utf8mb4, it should be <=63."
      }
  ]
}

The precheck reports an error for the test.tintxt_prefixed_index table, because it has an Index PRIMARY that has a prefix larger than 255 bytes on a TINYTEXT or TINYBLOB column.

Looking at the definition for this table, we can see that the primary key has a prefix of 65 on the TINYTEXT column col1. Because the table is defined using the utf8mb4 character set, which stores 4 bytes per character, the prefix exceeds the 255-byte limit.

mysql> show create table `test`.`tintxt_prefixed_index`\G
*************************** 1. row ***************************
       Table: tintxt_prefixed_index
Create Table: CREATE TABLE `tintxt_prefixed_index` (
  `col1` tinytext NOT NULL,
  `col2` tinytext,
  `col_id` tinytext,
  PRIMARY KEY (`col1`(65))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

Modifying the index prefix to 63 while using the utf8mb4 character set will allow the upgrade to proceed.

mysql> alter table `test`.`tintxt_prefixed_index` drop PRIMARY KEY, ADD  PRIMARY KEY (`col1`(63));
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

After doing this, the precheck succeeds.

{
  "id": "auroraUpgradeCheckIndexLengthLimitOnTinyColumns",
  "title": "Check for the tables with indexes defined with prefix length greater than 255 bytes on tiny columns",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Error

Check missing InnoDB metadata inconsistency for the mysql.host table

This is an internal-only precheck carried out by the RDS service. Any errors will be automatically handled on upgrade and can be safely ignored.

If you encounter any errors with this precheck, open a case with AWS Support to request that the metadata inconsistency be resolved. Alternatively, you can retry the upgrade by performing a logical dump, then restoring to a new Aurora MySQL version 3 DB cluster.

Precheck level: Warning

New default authentication plugin considerations

In MySQL 8.0, the caching_sha2_password authentication plugin was introduced, providing more secure password encryption and better performance than the deprecated mysql_native_password plugin. For Aurora MySQL version 3, the default authentication plugin used for database users is the mysql_native_password plugin.

This precheck warns that this plugin will be removed and the default changed in a future major version release. Consider evaluating the compatibility of your application clients and users ahead of this change.

For more information, see caching_sha2_password compatibility issues and solutions in the MySQL documentation.

Example output:

{
  "id": "defaultAuthenticationPlugin",
  "title": "New default authentication plugin considerations",
  "description": "Warning: The new default authentication plugin 'caching_sha2_password' offers more secure password hashing than previously used 'mysql_native_password' (and consequent improved client connection authentication). However, it also has compatibility implications that may affect existing MySQL installations. If your MySQL installation must serve pre-8.0 clients and you encounter compatibility issues after upgrading, the simplest way to address those issues is to reconfigure the server to revert to the previous default authentication plugin (mysql_native_password). For example, use these lines in the server option file:\n\n[mysqld]\ndefault_authentication_plugin=mysql_native_password\n\nHowever, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security.\nIf you are using replication please take time to understand how the authentication plugin changes may impact you.",
  "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues\nhttps://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication"
},

Precheck level: Warning

Usage of obsolete MAXDB sql_mode flag

In MySQL 8.0, a number of deprecated sql_mode system variable options were removed, one of which was MAXDB. This precheck examines all currently connected sessions, along with routines and triggers, to ensure that none have sql_mode set to any combination that includes MAXDB.

Example output:

{
  "id": "maxdbFlagCheck",
  "title": "Usage of obsolete MAXDB sql_mode flag",
  "status": "OK",
  "description": "Warning: The following DB objects have the obsolete MAXDB option persisted for sql_mode, which will be cleared during the upgrade. It can potentially change the datatype DATETIME into TIMESTAMP if it is used inside object's definition, and this in turn can change the behavior in case of dates earlier than 1970 or later than 2037. If this is a concern, please redefine these objects so that they do not rely on the MAXDB flag before running the upgrade.",
  "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "test.maxdb_stored_routine",
        "description": "PROCEDURE uses obsolete MAXDB sql_mode",
        "dbObjectType": "Routine"
      }
  ]
}

The precheck reports that the test.maxdb_stored_routine routine contains a unsupported sql_mode option.

After logging into the database, you can see in the routine definition that sql_mode contains MAXDB.

 > SHOW CREATE PROCEDURE test.maxdb_stored_routine\G

*************************** 1. row ***************************
           Procedure: maxdb_stored_routine
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
    Create Procedure: CREATE DEFINER="msandbox"@"localhost" PROCEDURE "maxdb_stored_routine"()
BEGIN
    SELECT * FROM test;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

To resolve the issue, re-create the procedure after setting the correct sql_mode on the client.

Re-create the procedure without the unsupported sql_mode.

mysql > set sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql > DROP PROCEDURE test.maxdb_stored_routine\G
Query OK, 0 rows affected (0.00 sec)

mysql >
mysql > DELIMITER $$
mysql >
mysql > CREATE PROCEDURE test.maxdb_stored_routine()
    -> SQL SECURITY DEFINER
    -> BEGIN
    ->     SELECT * FROM test;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql >
mysql > DELIMITER ;
mysql > show create procedure test.maxdb_stored_routine\G
*************************** 1. row ***************************
           Procedure: maxdb_stored_routine
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE
    Create Procedure: CREATE DEFINER="msandbox"@"localhost" PROCEDURE "maxdb_stored_routine"()
BEGIN
    SELECT * FROM test;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

The precheck succeeds.

{
  "id": "maxdbFlagCheck",
  "title": "Usage of obsolete MAXDB sql_mode flag",
  "status": "OK",
  "detectedProblems": []
}

Precheck level: Warning

Check for deprecated usage of single dollar signs in object names

As of MySQL 8.0.32, use of the dollar sign ($) as the first character of an unquoted identifier is deprecated. If you have any schemas, tables, views, columns, or routines containing a $ as the first character, this precheck returns a warning. While this warning doesn't block the upgrade from proceeding, we recommend that you take action soon to resolve this. From MySQL 8.4 any such identifiers will return a syntax error rather than a warning.

Example output:

{
  "id": "mysqlDollarSignNameCheck",
  "title": "Check for deprecated usage of single dollar signs in object names",
  "status": "OK",
  "description": "Warning: The following objects have names with deprecated usage of dollar sign ($) at the begining of the identifier. To correct this warning, ensure, that names starting with dollar sign, also end with it, similary to quotes ($example$). ",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "test.$deprecated_syntx",
        "description": " name starts with $ sign."
      }
  ]
},

The precheck reports a warning because the $deprecated_syntx table in the test schema contains a $ as the first character.

Precheck level: Warning

Usage of database objects with names conflicting with new reserved keywords

This check is similar to the routineSyntaxCheck, in that it checks for usage of database objects with names conflicting with new reserved keywords. While they don't block upgrades, you need to evaluate warnings carefully.

Example output:

Using the previous example with the table named except, the precheck returns a warning:

{
  "id": "reservedKeywordsCheck",
  "title": "Usage of db objects with names conflicting with new reserved keywords",
  "status": "OK",
  "description": "Warning: The following objects have names that conflict with new reserved keywords. Ensure queries sent by your applications use `quotes` when referring to them or they will result in errors.",
  "documentationLink": "https://dev.mysql.com/doc/refman/en/keywords.html",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "test.except",
        "description": "Table name",
        "dbObjectType": "Table"
      }
  ]
}

This warning lets you know that there might be some application queries to review. If your application queries aren't correctly escaping string literals, they might experience errors after upgrading to MySQL 8.0. Review your applications to confirm, testing against a clone or snapshot of your Aurora MySQL DB cluster running on version 3.

Example of a non-escaped application query that will fail after upgrading:

SELECT * FROM escape;

Example of a correctly escaped application query that will succeed after upgrading:

SELECT * FROM 'escape';

Precheck level: Warning

Usage of utf8mb3 character set

In MySQL 8.0 the utf8mb3 character set is deprecated, and will be removed in a future MySQL major version. This precheck is implemented to raise a warning if any database objects using this character set are detected. While this won't block an upgrade from proceeding, we highly recommend that you think about migrating tables to the utf8mb4 character set, which is the default as of MySQL 8.0. For more information on utf8mb3 and utf8mb4, see Converting between 3-byte and 4-byte Unicode character sets in the MySQL documentation.

Example output:

{
  "id": "utf8mb3",
  "title": "Usage of utf8mb3 charset",
  "status": "OK",
  "description": "Warning: The following objects use the deprecated utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support. The utf8mb3 character is subject to removal in the future.",
  "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "test.t1.col1",
        "description": "column's default character set: utf8",
        "dbObjectType": "Column"
      },
      {
        "level": "Warning",
        "dbObject": "test.t1.col2",
        "description": "column's default character set: utf8",
        "dbObjectType": "Column"
      }
  ]
}

To resolve this issue, you rebuild the objects and tables referenced. For more information and prerequisites before doing so, see Converting between 3-byte and 4-byte Unicode character sets in the MySQL documentation.

Precheck level: Warning

Zero date, datetime, and timestamp values

MySQL now enforces stricter rules regarding the use of zero values in date, datetime, and timestamp columns. We recommend that you use the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes in conjunction with strict mode, as they will be merged with strict mode in a future MySQL release.

If the sql_mode setting for any of your database connections, at the time of running the precheck, doesn't include these modes, a warning is raised in the precheck. Users might still be able to insert date, datetime, and timestamp values containing zero values. However, we strongly advise replacing any zero values with valid ones, as their behavior might change in the future and they might not function correctly. As this is a warning, it won't block upgrades, but we recommend that you start planning to take action.

Example output:

{
  "id": "zeroDatesCheck",
  "title": "Zero Date, Datetime, and Timestamp values",
  "status": "OK",
  "description": "Warning: By default zero date/datetime/timestamp values are no longer allowed in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in SQL_MODE by default. These modes should be used with strict mode as they will be merged with strict mode in a future release. If you do not include these modes in your SQL_MODE setting, you are able to insert date/datetime/timestamp values that contain zeros. It is strongly advised to replace zero values with valid ones, as they may not work correctly in the future.",
  "documentationLink": "https://lefred.be/content/mysql-8-0-and-wrong-dates/",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "global.sql_mode",
        "description": "does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE which allows insertion of zero dates"
      },
      {
        "level": "Warning",
        "dbObject": "session.sql_mode",
        "description": " of 10 session(s) does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE which allows insertion of zero dates"
      }
  ]
}

Precheck level: Warning

Checks whether the rollback segment history list length for the cluster is high

As mentioned in auroraUpgradeCheckForIncompleteXATransactions, while running the major version upgrade process, it is essential that the Aurora MySQL version 2 DB instance undergo a clean shutdown. This ensures that all transactions are committed or rolled back, and that InnoDB has purged all undo log records.

If your DB cluster has a high rollback segment history list length (HLL), it can prolong the amount of time that InnoDB takes to complete its purge of undo log records, leading to extended downtime during the major version upgrade process. If the precheck detects that the HLL on your DB cluster is high, it raises a warning. While this doesn't block your upgrade from proceeding, we recommend that you closely monitor the HLL on your DB cluster. Keeping it at low levels reduces the downtime required during a major version upgrade. For more information on monitoring HLL, see The InnoDB history list length increased significantly.

Example output:

{
  "id": "auroraUpgradeCheckForRollbackSegmentHistoryLength",
  "title": "Checks if the rollback segment history length for the cluster is high",
  "status": "OK",
  "description": "Rollback Segment History length is greater than 1M. Upgrade may take longer time.",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "information_schema.innodb_metrics",
        "description": "The InnoDB undo history list length('trx_rseg_history_len') is 82989114. Upgrade may take longer due to purging of undo information for old row versions."
      }
  ]
}

The precheck returns a warning because it detected the InnoDB undo HLL was high on the database cluster (82989114). Although the upgrade proceeds, depending on the amount of undo to purge, it can extend the downtime required during the upgrade process.

We recommend that you investigate open transactions on your DB cluster before running the upgrade in production, to make sure your HLL is kept at a manageable size.

Precheck level: Warning

Checks whether there are many uncommitted row modifications

As mentioned in auroraUpgradeCheckForIncompleteXATransactions, while running the major version upgrade process, it is essential that the Aurora MySQL version 2 DB instance undergo a clean shutdown. This ensures that all transactions are committed or rolled back, and that InnoDB has purged all undo log records.

If your DB cluster has transactions that have modified a large number of rows, it can prolong the amount of time InnoDB takes to complete a rollback of this transaction as part of the clean shutdown process. If the precheck finds long-running transactions, with a large number of modified rows on your DB cluster, it raises a warning. While this doesn't block your upgrade from proceeding, we recommend that you closely monitor the size of active transactions on your DB cluster. Keeping it at low levels reduces the downtime required during a major version upgrade.

Example output:

{
  "id": "auroraUpgradeCheckForUncommittedRowModifications",
  "title": "Checks if there are many uncommitted modifications to rows",
  "status": "OK",
  "description": "Database contains uncommitted row changes greater than 10M. Upgrade may take longer time.",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "information_schema.innodb_trx",
        "description": "The database contains 11000000 uncommitted row change(s) in 1 transaction(s). Upgrade may take longer due to transaction rollback."
      }
  ]
},

The precheck reports that the DB cluster contains a transaction with 11,000,000 uncommitted row changes that will need to be rolled back during the clean shutdown process. The upgrade will proceed, but to reduce downtime during the upgrade process, we recommend that you monitor and investigate this before running the upgrade on your production clusters.

To view active transactions on your writer DB instance, you can use the information_schema.innodb_trx table. The following query on the writer DB instance shows current transactions, run time, state, and modified rows for the DB cluster.

# Example of uncommitted transaction
mysql> SELECT trx_started,
       TIME_TO_SEC(TIMEDIFF(now(), trx_started)) AS seconds_trx_has_been_running,
       trx_mysql_thread_id AS show_processlist_connection_id,
       trx_id,
       trx_state,
       trx_rows_modified AS rows_modified
FROM information_schema.innodb_trx;
+---------------------+------------------------------+--------------------------------+----------+-----------+---------------+
| trx_started         | seconds_trx_has_been_running | show_processlist_connection_id | trx_id   | trx_state | rows_modified |
+---------------------+------------------------------+--------------------------------+----------+-----------+---------------+
| 2024-08-12 18:32:52 |                         1592 |                          20041 | 52866130 | RUNNING   |      11000000 |
+---------------------+------------------------------+--------------------------------+----------+-----------+---------------+
1 row in set (0.01 sec)

# Example of transaction rolling back
mysql> SELECT trx_started,
       TIME_TO_SEC(TIMEDIFF(now(), trx_started)) AS seconds_trx_has_been_running,
       trx_mysql_thread_id AS show_processlist_connection_id,
       trx_id,
       trx_state,
       trx_rows_modified AS rows_modified
FROM information_schema.innodb_trx;
+---------------------+------------------------------+--------------------------------+----------+--------------+---------------+
| trx_started         | seconds_trx_has_been_running | show_processlist_connection_id | trx_id   | trx_state    | rows_modified |
+---------------------+------------------------------+--------------------------------+----------+--------------+---------------+
| 2024-08-12 18:32:52 |                         1719 |                          20041 | 52866130 | ROLLING BACK |      10680479 |
+---------------------+------------------------------+--------------------------------+----------+--------------+---------------+
1 row in set (0.01 sec)

After the transaction is committed or rolled back, the precheck no longer returns a warning. Consult the MySQL documentation and your application team before rolling back any large transactions, as rollback can take some time to complete, depending on transaction size.

{
  "id": "auroraUpgradeCheckForUncommittedRowModifications",
  "title": "Checks if there are many uncommitted modifications to rows",
  "status": "OK",
  "detectedProblems": []
},

For more information on optimizing InnoDB transaction management, and the potential impact of running and rolling back large transactions on MySQL database instances, see Optimizing InnoDB transaction management in the MySQL documentation.

Precheck level: Notice

Usage of obsolete sql_mode flags

In addition to MAXDB, a number of other sql_mode options have been removed: DB2, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, and NO_TABLE_OPTIONS. As of MySQL 8.0, none of these values can be assigned to the sql_mode system variable. If this precheck finds any open sessions using these sql_mode settings, make sure that your DB instance and DB cluster parameter groups, and client applications and configurations, are updated to disable them.- For more information, see the MySQL documentation.

Example output:

{
  "id": "sqlModeFlagCheck",
  "title": "Usage of obsolete sql_mode flags",
  "status": "OK",
  "detectedProblems": []
}

To resolve any of these precheck failures, see maxdbFlagCheck.

Precheck level: Error, Warning, or Notice

Issues reported by the check table x for upgrade command

Before starting the upgrade to Aurora MySQL version 3, check table for upgrade is run on each table in the user schemas in your DB cluster. This precheck isn't the same as checkTableMysqlSchema.

The check table for upgrade command examines tables for any potential issues that might arise during an upgrade to a newer version of MySQL. Running this command before attempting an upgrade can help identify and resolve any incompatibilities ahead of time, making the actual upgrade process smoother.

This command performs various checks on each table, such as the following:

Unlike other prechecks, it can return an error, warning, or notice depending on the check table output. If this precheck returns any tables, review them carefully, along with the return code and message before initiating the upgrade. For more information, see CHECK TABLE statement in the MySQL documentation.

Here we provide an error example and a warning example.

Error example:

{
  "id": "checkTableOutput",
  "title": "Issues reported by 'check table x for upgrade' command",
  "status": "OK",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.parent",
        "description": "Table 'test.parent' doesn't exist"
      }
  ]
},

The precheck reports an error that the test.parent table doesn't exist.

The mysql-error.log file for the writer DB instance shows that there is a foreign key error.

2024-08-13T15:32:10.676893Z 62 [Warning] InnoDB: Load table `test`.`parent` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2024-08-13T15:32:10.676905Z 62 [Warning] InnoDB: Cannot open table test/parent from the internal data dictionary of InnoDB though the .frm file for the table exists.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

Log into the writer DB instance and run show engine innodb status\G to get more information on the foreign key error.

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2024-08-13 15:33:33 0x14ef7b8a1700 INNODB MONITOR OUTPUT
=====================================
.
.
.
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2024-08-13 15:32:10 0x14ef6dbbb700 Error in foreign key constraint of table test/child:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT `fk_pname` FOREIGN KEY (`p_name`) REFERENCES `parent` (`name`)
The index in the foreign key in table is p_name_idx
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.
.
.

The LATEST FOREIGN KEY ERROR message reports that the fk_pname foreign key constraint in the test.child table, which references the test.parent table,has either a missing index or data type mismatch. The MySQL documentation on foreign key constraints states that columns referenced in a foreign key must have an associated index, and the parent/child columns must use the same data type.

To verify whether this is related to a missing index or data type mismatch, log into the database and check the table definitions by temporarily disabling the session variable foreign_key_checks. After doing so, we can see that the child constraint in question (fk_pname) uses p_name varchar(20) CHARACTER SET latin1 DEFAULT NULL to reference the parent table name varchar(20) NOT NULL. The parent table uses DEFAULT CHARSET=utf8, but the child table’s p_name column uses latin1, so the data type mismatch error is thrown.

mysql> show create table parent\G
ERROR 1146 (42S02): Table 'test.parent' doesn't exist

mysql> show create table child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) NOT NULL,
  `p_name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `p_name_idx` (`p_name`),
  CONSTRAINT `fk_pname` FOREIGN KEY (`p_name`) REFERENCES `parent` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table parent\G
*************************** 1. row ***************************
       Table: parent
Create Table: CREATE TABLE `parent` (
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) NOT NULL,
  `p_name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `p_name_idx` (`p_name`),
  CONSTRAINT `fk_pname` FOREIGN KEY (`p_name`) REFERENCES `parent` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

To resolve this issue, we can either change the child table to use the same character set as the parent, or change the parent to use the same character set as the child table. Here, because the child table explicitly uses latin1 in the p_name column definition, we run ALTER TABLE to modify the character set to utf8.

mysql> alter table child modify p_name varchar(20) character set utf8 DEFAULT NULL;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)

After doing so, the precheck passes, and the upgrade can proceed.

Warning example:

{
  "id": "checkTableOutput",
  "title": "Issues reported by 'check table x for upgrade' command",
  "status": "OK",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "test.orders",
        "description": "Trigger test.orders.delete_audit_trigg does not have CREATED attribute."
      }
  ]
}

The precheck reports a warning for the delete_audit_trigg trigger on the test.orders table because it doesn't have a CREATED attribute. According to Checking version compatibility in the MySQL documentation, this message is informational, and is printed for triggers created before MySQL 5.7.2.

Because this is a warning, it doesn't block the upgrade from proceeding. However, if you wish to resolve the issue, you can re-create the trigger in question, and the precheck succeeds with no warnings.

{
  "id": "checkTableOutput",
  "title": "Issues reported by 'check table x for upgrade' command",
  "status": "OK",
  "detectedProblems": []
},