[MDEV-7231] Field ROUTINE_DEFINITION in INFORMATION_SCHEMA.`ROUTINES` contains broken procedure body when used shielding quotes inside. Created: 2014-11-28  Updated: 2016-02-27  Resolved: 2016-02-24

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Procedure
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5, 10.0
Fix Version/s: 10.1.12

Type: Bug Priority: Major
Reporter: Mikhail Gavrilov Assignee: Alexander Barkov
Resolution: Fixed Votes: 1
Labels: procedure, quotes, triggers, upstream
Environment:

Linux Fedora 21

Attachments: PNG File screenshot-1.png    
Sprint: 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



 Comments   
Comment by Elena Stepanova [ 2014-11-29 ]

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

Comment by Mikhail Gavrilov [ 2014-11-30 ]

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.

Comment by Elena Stepanova [ 2014-11-30 ]

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.

Comment by Jan Lindström (Inactive) [ 2015-01-16 ]

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.

Comment by Jan Lindström (Inactive) [ 2015-01-16 ]

I do not know enough about MySQL parser to be able to fix this bug. Reassigning to bar.

Comment by Peter Laursen [ 2015-02-02 ]

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

Comment by Alexander Barkov [ 2016-02-17 ]

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' |
 
+----------------------+

Comment by Mikhail Gavrilov [ 2016-02-26 ]

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 ???

Generated at Thu Feb 08 07:18:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.