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

0

We believe we have noticed some weird behavior in AWS upgrade pre-checks for Aurora 2 to 3 / Mysql 5.7 to 8.0.

We believe it is related to the AWS-specific rule "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," though we are not breaking this rule.

Our findings: (SP1)

DELIMITER $$
CREATE PROCEDURE sp_1 ()
BEGIN
    SELECT st.name as hdervascferef, max(st.status)
    FROM SetupTableName st
    GROUP BY st.name;
END$$
DELIMITER ;

Produces a precheck error:

{
      "level": "Error",
      "dbObject": "trax.sp_1",
      "description": "Obsolete procedure - trax.sp_1. Contains depreciated keywords."
},

But: (SP2)

DELIMITER $$
CREATE PROCEDURE sp_2 ()
BEGIN
    SELECT st.name as hdervasferef, max(st.status)
    FROM SetupTableName st
    GROUP BY st.name;
END$$
DELIMITER ;

Does not produce an error.

The only difference here is that the alias, hdervascferef from SP1, an arbitrary string containing substring asc, while the alias hdervasferef alias from SP2 has the 'c' removed and thus does not contain substring asc, and so there is no error.

We are running into this on many stored procedures because we have many tables with a column named hasChilds, which has the asc substring, and thus prevents these SPs from passing the pre-check. We have found removing instances of letters asc from an SP causes the pre-check to pass, but this is not a viable option for us, as the use of the hasChilds column in our stored procedures is vital to their function.

Replication steps :
  1. Add these two SPs into our AWS Aurora instance with engine = 5.7.mysql_aurora.2.07.2
  2. follow instructions at AWS RDS MySql Testing an Upgrade
  3. verify failed prechecks for SP1 but not SP2

We would appreciate any help / guidance that can be provided!

tl;dr

I think we are being flagged for the "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," due to our query having a group by and the sub string 'asc' in it, despite not actually breaking the rule, and this prevents us from upgrading our Aurora instances because we fail the pre-check.

asked 9 months ago125 views
1 Answer
0

I suggest creating a support ticket for this issue, they will be able to take a look at what was happening and help with the remediation.

answered 8 months 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