Inexplicable precheck errors when attempting to upgrade Aurora MySql 2.11.2/5.7 to 3.06.0/8.0.34

0

We have previously successfully updated our development and staging databases to Aurora MySql 3.05.1/8.0.32. But when trying to do a dry run of upgrading our production database, the latest version of the upgrade precheck raises "MySQL 8.0 syntax check for routine-like objects" errors for stored procedures that previously passed without problems, and runs fine in MySql 8. For some of the errors we can simply not figure out what is causing these precheck errors. An example of a stored procedure that gets rejected:

CREATE DEFINER=xxxxxxx@% PROCEDURE BO_GetCurrencyRoles() LANGUAGE SQL DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER COMMENT '' BEGIN SELECT i.name AS ccy_name, cr.name AS ccy_role FROM currency_role_mapping cm INNER JOIN instrument i ON cm.currency_id = i.id INNER JOIN currency_role cr ON cm.currency_role_id = cr.id; END

For this stored procedure we get this precheck error: "level": "Error", "dbObject": "unify.BO_GetCurrencyRoles", "description": "at line 4,17: unexpected token 'AS'"

As far as we can see, we have quoted all names that could conflict with reserved names. If somebody could point out what we are overlooking, it will be greatly appreciated.

asked 15 days ago218 views
1 Answer
0

Hello,

Test in your development/staging environment running on MySQL major version 8 if you are able to create the procedure with same Create statement that is getting flagged by Precheck during upgrade.

If you are able to create the procedure during testing you could consider dropping the procedure before upgrade and recreating them post upgrade or consider reaching out to AWS Support via a support case.

Hope this helps!!

AWS
SUPPORT ENGINEER
Vinay
answered 14 days ago
  • That is of course a solution, but I see this as a last resort. We presently have 20+ stored procedures getting rejected for reasons we can't comprehend. So we would have to create scripts for dropping the procedures before the upgrade and re-reating after the upgrading. Not really a complication you want to add to an upgrade of a 265/24/7 system. As this wasn't a problem with previous versions of the upgrade pre-checks, I have a suspicion that at least some of the rejections simply aren't correct. So I guess we have to create a case with AWS. And at least understanding why the upgrade pre-check rejects such a simple stored procedure (with all reserved names quoted) would definitely be good.

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