| Bug #116354 | information_schema.ROUTINES is using incorrect quotation | ||
|---|---|---|---|
| Submitted: | 14 Oct 2024 15:25 | Modified: | 13 Nov 2024 16:47 |
| Reporter: | Vasilii Chernov | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 9.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | information_schema, quotation, routines, triggers | ||
[14 Oct 2024 15:37]
MySQL Verification Team
Hi Mr. Chernov, Thank you for your bug report. However , this is not a bug. You can have as many quotation marks in your strings, but you have to escape them, as it is explained in our Reference Manual. Not a bug.
[15 Oct 2024 12:39]
Vasilii Chernov
I found similar issue https://bugs.mysql.com/bug.php?id=66414 And it was a bug in MySQL 5.7... But not it's not a bug in MySQL 9, isn't it?
[15 Oct 2024 12:50]
MySQL Verification Team
Hi Mr. Chernov, You are correct. It is not a bug in latest 8.0, 8.4 and 9.0.
[13 Nov 2024 14:28]
Vasilii Chernov
I still think it's a bug, when I do the following. **Steps to reproduce:** - Create the following table with the trigger using MySQLWorkbench ``` create table tt01(id int); delimiter // create trigger test_trigger before insert on tt01 for each row begin declare lv_str varchar(10); set lv_str = concat(new.id, '''A'''); end// ``` - Open InformationSchema.TRIGGERS to get sources for the created trigger `test_trigger` from action_statement filed - It would be: ``` begin declare lv_str varchar(10); set lv_str = concat(new.id, ''A''); end ``` - Create another trigger `test_trigger2` for the same table with the sources from information schema, it would be "as is": ``` create trigger test_trigger2 before insert on tt01 for each row begin declare lv_str varchar(10); set lv_str = concat(new.id, ''A''); end ``` **Actual result:** MySQLWorkbench throws error and the whole query is underlined as invalid one. MySQLWorkbench UI screenshot: https://lh3.googleusercontent.com/d/1YWjjSYU7_0FahQnau3pA1mO0fRMTUByo Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4 **Expected result:** One can recreate trigger using sources from the information schema
[13 Nov 2024 14:37]
MySQL Verification Team
Hi Mr. Chernov, You are reporting bug in Information Schema, but not in the Workbench. So far, we only see a problem in the Workbench, whose version and release we know nothing about. Not a bug.
[13 Nov 2024 14:39]
MySQL Verification Team
Also, Mr. Chernov, 9.0.1 is replaced by 9.1.0. Hence, test it all on the current version .....
[13 Nov 2024 14:42]
MySQL Verification Team
Mr. Chernov, If you wish to report a problem in the Workbench, please change a category and put the version / release of the Workbench that you have been using.
[13 Nov 2024 15:13]
MySQL Verification Team
Hi Mr. Chernov, We are happy to inform you that we verified your bug as Workbench bug, but from there on, we shall see where the bug really is located.
[13 Nov 2024 16:32]
MySQL Verification Team
This looks like https://bugs.mysql.com/bug.php?id=66414 -- Shane, MySQL Senior Principal Technical Support Engineer Oracle Corporation http://dev.mysql.com/
[13 Nov 2024 16:46]
Vasilii Chernov
MySQLWorkbench was only example as IDE. I can face the same issue via mysql cli or any other tool. The information itself in ACTION_STATEMENT in information_schema.TRIGGERS is incorrect.
[13 Nov 2024 16:47]
Vasilii Chernov
And it reproducible in the latest MySQL 9.1 too
Description: The content of column 'statement' of information_schema.TRIGGERS and information_schema.ROUTINES will convert double quotation marks '' to single quotation marks '. e.g. '''B''' will be converted to ''B''. How to repeat: Create a table with a trigger. E.g.: create table tt01(id int); delimiter // create trigger test_trigger before insert on tt01 for each row begin declare lv_str varchar(10); set lv_str = concat(new.id, '''A'''); end// The same via mysql cli with output: mysql> create table tt01(id int); Query OK, 0 rows affected (0.04 sec) mysql> delimiter // mysql> create trigger test_trigger -> before insert on tt01 for each row -> begin -> declare lv_str varchar(10); -> set lv_str = concat(new.id, '''A'''); -> end// Query OK, 0 rows affected (0.04 sec) mysql> show create trigger test_trigger\G *************************** 1. row *************************** Trigger: test_trigger sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `test_trigger` BEFORE INSERT ON `tt01` FOR EACH ROW begin declare lv_str varchar(10); set lv_str = concat(new.id, '''A'''); end character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: utf8mb4_unicode_ci Created: 2024-10-14 15:16:03.79 1 row in set (0.01 sec) mysql> SELECT trigger_name,action_statement FROM information_schema.TRIGGERS T -> WHERE trigger_name LIKE 'test_%'\G *************************** 1. row *************************** TRIGGER_NAME: test_trigger ACTION_STATEMENT: begin declare lv_str varchar(10); set lv_str = concat(new.id, ''A''); end 1 row in set (0.01 sec) Suggested fix: No need to convert double quotation marks '' to single quotation mark '. The problem is in the file /mysql-server/sql/sql_lex.cc Function void Lex_input_stream::body_utf8_append_literal It appends const LEX_STRING *txt to body buffer, but this is unescaped string literal. So all escapes are gone. So mysql.proc.body_utf8 & information_schema.routines.routine_definition columns are invalid.