| Bug #105990 | Contribution by Tencent: parse ending of SP error | ||
|---|---|---|---|
| Submitted: | 28 Dec 2021 8:37 | Modified: | 28 Dec 2021 9:41 |
| Reporter: | Steven Duan (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S2 (Serious) |
| Version: | 8.0/5.6/5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | parse error | ||
[28 Dec 2021 8:43]
Steven Duan
remove the extra semicolon to prevent mysqldump export errors (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-bugfix-parse_ending_of_sp_error.patch (application/octet-stream, text), 2.34 KiB.
[28 Dec 2021 9:24]
Steven Duan
Update synopsis and fix suggestions.
Synopsis: Contribution by Tencent: parse ending of SP error
Fix:
a) When setting the SP terminator (sp_head.cc:set_body_end), we verify
that the above error and remove the extra semicolon to correct the end
pointer.
b) When mysqldump is executed, we remove the extra semicolon when
exporting the stored SP.
[28 Dec 2021 9:25]
Steven Duan
remove the extra semicolon to prevent mysqldump export errors (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-bugfix-parse_ending_of_sp_error.patch (application/octet-stream, text), 3.19 KiB.
[28 Dec 2021 9:41]
MySQL Verification Team
Thank you for the contribution.
Description: The parser in multi_statements mode keeps terminating semi-colon for a statement. When it is a DDL creating a stored program, the semi-colon becomes part of the definition. Tools like mysqldump wrap the definition within a version comment which does not support any semi-colon-terminated complete statement, as a result, the output could not be imported. When defining a SP (e.g., TRIGGER, PROCEDURE, and FUNCTION), we often set new separators to distinguish the default line separator (semicolon ';') and whole SP terminators. Normally, the newly defined delimiter is directly used as the ending character of the SP. Eg., DELIMITER // CREATE TRIGGER `TG` BEFORE UPDATE ON `t1` FOR EACH ROW LABELTRI: BEGIN SET new.NAME = '1'; END// Even if the last line incorrectly writes one more semicolon, the extra semicolon will be deleted when storing the SP. Eg., DELIMITER // ... ... END; // The problem is that when the user or other DBA auxiliary tools not only write one more semicolon, but also add additional characters before the newly defined end separator, the SP will incorrectly store one more semicolon. Eg., DELIMITER // ... ... END; *// The SP is stored as: DELIMITER // ... ... END; At this time, when mysqldump is used, the last line will generate "end;". Which causes errors when importing the SP. Fix: When setting the SP terminator (sp_head.cc:set_body_end), we verify that the above error and remove the extra semicolon to correct the end pointer. How to repeat: create table ttt1 (c1 int); DELIMITER ;; CREATE TRIGGER `tg` BEFORE UPDATE ON `ttt1` FOR EACH ROW LABELTRI: BEGIN SET new.c1 = 2; END; 8888;; mysqldump ... > sp.sql mysql ... < ./sp.sql Suggested fix: When setting the SP terminator (sp_head.cc:set_body_end), we verify that the above error and remove the extra semicolon to correct the end pointer.