| Bug #106950 | routine_definition in information_schema shows bad info with backticks | ||
|---|---|---|---|
| Submitted: | 8 Apr 2022 1:13 | Modified: | 8 Apr 2022 13:29 |
| Reporter: | Charles Lane | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S2 (Serious) |
| Version: | 8.0, 5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | procedure backticks | ||
[8 Apr 2022 2:37]
huahua xu
Hi, Charles Lane, For "show create procedure xxx", its field 'create procedure' comes from mysql.proc.body. For "select routine_definition from information_schema.routines", its field 'routine_definition' comes from mysql.proc.body_utf8.
[8 Apr 2022 11:34]
MySQL Verification Team
Hello Charles Lane, Thank you for the report and feedback. regards, Umesh
[8 Apr 2022 13:29]
Charles Lane
For Mysql 8.0, there is no mysql.proc table to use as an alternative to information_schema.routines.
[10 Apr 2022 5:41]
huahua xu
Yes, you are right. For Mysql 8.0, the table mysql.proc is replace by mysql.routines, and it is a data dictionary table which is invisible to the outside.
[10 Apr 2022 5:45]
huahua xu
in addition, show create table information_schema.routines please. You will find that the field `ROUTINE_DEFINITION` references to `routines`.`definition_utf8`
Description: Define a procedure with backticks in the definition, and while "show create procedure" gives a correct restatment of the definition, the ROUTINE_DEFINITION in information_schema.routines alters the number of backticks, and gives strange doubling of characters in the procedure definition. How to repeat: mysql>create procedure test () select `ab``cdef` from `xy``z`; mysql>show create procedure test\G *************************** 1. row *************************** Procedure: test sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test`() select `ab``cdef` from `xy``z` character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) The info in routines.information_schema is messed up: mysql> select routine_definition from information_schema.routines where routine_name='test'\G *************************** 1. row *************************** ROUTINE_DEFINITION: select `aab`cdeff` from `xxy`zz` note the doubled letters in the definition, and the double-to-single backticks.