Aurora MySQL Upgrade From 2.10.1 to 3.01.0 (MySQL 5.7 to 8) Failing

Question

I restored a snapshot of an Aurora MySQL 5.7 cluster with an upgrade to MySQL 8 and it did not perform the upgrade.  The cluster was restored as MYSQL 5.7.  The upgrade-prechecks.log contains the following errors for 6 of my stored procedures:

The following routines contain keywords like 'QUERY CACHE', 'GROUP BY' with 'ASC' or 'DESC', 'SQL_CACHE', 'SQL_NO_CACHE' which are not supported in major version 8.0. It is recommended to drop these routines or rebuild routines without any of the above keywords before upgrade.",

The 6 stored procedures do not use any keywords and run fine on a local instance of MYSQL 8. They all use a 'GROUP BY' but do not use a 'GROUP BY' with 'ASC' or 'DESC'.

4 of the stored procedures are grouping by columns that contain Desc e.g. GROUP BY VehicleModelDescription.   The other 2 do not but do contain columns with Desc in the name in other parts of the proc.

I suspect the upgrade checked is incorrectly checking for DESC or ASC in the group by.

Answer

Hello,

From my understanding, upgrade from 2.10.1 to 3.01.0 failed with the below error message that can be found in the upgrade-prechecks.log. However, when you checked the affected six procedures does not contain the exact deprecated keywords.

Error Message :
The following routines contain keywords like 'QUERY CACHE', 'GROUP BY' with 'ASC' or 'DESC', 'SQL_CACHE', 'SQL_NO_CACHE' which are not supported in major version 8.0. It is recommended to drop these routines or rebuild routines without any of the above keywords before upgrade.

As you are aware of, the prechecks failed as a result of the stored procedures containing the one of the above keywords resulting to an error message. So it seems there are four(4) stored procedures that contains GROUP BY with DESC. Based on the https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals , the deprecated ASC or DESC qualifiers for GROUP BY clauses are removed.

According to the documentation https://aws.amazon.com/blogs/database/amazon-aurora-mysql-3-with-mysql-8-0-compatibility-is-now-generally-available/  If you need to sort the results of the query that uses GROUP BY with the ASC or DESC qualifier, you need to modify the query to use the ORDER BY clause with the ASC or DESC qualifier instead.

I would recommend that you please work closely with your DBA in changing the keywords in the stored procedures which have been deprecated. Also, please check the six stored procedures for the other keywords as it may also contain 'QUERY CACHE', 'SQL_CACHE' and 'SQL_NO_CACHE.

Additionally, you can consider dropping and recreating the stored procedures without any of the deprecated keywords and then retry upgrade to 3.01.0.

I hope this information can help resolve the issue.

If the information above does not help resolve the upgrade issue, please open a support case with AWS using the following https://console.aws.amazon.com/support/home#/case/create and provide details on the stored procedures along with upgrade-prechecks.log in the support case.

Aurora MySQL Upgrade From 2.10.1 to 3.01.0 (MySQL 5.7 to 8) Failing

Contenuto pertinente