Bug #75685 SHOW TRIGGERS removes escapes from body of CREATE statement
Submitted: 29 Jan 2015 20:05 Modified: 2 Feb 2015 14:56
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:any 5.0+ OS:Any
Assigned to: CPU Architecture:Any

[29 Jan 2015 20:05] Peter Laursen
Description:
SHOW TRIGGERS and SHOW CREATE TRIGGER are inconsistent.

How to repeat:
DELIMITER //

CREATE
TRIGGER `test_trigger` BEFORE DELETE ON `test_table`
FOR EACH ROW BEGIN
    DECLARE a VARCHAR(25);
    SET a = 'I''m not happy';
END //

DELIMITER ; 

SHOW TRIGGERS FROM `test` WHERE `Trigger`='test_trigger'; 
/* removes escapes
BEGIN 
DECLARE a VARCHAR(25);                                                                                                                                                                                                             
SET a = 'I'm not happy';                                                                                                                                                                                                           
END 
*/

-- whereas 
SHOW CREATE TRIGGER `test_trigger`; 
/* returns the SET statement in the body as 
SET a = 'I''m not happy'; 
*/

Suggested fix:
Don't strip the escapes. The two SHOW statements are inconsistent IMO. 

"a = 'I'm not happy';" is an invalid expression.  For same reason the return from SHOW TRIGGERS cannot be re-used programmatically. Doing so will return a syntax error. 

Workaround (in non-ANSI sql_mode) is to use doublequotes around the string like: 
SET a = "I'm not happy";
.. a human user can do that (if he is aware of this issue) but programmatically it is not possible to know advance what kind of qoutes may appear in a string and what kind of quotes to use. And both kinds of quotes may actually appear in sames tring like this (unescaped) example *Peter's Pizzabar to open next week will be named "PizzaYog"*.  What to do here? There is no solution for user how to write the TRIGGER and make the return from SHOW TRIGGERS usable for that TRIGGER.

-- Regards
-- Peter from 'Peter''s Programming Shop' (or was it 'Peter''s Pizzabar'?) :-)
[29 Jan 2015 20:09] Peter Laursen
corrected synopsis.
[29 Jan 2015 20:39] Peter Laursen
One more fix was needed in 'Peter''s synopsis'.
[2 Feb 2015 14:56] MySQL Verification Team
Thank you for the bug report.