Mysql snapshot upgrade 5.7.44 to 8.0.35

Question

I'm trying to upgrade the mysql instance from 5.7.44 to 8 and up. I have a smaller scale "test" db with the same configuration as my production db. Last week I tried and managed to get the test side upgraded with the following steps -
1. Made a copy of the latest "System" snapshot (5.7.44)
2. Applied the action "Upgrade snapshot" to the new copy, selecting 8.0.35
3. That was successful, then used the "Restore snapshot" to create an 8.0.35 instance
4. ...worked fine

Today I started preparing for the prod upgrade, thought I'd just run the snapshot copy and upgrade process as a verification before a maintenance break. However, the "Upgrade snapshot" keeps consistently failing without a descriptive message, just "Failed to upgrade DB snapshot: (db name)". I did this with the console interface, but tried to run from the cli also, using `aws rds modify-db-snapshot` - only to find out that of course it is async and does not really provide other information other than that the state changes to upgrading.

As I had not deleted the 5.7 test database, I tried to replicate the process with another snapshot from the test db, and that failed too in the same way, although no changes had been applied to it during the week.

Is there a way to find out what is going wrong with the **snapshot** upgrade process? The "recent events" only show the snapshot originally being created and nothing about the failing upgrade.

Answer

Hi again, I tried the CLI snapshot one more time and verified with the describe-db-snapshots that before the upgrade the copy had the status "Available". Tried to apply the upgrade once again, it took the status of "upgrading", followed after a few minutes into "Failed".

Ok - I went with another route and restored a copy snapshot of the test db instance (the initial, still existing 5.7.44 one). For this I enabled the error logs into cloudwatch. Then I went to "Modify" the new db instance, and changed the engine version from 5.7.44 to 8.0.35, kept other parameters as they were and pressed continue to start. This did not ultimately go through, but I was able now to look at the error at cloudwatch - it had failed the PrePatchCompatibility, and the specific failure was with
`mysql.flush_rewrite_rules - at line 5,8: unexpected token 'QUERY'`
Looks like an issue someone else has had too, and gotten resolved with a bit of help, referencing this post https://repost.aws/questions/QU_jfgd2gURk6TzL8JcQCO9Q/error-upgrading-mysql-5-7-to-8-0

"the mysql.flush_rewrite_rules procedure had to be dropped by the internal AWS RDS development team" - what would be the way to have this applied for my db too, if that is the preventing issue?

Mysql snapshot upgrade 5.7.44 to 8.0.35

Relevant content