Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7231

Field ROUTINE_DEFINITION in INFORMATION_SCHEMA.`ROUTINES` contains broken procedure body when used shielding quotes inside.

Details

    • 10.1.11, 10.1.12

    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                                                                                                               

      Attachments

        Activity

          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).

          elenst Elena Stepanova added a comment - 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 Mikhail Gavrilov added a comment - - 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.

          mikhail Mikhail Gavrilov added a comment - - 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.

          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.

          elenst Elena Stepanova added a comment - 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.

          jplindst Jan Lindström (Inactive) added a comment - 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.

          jplindst Jan Lindström (Inactive) added a comment - I do not know enough about MySQL parser to be able to fix this bug. Reassigning to bar.
          Peter Laursen Peter Laursen added a comment -

          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

          Peter Laursen Peter Laursen added a comment - 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
          bar Alexander Barkov added a comment - - 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' |
          +----------------------+

          bar Alexander Barkov added a comment - - 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 Mikhail Gavrilov added a comment - - 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 ???

          mikhail Mikhail Gavrilov added a comment - - 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 ???

          People

            bar Alexander Barkov
            mikhail Mikhail Gavrilov
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.