Bug #100396 "SELECT... FOR UPDATE... INTO... " statement no longer parses correctly

• Submitted: 31 Jul 2020 7:39
• Modified: 31 Jul 2020 8:35
• Reporter: Tudor Costin
• Status: Verified
• Category: MySQL Workbench: SQL Editor
• Severity: S3 (Non-critical)
• Version: 8.0.21
• OS: Windows
• CPU Architecture: Any

[31 Jul 2020 7:39] Tudor Costin

Description:
MySQL server v8 changed the relative order of the above keywords (FOR UPDATE precedes the INTO clause).

This was working fine in Workbench until the most recent minor patch: any DDL retrieved from the server is listed as incorrect. The SQL editor inside Workbench now expects that the FOR UPDATE clause must come after the INTO clause. It will not accept statements with the v8 order (an editor for a stored procedure or a trigger will not allow save until the clauses are reversed). However, trying to push this to the server fails, since the server expects the opposite ordering.

This affects stored procedures, functions, and triggers. In fact, a table with a trigger that contains a statement as above does not load into the designer at all (since it believes the DDL retrieved from the server is incorrect).

How to repeat:
I noticed the in the context of a table with an existing trigger (see above). But the simplest way to reproduce the issue is to create a trivial stored proc that contains a statement as above.

[31 Jul 2020 8:35] MySQL Verification Team

Hello  Tudor Costin,

Thank you for the report and feedback.

regards,
Umesh