Elena Stepanova added a comment - 2014-11-29 00:12 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).

Mikhail Gavrilov added a comment - 2014-11-30 22:14 - edited 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.

Elena Stepanova added a comment - 2014-11-30 22:50 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.

Jan Lindström (Inactive) added a comment - 2015-01-16 14:30 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.

Jan Lindström (Inactive) added a comment - 2015-01-16 15:44 I do not know enough about MySQL parser to be able to fix this bug. Reassigning to bar.

Peter Laursen added a comment - 2015-02-02 14:04 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

Alexander Barkov added a comment - 2016-02-17 07:44 - edited A smaller test reproducing the problem: DROP FUNCTION IF EXISTS f1; CREATE FUNCTION f1() RETURNS TEXT RETURN 'I''m happy'; SELECT body_utf8 FROM mysql.proc WHERE name='f1'; SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='f1'; Both SELECT queries return a wrong result with the quote character removed: +--------------------+ | body_utf8 | +--------------------+ | RETURN 'I'm happy' | +--------------------+ If I rewrite the query slightly (notice the N prefix meaning NATIONAL CHARACTER literal): DROP FUNCTION IF EXISTS f1; CREATE FUNCTION f1() RETURNS TEXT RETURN N'I''m happy'; SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='f1'; it returns a correct result: +----------------------+ | body_utf8 | +----------------------+ | RETURN N'I''m happy' | +----------------------+

Mikhail Gavrilov added a comment - 2016-02-26 20:10 - edited Bug is not fixed if only upgrade server. SELECT * FROM (SELECT INFORMATION_SCHEMA.`ROUTINES`.`SPECIFIC_NAME` , INFORMATION_SCHEMA.`ROUTINES`.`ROUTINE_DEFINITION` FROM INFORMATION_SCHEMA.`ROUTINES` WHERE INFORMATION_SCHEMA.`ROUTINES`.`ROUTINE_TYPE` = 'PROCEDURE' AND INFORMATION_SCHEMA.`ROUTINES`.`ROUTINE_SCHEMA` = DATABASE()) t1 JOIN (SELECT `name` SPECIFIC_NAME ,`body` FROM `mysql`.`proc` WHERE db = DATABASE() AND `type`='PROCEDURE') t2 ON (t1.SPECIFIC_NAME = t2.SPECIFIC_NAME) HAVING t1.`ROUTINE_DEFINITION` != t2.body For fix this needed recreate all procedures. Can you add this in mysql_upgrade ???