Aurora DB Upgrade Stuck During Migration from 5.7.mysql_aurora.2.11.5 to Latest Version

The situation you're experiencing with your Aurora MySQL upgrade being stuck is not uncommon, and there are several potential reasons for this delay. Based on the information provided, it appears that the upgrade process is still in the precheck phase, which can take a considerable amount of time depending on various factors.

Here are some possible reasons for the prolonged upgrade process:

1. Long-running or idle transactions: If there are read-only or read-write long-running transactions, or uncommitted idle transactions, they could be holding table locks. This can block the upgrade prechecks from completing, causing the upgrade to appear stuck.

2. Large number of tables: If your cluster has a substantial number of tables, it can significantly extend the duration of the precheck and engine version upgrade steps. This is due to the centralized storage of the MySQL dictionary data in MySQL 8.0.

3. High number of undo records: A large number of undo records can result in a long upgrade time due to undo purge during the clean shutdown.

4. Large ongoing write transactions: If there are large ongoing write transactions with uncommitted changes for many rows, it can cause prolonged upgrades due to transaction rollback during the clean shutdown.

5. XA transactions in prepared state: Any XA transactions in the prepared state must be committed or rolled back before the upgrade can proceed.

To address this issue, you can try the following steps:

1. Check for blocking transactions: Run the query select * from information_schema.processlist where USER='rdsadmin' to see if there are any commands waiting in states like "Waiting for table flush" or "Waiting for table metadata lock".

2. Identify and resolve blocking transactions: Use appropriate queries to identify any blocking transactions and commit, roll back, or kill them if possible.

3. Review and clean up unused tables: Consider reviewing and eliminating any unused tables to reduce the upgrade time.

4. Check undo records: Monitor the RollbackSegmentHistoryListLength (HLL) metric or run SHOW ENGINES INNODB STATUS to determine the number of undo records.

5. Commit or roll back large transactions: Ensure all large transactions are committed or rolled back before attempting the upgrade again.

If the upgrade continues to be stuck, you may need to contact AWS Support for further assistance. They can help you identify any specific issues with your cluster that might be causing the delay.

Remember that upgrades are best performed during low traffic periods, and it's always a good practice to have a backup of your database before initiating any major version upgrade.
Sources
Amazon Aurora MySQL version 2 (with MySQL 5.7 compatibility) to version 3 (with MySQL 8.0 compatibility) upgrade checklist, Part 2 | AWS Database Blog
Troubleshooting for Aurora MySQL in-place upgrade - Amazon Aurora
Upgrading the minor version or patch level of an Aurora MySQL DB cluster - Amazon Aurora