Details
Major
Description
Demonstration:
SELECT (SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.`ROUTINES` IR WHERE IR.ROUTINE_SCHEMA = 'BPLNew' AND IR.`SPECIFIC_NAME` = 'get_dict_source') = (SELECT body FROM `mysql`.`proc` mp WHERE mp.db='BPLnew' AND mp.`name` = 'get_dict_source') compare
compare
---------
0
As you can see on the screenshot comparison only difference is the amount of shielding quotes.
Why?
Yet another example:
Example:
SHOW CREATE PROCEDURE test
DELIMITER $$
CREATE PROCEDURE `test`(
)
BEGIN
SELECT CONCAT('ABC = ''',1,''''), CONCAT('ABC = ',2);
END$$
DELIMITER ;
Information schema:
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.`ROUTINES` IR WHERE IR.ROUTINE_SCHEMA = 'test' AND IR.`SPECIFIC_NAME` = 'test'
ROUTINE_DEFINITION
---------------------------------------------------------------
BEGIN
select concat('ABC = '',1,'''), CONCAT('ABC = ',2);
END
mysql:
SELECT body FROM `mysql`.`proc` mp WHERE mp.db='test' AND mp.`name` = 'test'
body
-----------------------------------------------------------------
BEGIN
select concat('ABC = ''',1,''''), CONCAT('ABC = ',2);
END
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I think priority must be increased because also affected triggers:
Demonstration:
CREATE TABLE `test` (
`a` MEDIUMTEXT,
`b` MEDIUMTEXT
) ENGINE=MYISAM DEFAULT CHARSET=utf8;
DELIMITER $$
CREATE TRIGGER `test` BEFORE INSERT ON `test`
FOR EACH ROW BEGIN
SET NEW.a=CONCAT('ABC = ''',1,'''');
SET NEW.b=CONCAT('ABC = ',2);
END$$
DELIMITER ;
SELECT
IT.`TRIGGER_NAME`
,IT.`EVENT_OBJECT_TABLE`
,IT.ACTION_STATEMENT
FROM INFORMATION_SCHEMA.`TRIGGERS` IT
WHERE IT.`TRIGGER_SCHEMA`='test' AND IT.`TRIGGER_NAME` = 'test';
TRIGGER_NAME EVENT_OBJECT_TABLE ACTION_STATEMENT
------------ ------------------ ----------------------------------------------------------------------------
test test BEGIN
SET NEW.a=CONCAT('ABC = '',1,''');
SET NEW.b=CONCAT('ABC = ',2);
END
SHOW TRIGGERS FROM `test` WHERE `trigger` = 'test'
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
------- ------ ------ -------------------------------------------------------------------------- ------ ------- ------------------------------------------------------------------------------------------------------------------------- -------- -------------------- -------------------- --------------------
test INSERT test BEGIN BEFORE (NULL) NO_BACKSLASH_ESCAPES,STRICT_ALL_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@::1 utf8 utf8_general_ci utf8_general_ci
SET NEW.a=CONCAT('ABC = '',1,''');
SET NEW.b=CONCAT('ABC = ',2);
END
INSERT INTO test(a,b) VALUES (NULL, NULL) SELECT * FROM test
a b --------- --------- ABC = '1' ABC = 2
As you can see trigger is worked but I cannot get it body.
If for PROCEDURE and FUNCTIONS exists workaround with mysql database, for trigger this workaround not work because triggers not stored into mysql database.
It's just the standard procedure – we usually set priority for upstream bugs to minor, because we want to wait and see whether upstream fixes them; and if not, the priority can be raised.
On the other hand, the upstream bug has been there for quite a while, over 3 years, still not fixed. Increasing priority as requested.
Problem is that on mysql.proc table body nad body_utf8 are not exactly the same even on default character set:
SELECT body FROM `mysql`.`proc` mp WHERE mp.db='test' AND mp.`name` = 'test';
body
BEGIN
SELECT CONCAT('ABC = ''',1,''''), CONCAT('ABC = ',2);
END
SELECT body_utf8 FROM `mysql`.`proc` mp WHERE mp.db='test' AND mp.`name` = 'test';
body_utf8
BEGIN
SELECT CONCAT('ABC = '',1,'''), CONCAT('ABC = ',2);
END
This difference is made when procedure body is constructed while parsing and error is somewhere on literal handling.
I do not know enough about MySQL parser to be able to fix this bug. Reassigning to bar.
Independent of this discussion I posted to bugs.mysql.com http://bugs.mysql.com/bug.php?id=75685 a few days ago.
It was originally reported by one of our users here: http://forums.webyog.com/index.php?showtopic=7625
Thanks for the report.
It's an upstream issue http://bugs.mysql.com/bug.php?id=58342 (still reproducible on MySQL 5.1 - 5.7).