By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Aurora upgrade 2 to 3 / MySql 5.7 to 8.0: potential bug in pre-check validation (depreciated words)

0

We have noticed that the pre-checks for the upgrade of MySQL 5.7 to MySQL 8 are having issues with character combinations that "resemble" depreciated words. For example, the depreciated "Group By ... DESC" is one of those constructs "https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html#USER_UpgradeDBInstance.MySQL.57to80Prechecks" "There must be no queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses."

While our stored procedures use Group by's, there is no associated "DESC" word with them. However, the character sequence does appear in the stored procedure in various forms:

  • There is a call to another stored procedure called "update_fcdescription();". It has the characters "desc" within the name
  • There are columns in the queries (table columns) with the name "blahDescriptionblah"
  • There is a block comment that has the word "Description:" that describes the stored procedure (documentation) However, there are no "DESC" words associated with the "Group by".

For testing,

  • I deleted the comments word, and that issue no longer appeared as an error
  • I renamed the call to the other stored procedure update_fcdescription(); to update_fcdxexscxrixption();, and that issue no longer appeared as an error
  • The columns that have the characters "desc" I couldn't work around without a lot of changing to the stored procedure

It seems that there is a Stackoverflow question outlining this behavior too: https://stackoverflow.com/questions/71412470/aws-mysql-aurora-major-version-2-3-upgrade-pre-checks-obsolete-procedure Also, a "re:Post" question too: https://repost.aws/questions/QUWJzlcpitRoGM0woZVOylBQ/aurora-2-to-3-mysql-5-7-to-8-0-upgrade-pre-check-incorrect-validation-on-store-procedure

This is clearly a bug in the pre-check process and is limiting our upgrade from MySQL 5.7 to 8.

Any updates on this being fixed/addressed? Thank you.

Topics
Migration & ModernizationDatabaseAnalyticsManagement & Governance
Tags
AWS Database Migration ServiceGeneral Database MigrationsAmazon AuroraAWS Managed ServicesAurora MySQL
Language
English
markwilhelm
asked 2 years ago569 views
2 Answers
0

Hello,

I understand upgrade failed when upgrading from Amazon Aurora DB Cluster version MySQL 5.7 to MySQL 8.

From the upgrade-prechecks.log, I believe you are seeing the below error message which indicate that there are some keywords present which are not supported in version 8.0.

Error: 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.",

Based on your findings and from my investigation, it seems the keyword is also reading from objects such as the stored procedure (e.g update_fcdescription()) which have the keyword DESC present in the stored procedure name.

Therefore, please note that I will escalate this behavior internally so that it can be investigated and rectified to avoid same issue from occurring in future. If further investigation is required on your DB Cluster, I would advise creating a support case.

Thank you for bringing this information to our attention. Your feedback is always appreciated.

AWS
SUPPORT ENGINEER
Somto_M
answered 2 years ago
  • markwilhelm
    2 years ago

    Thank you. I had already created a support case with much of the information presented here. I would point out that even in block comments the character combination of "desc" is also considered an error if the stored procedure has a "GROUP BY" anywhere in the stored procedure.

    I look forward to the resolution as I'm sure countless people are.

    On a side note, any idea when in-place upgrades (5.7 to 8) will happen?

    Thanks again.

0

Hello,

Thank you for writing back. I understand you want to know when the in-pace upgrade from 5.7 to 8.0 will be available.

Please note that I have no ETA for when an in-place upgrade from Aurora MySQL version 5.7 to 8.0 will be available. However, I would recommend that you refer to the links below for feature releases and latest announcement. Once in-place upgrade is implemented, information will be announced in the links below :

[-] What's New with Database? - https://aws.amazon.com/about-aws/whats-new/database/?whats-new-content.sort-by=item.additionalFields.postDateTime&whats-new-content.sort-order=desc&awsf.whats-new-products=*all

[-] Category: Amazon RDS - https://aws.amazon.com/blogs/aws/category/database/amazon-rds/

Additionally, please note that I have no ETA for when the issue with the keywords during upgrade would be fixed. However, since you have a case created for this issue, I would recommend that you continue to address the issue via the case you have created.

Thank you.

AWS
SUPPORT ENGINEER
Somto_M
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content