[SOLVED] AWS MySQL Aurora major version (2 -> 3) upgrade pre-checks: "Obsolete procedure"

Issue

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!

I’ve already asked this question on AWS re:Post to no avail

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.

Solution

The workaround solution we went is is converting all of our flagged stored procedures to use prepared statements to break up the occurrences of "asc" and "desc". Using the example sp_1 above, the converted SP becomes

DELIMITER $$
CREATE PROCEDURE sp_1 ()
BEGIN
    DECLARE selectStr varchar(4095);
    SET selectStr = CONCAT('SELECT st.name as hderva', 'scferef, max(st.status)
    FROM SetupTableName st
    GROUP BY st.name;');
    SET @sqlQuery = insertStr;
    PREPARE execStr FROM @sqlQuery;
    EXECUTE execStr;
END$$
DELIMITER ;

Answered By – J. LaF

Answer Checked By – Candace Johnson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.