Bug #66414 incorrect quotation of statement in information_schema.TRIGGERS/ROUTINES
Submitted: 16 Aug 2012 3:11 Modified: 12 Sep 2012 3:09
Reporter: wei liu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.5.16, 5.5.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: information_schema, quotation, routines, triggers

[16 Aug 2012 3:11] wei liu
Description:
The content of column 'statement' of information_schema.TRIGGERS/ROUTINES will convert double quotation marks '' to single quotation marks '. e.g. '''A''' will be converted to ''A''.

How to repeat:
1. Create trigger
delimiter ;;
drop trigger if exists trg_tt01_bir_id ;;
CREATE trigger trg_tt01_bir_id

  before insert on tt01 for each row
begin
declare lv_str varchar(10);
    set lv_str = concat(new.id,'''A''');
end ;;
DELIMITER ;
2. select statement.
SELECT trigger_name,action_statement FROM information_schema.TRIGGERS T;
3. Get the statement with incorrect quotation marks 
begin
declare lv_str varchar(10);
    set lv_str = concat(new.id,''A'');
end

Suggested fix:
Please just do not convert double quotation marks '' to single quotation marks ' in information_schema.TRIGGERS/ROUTINES etc.
[19 Aug 2012 7:02] Valeriy Kravchuk
Double quote (as  a single character) is ", NOT ''. Please, check. You had use 3 single quotes in your example...
[21 Aug 2012 6:50] wei liu
My example is correct. The statement is '''A''', but information_schema.triggers show it as ''A''.
[22 Aug 2012 10:00] Valeriy Kravchuk
OK, you are right. We have a bug here:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tt01(id int);
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter //
mysql> CREATE trigger trg_tt01_bir_id
    ->
    ->   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.52 sec)

mysql> show create trigger trg_tt01_bir_id\G
*************************** 1. row ***************************
               Trigger: trg_tt01_bir_id
              sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTI
TUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger trg_tt01_bir_i
d

  before insert on tt01 for each row
begin
declare lv_str varchar(10);
    set lv_str = concat(new.id,'''A''');
end
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8_general_ci
1 row in set (0.06 sec)

mysql> SELECT trigger_name,action_statement FROM information_schema.TRIGGERS T
    -> WHERE trigger_name LIKE 'trg_tt01%'\G
*************************** 1. row ***************************
    trigger_name: trg_tt01_bir_id
action_statement: begin
declare lv_str varchar(10);
    set lv_str = concat(new.id,''A'');
end
1 row in set (0.22 sec)

as the output of SELECT is different from the output of SHOW CREATE TRIGGER.
[12 Sep 2012 3:09] wei liu
It seems that the MySQLadmin still has the same fault. Use mysqladmin to export tables with triggers, the script of trigger will still convert '''' to '''.
So the dump with triggers which generated by mysqladmin will be incorrect. And it will cause 'SQL syntax' error when use the dump.

But using mysqldump or 'show create trigger' can get the correct output.

So do I need to report another bug for the tool 'MySQLAdmin' or just leave it in this bug report?
[27 Sep 2017 15:38] Jay Waddington
Please fix that!
The error is obvious:

The problem is in `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.

The guys from jetbrains found that, see https://youtrack.jetbrains.com/issue/DBE-5060
[8 Oct 2021 14:14] Nicolai Parlog
Relaying a message from JetBrain's Maxim Sobolevskiy:

> It can be reproduced on ANY version including the last one 8.0.26.
>
> It affects us in the following way: it's impossible in DataGrip (and any
> other IntelliJ-based IDE) to show the correct source code of the, say,
> view.
>
> If the correct source is *create view qqq as select 'ab''s'*
> MySQL gives *create view qqq as select 'ab's'* which is incorrect.
[26 Jul 2023 14:24] Arina E
Bug still affects JetBrains IDE users. 

Recent example: https://youtrack.jetbrains.com/issue/DBE-18488