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.

1개 답변
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.

AWS
답변함 2년 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인