' 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.
Before rebuilding tablespaces, see [Online DDL operations](https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html) in the MySQL documentation to understand the effects of locking and data movement on foreground transactions.
```
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": []
}
```
**auroraGetDanglingFulltextIndex**
**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 \$176012](https://bugs.mysql.com/bug.php?id=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": []
},
```
**auroraUpgradeCheckForDatafilePathInconsistency**
**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": []
}
```
**auroraUpgradeCheckForFtsSpaceIdZero**
**Precheck level: Error**
**Check for full-text index with space ID as zero**
In MySQL, when you add a [full-text index](https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html) to an InnoDB table, a number of auxiliary index tablespaces are created. Due to a [bug](https://bugs.mysql.com/bug.php?id=72132) 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](https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_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.
Before rebuilding tablespaces, see [Partitioning operations](https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-partitioning) in the MySQL documentation to understand the effects of locking and data movement on foreground transactions.
```
{
"id": "auroraUpgradeCheckForFtsSpaceIdZero",
"title": "Check for fulltext index with space id as zero",
"status": "OK",
"detectedProblems": []
}
```
**auroraUpgradeCheckForIncompleteXATransactions**
**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](https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_slow_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](https://dev.mysql.com/doc/refman/5.7/en/xa.html) 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](https://dev.mysql.com/doc/refman/5.7/en/xa-statements.html) in the MySQL documentation. For more information on XA transaction states, see [XA transaction states](https://dev.mysql.com/doc/refman/5.7/en/xa-states.html) 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\$1schema.innodb\$1trx](https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-trx-table.html) table and the `XA RECOVER` output for more information.
Before committing or rolling back a transaction, we recommend that you review the [MySQL documentation](https://dev.mysql.com/doc/refman/5.7/en/xa-restrictions.html) and your application requirements.
```
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": []
}
```
**auroraUpgradeCheckForInstanceLimit**
**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](Concepts.DBInstanceClass.md) 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.
**auroraUpgradeCheckForInternalUsers**
**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": []
}
```
**auroraUpgradeCheckForInvalidUtf8mb3CharacterStringInViews**
**Precheck level: Error**
**Check for invalid utf8mb3 characters in view definition**
This precheck identifies views that contain comments with invalid `utf8mb3` character encoding. In MySQL 8.0, stricter validation is applied to character encoding in metadata, including view comments. If any view definition contains characters that are not valid in the `utf8mb3` character set, the upgrade fails.
To resolve this issue, modify the view definition to remove or replace any non-BMP characters before you attempt the upgrade.
**Example output:**
```
{
"id": "auroraUpgradeCheckForInvalidUtf8mb3CharacterStringInViews",
"title": "Check for invalid utf8mb3 character string.",
"status": "OK",
"description": "Definition of following view(s) has/have invalid utf8mb3 character string.",
"detectedProblems": [
{
"level": "Error",
"dbObject": "precheck.utf8mb3_invalid_char_view",
"description": "Definition of view precheck.utf8mb3_invalid_char_view contains an invalid utf8mb3 character string. This is due to https://bugs.mysql.com/bug.php?id=110177. To fix the inconsistency, we recommend you to modify the view definition to not use non-BMP characters and try the upgrade again."
}
]
},
```
The precheck reports that the `utf8mb3_invalid_char_view` view definition contains invalid `utf8mb3` characters in its definition.
To resolve this issue, identify the view that contains the unsupported characters and update the comments. First, examine the view structure and identify comments.
```
MySQL> SHOW CREATE VIEW precheck.utf8mb3_invalid_char_view\G
*************************** 1. row ***************************
View: utf8mb3_invalid_char_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`%` SQL SECURITY DEFINER VIEW `utf8mb3_invalid_char_view` AS select 'This row contains a dolphin 🐬' AS `message`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set, 1 warning (0.00 sec)
```
Once you've identified the view that contains the error, replace the view with the `CREATE OR REPLACE VIEW` statement.
```
MySQL> CREATE OR REPLACE VIEW precheck.utf8mb3_invalid_char_view AS select 'This view definition to not use non-BMP characters' AS message;
```
After updating all view definitions that contain unsupported characters, the precheck passes and the upgrade can proceed.
```
{
"id": "auroraUpgradeCheckForInvalidUtf8mb3ColumnComments",
"title": "Check for invalid utf8mb3 column comments.",
"status": "OK",
"detectedProblems": []
}
```
**auroraUpgradeCheckForInvalidUtf8mb3ColumnComments**
**Precheck level: Error**
**Check for invalid utf8mb3 column comments**
This precheck identifies tables that contain column comments with invalid `utf8mb3` character encoding. In MySQL 8.0, stricter validation is applied to character encoding in metadata, including column comments. If any column comments contain characters that are not valid in the utf8mb3 character set, the upgrade will fail.
To resolve this issue, you must modify the column comments to remove or replace any non-BMP characters before attempting the upgrade. You can use the `ALTER TABLE` statement to update the column comments.
**Example output:**
```
{
"id": "auroraUpgradeCheckForInvalidUtf8mb3ColumnComments",
"title": "Check for invalid utf8mb3 column comments.",
"status": "OK",
"description": "Following table(s) has/have invalid utf8mb3 comments on the column/columns.",
"detectedProblems": [
{
"level": "Error",
"dbObject": "test.t2",
"description": "Table test.t2 has invalid utf8mb3 comments in it's column/columns. This is due to non-BMP characters in the comment field. To fix the inconsistency, we recommend you to modify comment fields to not use non-BMP characters and try the upgrade again."
}
]
}
```
The precheck reports that the `test.t2` table contains invalid `utf8mb3` characters in one or more column comments, specifically due to the presence of non-BMP characters.
To resolve this issue, you can identify the problematic columns and update their comments. First, examine the table structure to identify columns with comments:
```
mysql> SHOW CREATE TABLE test.t2\G
```
Once you've identified the columns with problematic comments, update them using the `ALTER TABLE` statement. For example:
```
mysql> ALTER TABLE test.t2 MODIFY COLUMN column_name data_type COMMENT 'Updated comment without non-BMP characters';
```
Alternatively, you can remove the comment entirely:
```
mysql> ALTER TABLE test.t2 MODIFY COLUMN column_name data_type COMMENT '';
```
After updating all problematic column comments, the precheck will pass and the upgrade can proceed:
```
{
"id": "auroraUpgradeCheckForInvalidUtf8mb3ColumnComments",
"title": "Check for invalid utf8mb3 column comments.",
"status": "OK",
"detectedProblems": []
}
```
Before modifying column comments, ensure that any application code or documentation that relies on these comments is updated accordingly. Consider migrating to the utf8mb4 character set for better Unicode support if your application requires non-BMP characters.
**auroraUpgradeCheckForInvalidUtf8mb3IndexComments**
**Precheck level: Error**
**Check for invalid utf8mb3 index comments**
This precheck identifies tables that contain index comments with invalid `utf8mb3` character encoding. In MySQL 8.0, stricter validation is applied to character encoding in metadata, including index comments. If any index comments contain characters that are not valid in the `utf8mb3` character set, the upgrade fails.
To resolve this issue, you must modify the index comments to remove or replace any non-BMP characters before attempting the upgrade.
**Example output:**
```
{
"id": "auroraUpgradeCheckForInvalidUtf8mb3IndexComments",
"title": "Check for invalid utf8mb3 index comments.",
"status": "OK",
"description": "Following table(s) has/have invalid utf8mb3 comments on the index.",
"detectedProblems": [
{
"level": "Error",
"dbObject": "precheck.utf8mb3_tab_index_comment",
"description": "Table precheck.utf8mb3_tab_index_comment has invalid utf8mb3 comments in it's index. This is due to https://bugs.mysql.com/bug.php?id=110177. To fix the inconsistency, we recommend you to modify comment fields to not use non-BMP characters and try the upgrade again."
}
]
},
```
The precheck reports that the `utf8mb3_tab_index_comment` table contains invalid `utf8mb3` characters in one or more column comments, specifically due to the presence of non-BMP characters.
To resolve this issue, first, examine the table structure to identify the index with problematic comments.
```
MySQL> SHOW CREATE TABLE precheck.utf8mb3_tab_index_comment\G
*************************** 1. row ***************************
Table: utf8mb3_tab_index_comment
Create Table: CREATE TABLE `utf8mb3_tab_index_comment` (
`id` int(11) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
KEY `idx_name` (`name`) COMMENT 'Name index 🐬'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
```
Once you identify the index that contains comments that use unsupported characters, drop and recreate the index.
Dropping and recreating a table index can lead to downtime. We recommend that you plan and schedule this operation during maintenance.
```
MySQL> ALTER TABLE precheck.utf8mb3_tab_index_comment DROP INDEX idx_name;
MySQL> ALTER TABLE precheck.utf8mb3_tab_index_comment ADD INDEX idx_name(name);
```
The following example shows another way to recreate the index.
```
MySQL> ALTER TABLE utf8mb3_tab_index_comment DROP INDEX idx_name, ADD INDEX idx_name (name) COMMENT 'Updated comment without non-BMP characters';
```
After you remove or update all unsupported index comments, the precheck passes and the upgrade can proceed.
```
{
"id": "auroraUpgradeCheckForInvalidUtf8mb3IndexComments",
"title": "Check for invalid utf8mb3 index comments.",
"status": "OK",
"detectedProblems": []
},
```
**auroraUpgradeCheckForInvalidUtf8mb3TableComments**
**Precheck level: Error**
**Check for invalid utf8mb3 characters in table definition**
This precheck identifies tables that contain comments with invalid `utf8mb3` character encoding. In MySQL 8.0, stricter validation is applied to character encoding in metadata, including table comments. If any table comments contain characters that are not valid in the `utf8mb3` character set, the upgrade fails.
To resolve this issue, you must modify the table comments to remove or replace any non-BMP characters before attempting the upgrade.
**Example output:**
```
{
"id": "auroraUpgradeCheckForInvalidUtf8mb3TableComments",
"title": "Check for invalid utf8mb3 table comments.",
"status": "OK",
"description": "Following table(s) has/have invalid utf8mb3 comments.",
"detectedProblems": [
{
"level": "Error",
"dbObject": "precheck.utf8mb3_table_with_comment",
"description": "Table precheck.utf8mb3_table_with_comment has invalid utf8mb3 comments. This is due to https://bugs.mysql.com/bug.php?id=110177. To fix the inconsistency, we recommend you to modify comment fields to not use non-BMP characters and try the upgrade again."
}
]
},
```
The precheck reports invalid `utf8mb3` comments defined for the `utf8mb3_table_with_comment` tables in the test database.
To resolve this issue, identify the table that contains unsupported characters and update the comments. First, examine the table structure and identify the comments.
```
MySQL> SHOW CREATE TABLE precheck.utf8mb3_table_with_comment\G
*************************** 1. row ***************************
Table: utf8mb3_table_with_comment
Create Table: CREATE TABLE `utf8mb3_table_with_comment` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This table comment contains flag 🏳️'
1 row in set (0.00 sec)
```
Once you've identified table comments that contain unsupported chatacters, update the comments with the `ALTER TABLE` statement.
```
MySQL> ALTER TABLE precheck.utf8mb3_table_with_comment COMMENT='Updated comment without non-BMP characters';
```
Alternatively, you can remove the comment.
```
MySQL> ALTER TABLE precheck.utf8mb3_table_with_comment COMMENT='';
```
After you remove all unsupported characters from all table comments, the precheck succeeds.
```
{
"id": "auroraUpgradeCheckForInvalidUtf8mb3TableComments",
"title": "Check for invalid utf8mb3 table comments.",
"status": "OK",
"detectedProblems": []
},
```
**auroraUpgradeCheckForMasterUser**
**Precheck level: Error**
**Check whether RDS master user exists**
MySQL 8 has added a new privilege model with support for [role](https://dev.mysql.com/doc/refman/8.0/en/roles.html) and [dynamic privileges](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#static-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](UsingWithRDS.MasterAccounts.md). For more information on the role-based privilege model in Aurora MySQL version 3, see [Role-based privilege model](AuroraMySQL.Compare-80-v3.md#AuroraMySQL.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](Aurora.Modifying.md#Aurora.Modifying.Password).
**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": []
}
```
**auroraUpgradeCheckForPrefixIndexOnGeometryColumns**
**Precheck level: Error**
**Check for geometry columns on prefix indexes**
As of [MySQL 8.0.12 ](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-12.html#mysqld-8-0-12-spatial-support), you can no longer create a [prefixed](https://dev.mysql.com/doc/refman/5.7/en/column-indexes.html#column-indexes-prefix) index on a column using the [GEOMETRY](https://dev.mysql.com/doc/refman/5.7/en/gis-data-formats.html) data type. For more information, see [WL\$111808](https://dev.mysql.com/worklog/task/?id=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": []
}
```
**auroraUpgradeCheckForSpecialCharactersInProcedures**
**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](https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-file-removal.html). 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](https://en.wikipedia.org/wiki/Non-breaking_space) `\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": []
},
```
**auroraUpgradeCheckForSysSchemaObjectTypeMismatch**
**Precheck level: Error**
**Check object type mismatch for `sys` schema**
The [sys schema](https://dev.mysql.com/doc/refman/8.0/en/sys-schema.html) 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\$1SCHEMA.TABLES](https://dev.mysql.com/doc/refman/5.7/en/information-schema-tables-table.html)), 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\$1global\$1by\$1latency](https://dev.mysql.com/doc/refman/5.7/en/sys-waits-global-by-latency.html) 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](https://github.com/mysql/mysql-server/blob/mysql-5.7.44/scripts/sys_schema/views/p_s/waits_global_by_latency.sql) 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": []
}
```
**auroraUpgradeCheckForViewColumnNameLength**
**Precheck level: Error**
**Check upper limit for column name in view**
The [maximum permitted length of a column name](https://dev.mysql.com/doc/refman/5.7/en/identifier-length.html) 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": []
}
```
**auroraUpgradeCheckIndexLengthLimitOnTinyColumns**
**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](https://dev.mysql.com/doc/refman/5.7/en/binary-varbinary.html) in MySQL, you must add a [prefix](https://dev.mysql.com/doc/refman/5.7/en/column-indexes.html#column-indexes-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](https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html) 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": []
}
```
**auroraUpgradeCheckMissingInnodbMetadataForMysqlHostTable**
**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](https://aws.amazon.com/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.
## Warnings
The following prechecks generate warnings when the precheck fails, but the upgrade can proceed.
**Topics**
+ [
### MySQL prechecks that report warnings
](#precheck-descriptions-warnings.mysql)
+ [
### Aurora MySQL prechecks that report warnings
](#precheck-descriptions-warnings.aurora)
### MySQL prechecks that report warnings
The following prechecks are from Community MySQL:
+ [defaultAuthenticationPlugin](#defaultAuthenticationPlugin)
+ [maxdbFlagCheck](#maxdbFlagCheck)
+ [mysqlDollarSignNameCheck](#mysqlDollarSignNameCheck)
+ [reservedKeywordsCheck](#reservedKeywordsCheck)
+ [utf8mb3Check](#utf8mb3Check)
+ [zeroDatesCheck](#zeroDatesCheck)
**defaultAuthenticationPlugin**
**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\$1sha2\$1password compatibility issues and solutions](https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues) 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"
},
```
**maxdbFlagCheck**
**Precheck level: Warning**
**Usage of obsolete `MAXDB` `sql_mode` flag**
In MySQL 8.0, a number of deprecated [sql\$1mode](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode) system variable options were [removed](https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html), 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.
According to the [MySQL documentation](https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html), MySQL stores the `sql_mode` setting that's in effect when a routine is created or altered. It always runs the routine with this setting, regardless of the `sql_mode` setting when the routine starts.
Before changing `sql_mode`, see [Server SQL modes](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html) in the MySQL documentation. Carefully evaluate any potential impact of doing so on your application.
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": []
}
```
**mysqlDollarSignNameCheck**
**Precheck level: Warning**
**Check for deprecated usage of single dollar signs in object names**
As of [MySQL 8.0.32](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-32.html#mysqld-8-0-32-deprecation-removal), 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 ](https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html) 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.
**reservedKeywordsCheck**
**Precheck level: Warning**
**Usage of database objects with names conflicting with new reserved keywords**
This check is similar to the [routineSyntaxCheck](#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](https://dev.mysql.com/doc/refman/8.0/en/string-literals.html), 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';
```
**utf8mb3Check**
**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](https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html) and [utf8mb4](https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb4.html), see [Converting between 3-byte and 4-byte Unicode character sets](https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html) 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](https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html) in the MySQL documentation.
**zeroDatesCheck**
**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"
}
]
}
```
### Aurora MySQL prechecks that report warnings
The following prechecks are specific to Aurora MySQL:
+ [auroraUpgradeCheckForRollbackSegmentHistoryLength](#auroraUpgradeCheckForRollbackSegmentHistoryLength)
+ [auroraUpgradeCheckForUncommittedRowModifications](#auroraUpgradeCheckForUncommittedRowModifications)
**auroraUpgradeCheckForRollbackSegmentHistoryLength**
**Precheck level: Warning**
**Checks whether the rollback segment history list length for the cluster is high**
As mentioned in [auroraUpgradeCheckForIncompleteXATransactions](#auroraUpgradeCheckForIncompleteXATransactions), while running the major version upgrade process, it is essential that the Aurora MySQL version 2 DB instance undergo a [clean shutdown](https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_slow_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](proactive-insights.history-list.md).
**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](proactive-insights.history-list.md) on your DB cluster before running the upgrade in production, to make sure your HLL is kept at a manageable size.
**auroraUpgradeCheckForUncommittedRowModifications**
**Precheck level: Warning**
**Checks whether there are many uncommitted row modifications**
As mentioned in [auroraUpgradeCheckForIncompleteXATransactions](#auroraUpgradeCheckForIncompleteXATransactions), while running the major version upgrade process, it is essential that the Aurora MySQL version 2 DB instance undergo a [clean shutdown](https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_slow_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\$1schema.innodb\$1trx](https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-trx-table.html) 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](https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-transaction-management.html) in the MySQL documentation.
## Notices
The following precheck generates a notice when the precheck fails, but the upgrade can proceed.
**sqlModeFlagCheck**
**Precheck level: Notice**
**Usage of obsolete `sql_mode` flags**
In addition to `MAXDB`, a number of other `sql_mode` options have been [removed](https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html): `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](https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html).
**Example output:**
```
{
"id": "sqlModeFlagCheck",
"title": "Usage of obsolete sql_mode flags",
"status": "OK",
"detectedProblems": []
}
```
To resolve any of these precheck failures, see [maxdbFlagCheck](#maxdbFlagCheck).
## Errors, warnings, or notices
The following precheck can return an error, warning, or notice depending on the precheck output.
**checkTableOutput**
**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](#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:
+ Verifying that the table structure and metadata are compatible with the target MySQL version
+ Checking for any deprecated or removed features used by the table
+ Ensuring that the table can be properly upgraded without data loss
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](https://dev.mysql.com/doc/refman/5.7/en/check-table.html) 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](https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html) 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\$1key\$1checks](https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html#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](https://dev.mysql.com/doc/refman/5.7/en/check-table.html#check-table-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": []
},
```
# How to perform an in-place upgrade
We recommend that you review the background material in [How the Aurora MySQL in-place major version upgrade works](AuroraMySQL.Updates.MajorVersionUpgrade.md#AuroraMySQL.Upgrading.Sequence).
Perform any preupgrade planning and testing, as described in [Planning a major version upgrade for an Aurora MySQL cluster](AuroraMySQL.Updates.MajorVersionUpgrade.md#AuroraMySQL.Upgrading.Planning).
## Console
The following example upgrades the `mydbcluster-cluster` DB cluster to Aurora MySQL version 3.04.1.
**To upgrade the major version of an Aurora MySQL DB cluster**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. If you used a custom parameter group for the original DB cluster, create a corresponding parameter group compatible with the new major version. Make any necessary adjustments to the configuration parameters in that new parameter group. For more information, see [How in-place upgrades affect the parameter groups for a cluster](#AuroraMySQL.Upgrading.ParamGroups).
1. In the navigation pane, choose **Databases**.
1. In the list, choose the DB cluster that you want to modify.
1. Choose **Modify**.
1. For **Version**, choose a new Aurora MySQL major version.
We generally recommend using the latest minor version of the major version. Here, we choose the current default version.
![\[In-place upgrade of an Aurora MySQL DB cluster from version 2 to version 3\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/ams-upgrade-v2-v3.png)
1. Choose **Continue**.
1. On the next page, specify when to perform the upgrade. Choose **During the next scheduled maintenance window** or **Immediately**.
1. (Optional) Periodically examine the **Events** page in the RDS console during the upgrade. Doing so helps you to monitor the progress of the upgrade and identify any issues. If the upgrade encounters any issues, consult [Troubleshooting for Aurora MySQL in-place upgrade](AuroraMySQL.Upgrading.Troubleshooting.md) for the steps to take.
1. If you created a new parameter group at the start of this procedure, associate the custom parameter group with your upgraded cluster. For more information, see [How in-place upgrades affect the parameter groups for a cluster](#AuroraMySQL.Upgrading.ParamGroups).
**Note**
Performing this step requires you to restart the cluster again to apply the new parameter group.
1. (Optional) After you complete any post-upgrade testing, delete the manual snapshot that Aurora created at the beginning of the upgrade.
## AWS CLI
To upgrade the major version of an Aurora MySQL DB cluster, use the AWS CLI [modify-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-cluster.html) command with the following required parameters:
+ `--db-cluster-identifier`
+ `--engine-version`
+ `--allow-major-version-upgrade`
+ `--apply-immediately` or `--no-apply-immediately`
If your cluster uses any custom parameter groups, also include one or both of the following options:
+ `--db-cluster-parameter-group-name`, if the cluster uses a custom cluster parameter group
+ `--db-instance-parameter-group-name`, if any instances in the cluster use a custom DB parameter group
The following example upgrades the `sample-cluster` DB cluster to Aurora MySQL version 3.04.1. The upgrade happens immediately, instead of waiting for the next maintenance window.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-db-cluster \
--db-cluster-identifier sample-cluster \
--engine-version 8.0.mysql_aurora.3.04.1 \
--allow-major-version-upgrade \
--apply-immediately
```
For Windows:
```
aws rds modify-db-cluster ^
--db-cluster-identifier sample-cluster ^
--engine-version 8.0.mysql_aurora.3.04.1 ^
--allow-major-version-upgrade ^
--apply-immediately
```
You can combine other CLI commands with `modify-db-cluster` to create an automated end-to-end process for performing and verifying upgrades. For more information and examples, see [Aurora MySQL in-place upgrade tutorial](AuroraMySQL.Upgrading.Tutorial.md).
**Note**
If your cluster is part of an Aurora global database, the in-place upgrade procedure is slightly different. You call the [modify-global-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-global-cluster.html) command operation instead of `modify-db-cluster`. For more information, see [In-place major upgrades for global databases](#AuroraMySQL.Upgrading.GlobalDB).
## RDS API
To upgrade the major version of an Aurora MySQL DB cluster, use the RDS API operation [ModifyDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBCluster.html) with the following required parameters:
+ `DBClusterIdentifier`
+ `Engine`
+ `EngineVersion`
+ `AllowMajorVersionUpgrade`
+ `ApplyImmediately` (set to `true` or `false`)
**Note**
If your cluster is part of an Aurora global database, the in-place upgrade procedure is slightly different. You call the [ModifyGlobalCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyGlobalClusterParameterGroup.html) operation instead of `ModifyDBCluster`. For more information, see [In-place major upgrades for global databases](#AuroraMySQL.Upgrading.GlobalDB).
## How in-place upgrades affect the parameter groups for a cluster
Aurora parameter groups have different sets of configuration settings for clusters that are compatible with MySQL 5.7 or 8.0. When you perform an in-place upgrade, the upgraded cluster and all its instances must use the corresponding cluster and instance parameter groups:
Your cluster and instances might use the default 5.7-compatible parameter groups. If so, the upgraded cluster and instance start with the default 8.0-compatible parameter groups. If your cluster and instances use any custom parameter groups, make sure to create corresponding or 8.0-compatible parameter groups. Also make sure to specify those during the upgrade process.
**Note**
For most parameter settings, you can choose the custom parameter group at two points. These are when you create the cluster or associate the parameter group with the cluster later.
However, if you use a nondefault setting for the `lower_case_table_names` parameter, you must set up the custom parameter group with this setting in advance. Then specify the parameter group when you perform the snapshot restore to create the cluster. Any change to the `lower_case_table_names` parameter has no effect after the cluster is created.
We recommend that you use the same setting for `lower_case_table_names` when you upgrade from Aurora MySQL version 2 to version 3.
With an Aurora global database based on Aurora MySQL, you can perform an in-place upgrade from Aurora MySQL version 2 to version 3 only if you set the `lower_case_table_names` parameter to default and reboot your global database. For more information on the methods that you can use, see [Major version upgrades](aurora-global-database-upgrade.md#aurora-global-database-upgrade.major).
## Changes to cluster properties between Aurora MySQL versions
When you upgrade from Aurora MySQL version 2 to version 3, make sure to check any applications or scripts that you use to set up or manage Aurora MySQL clusters and DB instances.
Also, change your code that manipulates parameter groups to account for the fact that the default parameter group names are different for 5.7- and 8.0-compatible clusters. The default parameter group names for Aurora MySQL version 2 and 3 clusters are `default.aurora-mysql5.7` and `default.aurora-mysql8.0`, respectively.
For example, you might have code like the following that applies to your cluster before an upgrade.
```
# Check the default parameter values for MySQL 5.7–compatible clusters.
aws rds describe-db-parameters --db-parameter-group-name default.aurora-mysql5.7 --region us-east-1
```
After upgrading the major version of the cluster, modify that code as follows.
```
# Check the default parameter values for MySQL 8.0–compatible clusters.
aws rds describe-db-parameters --db-parameter-group-name default.aurora-mysql8.0 --region us-east-1
```
## In-place major upgrades for global databases
For an Aurora global database, you upgrade the global database cluster. Aurora automatically upgrades all of the clusters at the same time and makes sure that they all run the same engine version. This requirement is because any changes to system tables, data file formats, and so on, are automatically replicated to all the secondary clusters.
Follow the instructions in [How the Aurora MySQL in-place major version upgrade works](AuroraMySQL.Updates.MajorVersionUpgrade.md#AuroraMySQL.Upgrading.Sequence). When you specify what to upgrade, make sure to choose the global database cluster instead of one of the clusters it contains.
If you use the AWS Management Console, choose the item with the role **Global database**.
![\[Upgrading global database cluster\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/aurora-global-databases-major-upgrade-global-cluster.png)
If you use the AWS CLI or RDS API, start the upgrade process by calling the [modify-global-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-global-cluster.html) command or [ModifyGlobalCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyGlobalCluster.html) operation. You use one of these instead of `modify-db-cluster` or `ModifyDBCluster`.
**Note**
You can't specify a custom parameter group for the global database cluster while you're performing a major version upgrade of that Aurora global database. Create your custom parameter groups in each Region of the global cluster. Then apply them manually to the Regional clusters after the upgrade.
To upgrade the major version of an Aurora MySQL global database cluster by using the AWS CLI, use the [modify-global-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-global-cluster.html) command with the following required parameters:
+ `--global-cluster-identifier`
+ `--engine aurora-mysql`
+ `--engine-version`
+ `--allow-major-version-upgrade`
The following example upgrades the global database cluster to Aurora MySQL version 3.04.2.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-global-cluster \
--global-cluster-identifier global_cluster_identifier \
--engine aurora-mysql \
--engine-version 8.0.mysql_aurora.3.04.2 \
--allow-major-version-upgrade
```
For Windows:
```
aws rds modify-global-cluster ^
--global-cluster-identifier global_cluster_identifier ^
--engine aurora-mysql ^
--engine-version 8.0.mysql_aurora.3.04.2 ^
--allow-major-version-upgrade
```
## In-place upgrades for DB clusters with cross-Region read replicas
You can upgrade an Aurora DB cluster that has a cross-Region read replica using the in-place upgrade procedure, but there are certain considerations:
+ You must upgrade the read replica DB cluster first. If you try to upgrade the primary cluster first, you will receive an error message such as the following:
Unable to upgrade DB cluster test-xr-primary-cluster because the associated Aurora cross-Region replica test-xr-replica-cluster isn't patched yet. Upgrade the Aurora cross-Region replica and try again.
This means that the primary DB cluster can't have a higher DB engine version than the replica cluster.
+ Before you upgrade the primary DB cluster, stop the write workload and disable any new connection requests to the writer DB instance of the primary cluster.
+ When you upgrade the primary cluster, choose a custom DB cluster parameter group with the `binlog_format` parameter set to a value that supports binary logging replication, such as `MIXED`.
For more information about using binary logging with Aurora MySQL, see [Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)](AuroraMySQL.Replication.MySQL.md). For more information about modifying Aurora MySQL configuration parameters, see [Aurora MySQL configuration parameters](AuroraMySQL.Reference.ParameterGroups.md) and [Parameter groups for Amazon Aurora](USER_WorkingWithParamGroups.md).
+ Don't wait a long time to upgrade the primary DB cluster after you upgrade the replica cluster. We recommend not waiting longer than the next maintenance window.
+ After you upgrade the primary DB cluster, reboot its writer DB instance. The custom DB cluster parameter group that enables binlog replication doesn't take effect until the writer DB instance is rebooted.
+ Don't resume the write workload or enable connections to the writer DB instance until you confirm that cross-Region replication has restarted, and that the replica lag in the secondary AWS Region is 0.
# Aurora MySQL in-place upgrade tutorial
The following Linux examples show how you might perform the general steps of the in-place upgrade procedure using the AWS CLI.
This first example creates an Aurora DB cluster that's running a 2.x version of Aurora MySQL. The cluster includes a writer DB instance and a reader DB instance. The `wait db-instance-available` command pauses until the writer DB instance is available. That's the point when the cluster is ready to be upgraded.
```
aws rds create-db-cluster --db-cluster-identifier mynewdbcluster --engine aurora-mysql \
--db-cluster-version 5.7.mysql_aurora.2.11.2
...
aws rds create-db-instance --db-instance-identifier mynewdbcluster-instance1 \
--db-cluster-identifier mynewdbcluster --db-instance-class db.t4g.medium --engine aurora-mysql
...
aws rds wait db-instance-available --db-instance-identifier mynewdbcluster-instance1
```
The Aurora MySQL 3.x versions that you can upgrade the cluster to depend on the 2.x version that the cluster is currently running and on the AWS Region where the cluster is located. The first command, with `--output text`, just shows the available target version. The second command shows the full JSON output of the response. In that response, you can see details such as the `aurora-mysql` value that you use for the `engine` parameter. You can also see the fact that upgrading to 3.04.0 represents a major version upgrade.
```
aws rds describe-db-clusters --db-cluster-identifier mynewdbcluster \
--query '*[].{EngineVersion:EngineVersion}' --output text
5.7.mysql_aurora.2.11.2
aws rds describe-db-engine-versions --engine aurora-mysql --engine-version 5.7.mysql_aurora.2.11.2 \
--query '*[].[ValidUpgradeTarget]'
...
{
"Engine": "aurora-mysql",
"EngineVersion": "8.0.mysql_aurora.3.04.0",
"Description": "Aurora MySQL 3.04.0 (compatible with MySQL 8.0.28)",
"AutoUpgrade": false,
"IsMajorVersionUpgrade": true,
"SupportedEngineModes": [
"provisioned"
],
"SupportsParallelQuery": true,
"SupportsGlobalDatabases": true,
"SupportsBabelfish": false,
"SupportsIntegrations": false
},
...
```
This example shows how if you enter a target version number that isn't a valid upgrade target for the cluster, Aurora doesn't perform the upgrade. Aurora also doesn't perform a major version upgrade unless you include the `--allow-major-version-upgrade` parameter. That way, you can't accidentally perform an upgrade that has the potential to require extensive testing and changes to your application code.
```
aws rds modify-db-cluster --db-cluster-identifier mynewdbcluster \
--engine-version 5.7.mysql_aurora.2.09.2 --apply-immediately
An error occurred (InvalidParameterCombination) when calling the ModifyDBCluster operation: Cannot find upgrade target from 5.7.mysql_aurora.2.11.2 with requested version 5.7.mysql_aurora.2.09.2.
aws rds modify-db-cluster --db-cluster-identifier mynewdbcluster \
--engine-version 8.0.mysql_aurora.3.04.0 --region us-east-1 --apply-immediately
An error occurred (InvalidParameterCombination) when calling the ModifyDBCluster operation: The AllowMajorVersionUpgrade flag must be present when upgrading to a new major version.
aws rds modify-db-cluster --db-cluster-identifier mynewdbcluster \
--engine-version 8.0.mysql_aurora.3.04.0 --apply-immediately --allow-major-version-upgrade
{
"DBClusterIdentifier": "mynewdbcluster",
"Status": "available",
"Engine": "aurora-mysql",
"EngineVersion": "5.7.mysql_aurora.2.11.2"
}
```
It takes a few moments for the status of the cluster and associated DB instances to change to `upgrading`. The version numbers for the cluster and DB instances only change when the upgrade is finished. Again, you can use the `wait db-instance-available` command for the writer DB instance to wait until the upgrade is finished before proceeding.
```
aws rds describe-db-clusters --db-cluster-identifier mynewdbcluster \
--query '*[].[Status,EngineVersion]' --output text
upgrading 5.7.mysql_aurora.2.11.2
aws rds describe-db-instances --db-instance-identifier mynewdbcluster-instance1 \
--query '*[].{DBInstanceIdentifier:DBInstanceIdentifier,DBInstanceStatus:DBInstanceStatus} | [0]'
{
"DBInstanceIdentifier": "mynewdbcluster-instance1",
"DBInstanceStatus": "upgrading"
}
aws rds wait db-instance-available --db-instance-identifier mynewdbcluster-instance1
```
At this point, the version number for the cluster matches the one that was specified for the upgrade.
```
aws rds describe-db-clusters --db-cluster-identifier mynewdbcluster \
--query '*[].[EngineVersion]' --output text
8.0.mysql_aurora.3.04.0
```
The preceding example did an immediate upgrade by specifying the `--apply-immediately` parameter. To let the upgrade happen at a convenient time when the cluster isn't expected to be busy, you can specify the `--no-apply-immediately` parameter. Doing so makes the upgrade start during the next maintenance window for the cluster. The maintenance window defines the period during which maintenance operations can start. A long-running operation might not finish during the maintenance window. Thus, you don't need to define a larger maintenance window even if you expect that the upgrade might take a long time.
The following example upgrades a cluster that's initially running Aurora MySQL version 2.11.2. In the `describe-db-engine-versions` output, the `False` and `True` values represent the `IsMajorVersionUpgrade` property. From version 2.11.2, you can upgrade to some other 2.\$1 versions. Those upgrades aren't considered major version upgrades and so don't require an in-place upgrade. In-place upgrade is only available for upgrades to the 3.\$1 versions that are shown in the list.
```
aws rds describe-db-clusters --db-cluster-identifier mynewdbcluster \
--query '*[].{EngineVersion:EngineVersion}' --output text
5.7.mysql_aurora.2.11.2
aws rds describe-db-engine-versions --engine aurora-mysql --engine-version 5.7.mysql_aurora.2.10.2 \
--query '*[].[ValidUpgradeTarget]|[0][0]|[*].[EngineVersion,IsMajorVersionUpgrade]' --output text
5.7.mysql_aurora.2.11.3 False
5.7.mysql_aurora.2.11.4 False
5.7.mysql_aurora.2.11.5 False
5.7.mysql_aurora.2.11.6 False
5.7.mysql_aurora.2.12.0 False
5.7.mysql_aurora.2.12.1 False
5.7.mysql_aurora.2.12.2 False
5.7.mysql_aurora.2.12.3 False
8.0.mysql_aurora.3.04.0 True
8.0.mysql_aurora.3.04.1 True
8.0.mysql_aurora.3.04.2 True
8.0.mysql_aurora.3.04.3 True
8.0.mysql_aurora.3.05.2 True
8.0.mysql_aurora.3.06.0 True
8.0.mysql_aurora.3.06.1 True
8.0.mysql_aurora.3.07.1 True
aws rds modify-db-cluster --db-cluster-identifier mynewdbcluster \
--engine-version 8.0.mysql_aurora.3.04.0 --no-apply-immediately --allow-major-version-upgrade
...
```
When a cluster is created without a specified maintenance window, Aurora picks a random day of the week. In this case, the `modify-db-cluster` command is submitted on a Monday. Thus, we change the maintenance window to be Tuesday morning. All times are represented in the UTC time zone. The `tue:10:00-tue:10:30` window corresponds to 2:00-2:30 AM Pacific time. The change in the maintenance window takes effect immediately.
```
aws rds describe-db-clusters --db-cluster-identifier mynewdbcluster --query '*[].[PreferredMaintenanceWindow]'
[
[
"sat:08:20-sat:08:50"
]
]
aws rds modify-db-cluster --db-cluster-identifier mynewdbcluster --preferred-maintenance-window tue:10:00-tue:10:30"
aws rds describe-db-clusters --db-cluster-identifier mynewdbcluster --query '*[].[PreferredMaintenanceWindow]'
[
[
"tue:10:00-tue:10:30"
]
]
```
The following example shows how to get a report of the events generated by the upgrade. The `--duration` argument represents the number of minutes to retrieve the event information. This argument is needed, because by default `describe-events` only returns events from the last hour.
```
aws rds describe-events --source-type db-cluster --source-identifier mynewdbcluster --duration 20160
{
"Events": [
{
"SourceIdentifier": "mynewdbcluster",
"SourceType": "db-cluster",
"Message": "DB cluster created",
"EventCategories": [
"creation"
],
"Date": "2022-11-17T01:24:11.093000+00:00",
"SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:mynewdbcluster"
},
{
"SourceIdentifier": "mynewdbcluster",
"SourceType": "db-cluster",
"Message": "Upgrade in progress: Performing online pre-upgrade checks.",
"EventCategories": [
"maintenance"
],
"Date": "2022-11-18T22:57:08.450000+00:00",
"SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:mynewdbcluster"
},
{
"SourceIdentifier": "mynewdbcluster",
"SourceType": "db-cluster",
"Message": "Upgrade in progress: Performing offline pre-upgrade checks.",
"EventCategories": [
"maintenance"
],
"Date": "2022-11-18T22:57:59.519000+00:00",
"SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:mynewdbcluster"
},
{
"SourceIdentifier": "mynewdbcluster",
"SourceType": "db-cluster",
"Message": "Upgrade in progress: Creating pre-upgrade snapshot [preupgrade-mynewdbcluster-5-7-mysql-aurora-2-10-2-to-8-0-mysql-aurora-3-02-0-2022-11-18-22-55].",
"EventCategories": [
"maintenance"
],
"Date": "2022-11-18T23:00:22.318000+00:00",
"SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:mynewdbcluster"
},
{
"SourceIdentifier": "mynewdbcluster",
"SourceType": "db-cluster",
"Message": "Upgrade in progress: Cloning volume.",
"EventCategories": [
"maintenance"
],
"Date": "2022-11-18T23:01:45.428000+00:00",
"SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:mynewdbcluster"
},
{
"SourceIdentifier": "mynewdbcluster",
"SourceType": "db-cluster",
"Message": "Upgrade in progress: Purging undo records for old row versions. Records remaining: 164",
"EventCategories": [
"maintenance"
],
"Date": "2022-11-18T23:02:25.141000+00:00",
"SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:mynewdbcluster"
},
{
"SourceIdentifier": "mynewdbcluster",
"SourceType": "db-cluster",
"Message": "Upgrade in progress: Purging undo records for old row versions. Records remaining: 164",
"EventCategories": [
"maintenance"
],
"Date": "2022-11-18T23:06:23.036000+00:00",
"SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:mynewdbcluster"
},
{
"SourceIdentifier": "mynewdbcluster",
"SourceType": "db-cluster",
"Message": "Upgrade in progress: Upgrading database objects.",
"EventCategories": [
"maintenance"
],
"Date": "2022-11-18T23:06:48.208000+00:00",
"SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:mynewdbcluster"
},
{
"SourceIdentifier": "mynewdbcluster",
"SourceType": "db-cluster",
"Message": "Database cluster major version has been upgraded",
"EventCategories": [
"maintenance"
],
"Date": "2022-11-18T23:10:28.999000+00:00",
"SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:mynewdbcluster"
}
]
}
```
# Finding the reasons for Aurora MySQL major version upgrade failures
In the [tutorial](AuroraMySQL.Upgrading.Tutorial.md), the upgrade from Aurora MySQL version 2 to version 3 succeeded. But if the upgrade had failed, you would want to know why.
You can start by using the `describe-events` AWS CLI command to look at the DB cluster events. This example shows the events for `mydbcluster` for the last 10 hours.
```
aws rds describe-events \
--source-type db-cluster \
--source-identifier mydbcluster \
--duration 600
```
In this case, we had an upgrade precheck failure.
```
{
"Events": [
{
"SourceIdentifier": "mydbcluster",
"SourceType": "db-cluster",
"Message": "Database cluster engine version upgrade started.",
"EventCategories": [
"maintenance"
],
"Date": "2024-04-11T13:23:22.846000+00:00",
"SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:mydbcluster"
},
{
"SourceIdentifier": "mydbcluster",
"SourceType": "db-cluster",
"Message": "Database cluster is in a state that cannot be upgraded: Upgrade prechecks failed. For more details, see the
upgrade-prechecks.log file. For more information on troubleshooting the cause of the upgrade failure, see
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Upgrading.Troubleshooting.html",
"EventCategories": [
"maintenance"
],
"Date": "2024-04-11T13:23:24.373000+00:00",
"SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:mydbcluster"
}
]
}
```
To diagnose the exact cause of the problem, examine the database logs for the writer DB instance. When an upgrade to Aurora MySQL version 3 fails, the writer instance contains a log file with the name `upgrade-prechecks.log`. This example shows how to detect the presence of that log and then download it to a local file for examination.
```
aws rds describe-db-log-files --db-instance-identifier mydbcluster-instance \
--query '*[].[LogFileName]' --output text
error/mysql-error-running.log
error/mysql-error-running.log.2024-04-11.20
error/mysql-error-running.log.2024-04-11.21
error/mysql-error.log
external/mysql-external.log
upgrade-prechecks.log
aws rds download-db-log-file-portion --db-instance-identifier mydbcluster-instance \
--log-file-name upgrade-prechecks.log \
--starting-token 0 \
--output text >upgrade_prechecks.log
```
The `upgrade-prechecks.log` file is in JSON format. We download it using the `--output text` option to avoid encoding JSON output within another JSON wrapper. For Aurora MySQL version 3 upgrades, this log always includes certain informational and warning messages. It only includes error messages if the upgrade fails. If the upgrade succeeds, the log file isn't produced at all.
To summarize all of the errors and display the associated object and description fields, you can run the command `grep -A 2 '"level": "Error"'` on the contents of the `upgrade-prechecks.log` file. Doing so displays each error line and the two lines after it. These contain the name of the corresponding database object and guidance about how to correct the problem.
```
$ cat upgrade-prechecks.log | grep -A 2 '"level": "Error"'
"level": "Error",
"dbObject": "problematic_upgrade.dangling_fulltext_index",
"description": "Table `problematic_upgrade.dangling_fulltext_index` contains dangling FULLTEXT index. Kindly recreate the table before upgrade."
```
In this example, you can run the following SQL command on the offending table to try to fix the issue, or you can re-create the table without the dangling index.
```
OPTIMIZE TABLE problematic_upgrade.dangling_fulltext_index;
```
Then retry the upgrade.
# Troubleshooting for Aurora MySQL in-place upgrade
Use the following tips to help troubleshoot problems with Aurora MySQL in-place upgrades. These tips don't apply to Aurora Serverless DB clusters.
| Reason for in-place upgrade being canceled or slow | Effect | Solution to allow in-place upgrade to complete within maintenance window |
| --- | --- | --- |
| Associated Aurora cross-Region replica isn't patched yet | Aurora cancels the upgrade. | Upgrade the Aurora cross-Region replica and try again. |
| Cluster has XA transactions in the prepared state | Aurora cancels the upgrade. | Commit or roll back all prepared XA transactions. |
| Cluster is processing any data definition language (DDL) statements | Aurora cancels the upgrade. | Consider waiting and performing the upgrade after all DDL statements are finished. |
| Cluster has uncommitted changes for many rows | Upgrade might take a long time. | The upgrade process rolls back the uncommitted changes. The indicator for this condition is the value of `TRX_ROWS_MODIFIED` in the `INFORMATION_SCHEMA.INNODB_TRX` table. Consider performing the upgrade only after all large transactions are committed or rolled back. |
| Cluster has high number of undo records | Upgrade might take a long time. | Even if the uncommitted transactions don't affect a large number of rows, they might involve a large volume of data. For example, you might be inserting large BLOBs. Aurora doesn't automatically detect or generate an event for this kind of transaction activity. The indicator for this condition is the history list length (HLL). The upgrade process rolls back the uncommitted changes. You can check the HLL in the output from the `SHOW ENGINE INNODB STATUS` SQL command, or directly by using the following SQL query: SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';
You can also monitor the `RollbackSegmentHistoryListLength` metric in Amazon CloudWatch. Consider performing the upgrade only after the HLL is smaller. |
| Cluster is in the process of committing a large binary log transaction | Upgrade might take a long time. | The upgrade process waits until the binary log changes are applied. More transactions or DDL statements could start during this period, further slowing down the upgrade process. Schedule the upgrade process when the cluster isn't busy with generating binary log replication changes. Aurora doesn't automatically detect or generate an event for this condition. |
| Schema inconsistencies resulting from file removal or corruption | Aurora cancels the upgrade. | Change the default storage engine for temporary tables from MyISAM to InnoDB. Perform the following steps: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Upgrading.Troubleshooting.html) |
| Master user was deleted | Aurora cancels the upgrade. | Don't delete the master user. However, if for some reason you should happen to delete the master user, restore it using the following SQL commands: CREATE USER 'master_username'@'%' IDENTIFIED BY 'master_user_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT,
TRIGGER, LOAD FROM S3, SELECT INTO S3, INVOKE LAMBDA, INVOKE SAGEMAKER, INVOKE COMPREHEND ON *.* TO 'master_username'@'%' WITH GRANT OPTION;
|
For more details on troubleshooting issues that cause upgrade prechecks to fail, see the following blogs:
+ [Amazon Aurora MySQL version 2 (with MySQL 5.7 compatibility) to version 3 (with MySQL 8.0 compatibility) upgrade checklist, Part 1](https://aws.amazon.com/blogs/database/amazon-aurora-mysql-version-2-with-mysql-5-7-compatibility-to-version-3-with-mysql-8-0-compatibility-upgrade-checklist-part-1/)
+ [Amazon Aurora MySQL version 2 (with MySQL 5.7 compatibility) to version 3 (with MySQL 8.0 compatibility) upgrade checklist, Part 2](https://aws.amazon.com/blogs/database/amazon-aurora-mysql-version-2-with-mysql-5-7-compatibility-to-version-3-with-mysql-8-0-compatibility-upgrade-checklist-part-2/)
You can use the following steps to perform your own checks for some of the conditions in the preceding table. That way, you can schedule the upgrade at a time when you know the database is in a state where the upgrade can complete successfully and quickly.
+ You can check for open XA transactions by executing the `XA RECOVER` statement. You can then commit or roll back the XA transactions before starting the upgrade.
+ You can check for DDL statements by executing a `SHOW PROCESSLIST` statement and looking for `CREATE`, `DROP`, `ALTER`, `RENAME`, and `TRUNCATE` statements in the output. Allow all DDL statements to finish before starting the upgrade.
+ You can check the total number of uncommitted rows by querying the `INFORMATION_SCHEMA.INNODB_TRX` table. The table contains one row for each transaction. The `TRX_ROWS_MODIFIED` column contains the number of rows modified or inserted by the transaction.
+ You can check the length of the InnoDB history list by executing the `SHOW ENGINE INNODB STATUS SQL` statement and looking for the `History list length` in the output. You can also check the value directly by running the following query:
```
SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';
```
The length of the history list corresponds to the amount of undo information stored by the database to implement multi-version concurrency control (MVCC).
# Post-upgrade cleanup for Aurora MySQL version 3
After you finish upgrading any Aurora MySQL version 2 clusters to Aurora MySQL version 3, you can perform these other cleanup actions:
+ Create new MySQL 8.0–compatible versions of any custom parameter groups. Apply any necessary custom parameter values to the new parameter groups.
+ Update any CloudWatch alarms, setup scripts, and so on to use the new names for any metrics whose names were affected by inclusive language changes. For a list of such metrics, see [Inclusive language changes for Aurora MySQL version 3](AuroraMySQL.Compare-v2-v3.md#AuroraMySQL.8.0-inclusive-language).
+ Update any CloudFormation templates to use the new names for any configuration parameters whose names were affected by inclusive language changes. For a list of such parameters, see [Inclusive language changes for Aurora MySQL version 3](AuroraMySQL.Compare-v2-v3.md#AuroraMySQL.8.0-inclusive-language).
## Spatial indexes
After upgrading to Aurora MySQL version 3, check if you need to drop or recreate objects and indexes related to spatial indexes. Before MySQL 8.0, Aurora could optimize spatial queries using indexes that didn't contain a spatial resource identifier (SRID). Aurora MySQL version 3 only uses spatial indexes containing SRIDs. During an upgrade, Aurora automatically drops any spatial indexes without SRIDs and prints warning messages in the database log. If you observe such warning messages, create new spatial indexes with SRIDs after the upgrade. For more information about changes to spatial functions and data types in MySQL 8.0, see [Changes in MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html) in the *MySQL Reference Manual*.
# Database engine updates and fixes for Amazon Aurora MySQL
You can find the following information in the *Release notes for Amazon Aurora MySQL-Compatible Edition*:
+ [Database engine updates for Amazon Aurora MySQL version 3](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.Updates.30Updates.html)
+ [Database engine updates for Amazon Aurora MySQL version 2](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.Updates.20Updates.html)
+ [Database engine updates for Amazon Aurora MySQL version 1 (Deprecated)](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.Updates.11Updates.html)
+ [MySQL bugs fixed by Aurora MySQL database engine updates](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.Updates.MySQLBugs.html)
+ [Security vulnerabilities fixed in Amazon Aurora MySQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.CVE_list.html)