Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.1.67, 5.2.14, 5.3.12, 5.5(EOL), 10.0(EOL)
-
Linux Fedora 21
-
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
Issue Links
Activity
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.
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.
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
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' |
|
+----------------------+
|
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 ???
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).