MySQL Bugs: #79273: Stored Routing Returning Incorrect Value

Bug #79273	Stored Routing Returning Incorrect Value
Submitted:	13 Nov 2015 14:22	Modified:	18 Jan 2016 14:25
Reporter:	Scott Nebor	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.6.27/5.5/5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Starting with mysql 5.6.27, a stored procedure can return an invalid/incorrect value

How to repeat:
-- create a simple table
CREATE TABLE `TestTable` (
  `ColumnA` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- add two rows to the table
INSERT INTO `TestTable` (`ColumnA`) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ');
INSERT INTO `TestTable` (`ColumnA`) VALUES ('1234567890');

-- create a stored procedure
CREATE PROCEDURE `testFunc`()
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
    DECLARE lDataResult text;

       SELECT ColumnA INTO lDataResult FROM TestTable WHERE ColumnA='ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    SELECT  lDataResult;
END

-- call the function
call testFunc();

This should return "ABCDEFGHIJKLMNOPQRSTUVWXYZ".  Instead, it returns "1234567890KLMNOPQRSTUVWXYZ" which is a mix of the two rows in TestTable

A few comments
- this issue does not happen in mysql 5.6.26 or 5.6.25 (I haven't tried any other versions)
- the stored procedure works fine if TestTable only has one row
- if lDataResult is declared as a varchar, then the issue is not seen.  It needs to be text or blob

Suggested fix:
In the example provided, the stored procedure should return the correct data

C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.74-Win X64 Source distribution

Copyright (c) 2000, 2013, 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 5.1 > CREATE DATABASE s;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 > USE s
Database changed
mysql 5.1 > CREATE TABLE `TestTable` (
    ->   `ColumnA` varchar(64) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql 5.1 >
mysql 5.1 > -- add two rows to the table
mysql 5.1 > INSERT INTO `TestTable` (`ColumnA`) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ');
Query OK, 1 row affected (0.00 sec)

mysql 5.1 > INSERT INTO `TestTable` (`ColumnA`) VALUES ('1234567890');
Query OK, 1 row affected (0.00 sec)

mysql 5.1 > DELIMITER $$
mysql 5.1 > CREATE PROCEDURE `testFunc`()
    ->  LANGUAGE SQL
    ->  NOT DETERMINISTIC
    ->  CONTAINS SQL
    ->  SQL SECURITY DEFINER
    ->  COMMENT ''
    -> BEGIN
    ->     DECLARE lDataResult text;
    ->
    ->        SELECT ColumnA INTO lDataResult FROM TestTable WHERE ColumnA='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    ->
    ->     SELECT  lDataResult;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 > DELIMITER ;
mysql 5.1 > call testFunc();
+----------------------------+
| lDataResult                |
+----------------------------+
| ABCDEFGHIJKLMNOPQRSTUVWXYZ |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql 5.1 >

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.47 Source distribution PULL: 2015-NOV-07

Copyright (c) 2000, 2015, 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 5.5 > CREATE DATABASE s;
Query OK, 1 row affected (0.00 sec)

mysql 5.5 > USE s
Database changed
mysql 5.5 > CREATE TABLE `TestTable` (
    ->   `ColumnA` varchar(64) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql 5.5 > -- add two rows to the table
mysql 5.5 > INSERT INTO `TestTable` (`ColumnA`) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ');
Query OK, 1 row affected (0.01 sec)

mysql 5.5 > INSERT INTO `TestTable` (`ColumnA`) VALUES ('1234567890');
Query OK, 1 row affected (0.03 sec)

mysql 5.5 > DELIMITER $$
mysql 5.5 > CREATE PROCEDURE `testFunc`()
    ->  LANGUAGE SQL
    ->  NOT DETERMINISTIC
    ->  CONTAINS SQL
    ->  SQL SECURITY DEFINER
    ->  COMMENT ''
    -> BEGIN
    ->     DECLARE lDataResult text;
    ->        SELECT ColumnA INTO lDataResult FROM TestTable WHERE ColumnA='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    ->     SELECT  lDataResult;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 > DELIMITER ;
mysql 5.5 > call testFunc();
+----------------------------+
| lDataResult                |
+----------------------------+
| 1234567890KLMNOPQRSTUVWXYZ |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql 5.5 >

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.28 Source distribution PULL: 2015-NOV-07

Copyright (c) 2000, 2015, 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 5.6 > CREATE DATABASE s;
Query OK, 1 row affected (0.00 sec)

mysql 5.6 > USE s
Database changed
mysql 5.6 > CREATE TABLE `TestTable` (
    ->   `ColumnA` varchar(64) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql 5.6 > -- add two rows to the table
mysql 5.6 > INSERT INTO `TestTable` (`ColumnA`) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ');
Query OK, 1 row affected (0.01 sec)

mysql 5.6 > INSERT INTO `TestTable` (`ColumnA`) VALUES ('1234567890');
Query OK, 1 row affected (0.01 sec)

mysql 5.6 > DELIMITER $$
mysql 5.6 > CREATE PROCEDURE `testFunc`()
    ->  LANGUAGE SQL
    ->  NOT DETERMINISTIC
    ->  CONTAINS SQL
    ->  SQL SECURITY DEFINER
    ->  COMMENT ''
    -> BEGIN
    ->     DECLARE lDataResult text;
    ->        SELECT ColumnA INTO lDataResult FROM TestTable WHERE ColumnA='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    ->     SELECT  lDataResult;
    -> END$$
Query OK, 0 rows affected (0.06 sec)

mysql 5.6 > DELIMITER ;
mysql 5.6 > call testFunc();
+----------------------------+
| lDataResult                |
+----------------------------+
| ABCDEFGHIJKLMNOPQRSTUVWXYZ |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql 5.6 > exit
Bye

C:\dbs>5.6\bin\mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.6.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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 DATABASE s;
Query OK, 1 row affected (0.00 sec)

mysql> USE s
Database changed
mysql> CREATE TABLE `TestTable` (
    ->   `ColumnA` varchar(64) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql> -- add two rows to the table
mysql> INSERT INTO `TestTable` (`ColumnA`) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `TestTable` (`ColumnA`) VALUES ('1234567890');
Query OK, 1 row affected (0.05 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE `testFunc`()
    ->  LANGUAGE SQL
    ->  NOT DETERMINISTIC
    ->  CONTAINS SQL
    ->  SQL SECURITY DEFINER
    ->  COMMENT ''
    -> BEGIN
    ->     DECLARE lDataResult text;
    ->        SELECT ColumnA INTO lDataResult FROM TestTable WHERE ColumnA='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    ->     SELECT  lDataResult;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call testFunc();
+----------------------------+
| lDataResult                |
+----------------------------+
| ABCDEFGHIJKLMNOPQRSTUVWXYZ |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

C:\dbs>57

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.10 Source distribution PULL: 2015-NOV-07

Copyright (c) 2000, 2015, 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 5.7 > CREATE DATABASE s;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > USE s
Database changed
mysql 5.7 > CREATE TABLE `TestTable` (
    ->   `ColumnA` varchar(64) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql 5.7 > -- add two rows to the table
mysql 5.7 > INSERT INTO `TestTable` (`ColumnA`) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ');
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > INSERT INTO `TestTable` (`ColumnA`) VALUES ('1234567890');
Query OK, 1 row affected (0.03 sec)

mysql 5.7 > DELIMITER $$
mysql 5.7 > CREATE PROCEDURE `testFunc`()
    ->  LANGUAGE SQL
    ->  NOT DETERMINISTIC
    ->  CONTAINS SQL
    ->  SQL SECURITY DEFINER
    ->  COMMENT ''
    -> BEGIN
    ->     DECLARE lDataResult text;
    ->        SELECT ColumnA INTO lDataResult FROM TestTable WHERE ColumnA='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    ->     SELECT  lDataResult;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > DELIMITER ;
mysql 5.7 > call testFunc();
+----------------------------+
| lDataResult                |
+----------------------------+
| 1234567890KLMNOPQRSTUVWXYZ |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql 5.7 >

On Debian:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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 `TestTable` (
    ->   `ColumnA` varchar(64) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> -- add two rows to the table
mysql> INSERT INTO `TestTable` (`ColumnA`) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `TestTable` (`ColumnA`) VALUES ('1234567890');
Query OK, 1 row affected (0.01 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE `testFunc`()
    -> LANGUAGE SQL
    -> NOT DETERMINISTIC
    -> CONTAINS SQL
    -> SQL SECURITY DEFINER
    -> COMMENT ''
    -> BEGIN
    ->     DECLARE lDataResult text;
    -> 
    ->        SELECT ColumnA INTO lDataResult FROM TestTable WHERE ColumnA='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    -> 
    ->     SELECT  lDataResult;
    -> END$$
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;
mysql> call testFunc();
+----------------------------+
| lDataResult                |
+----------------------------+
| 1234567890KLMNOPQRSTUVWXYZ |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Thank you for the bug report. I could repeat 5.6.27 on Linux (not Windows), 5.7 on both Linux and Windows and 5.5 on Windows.

miguel@debianvb:~/mysql-5.6.27$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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 `TestTable` (
    ->   `ColumnA` varchar(64) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> -- add two rows to the table
mysql> INSERT INTO `TestTable` (`ColumnA`) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `TestTable` (`ColumnA`) VALUES ('1234567890');
Query OK, 1 row affected (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE `testFunc`()
    -> LANGUAGE SQL
    -> NOT DETERMINISTIC
    -> CONTAINS SQL
    -> SQL SECURITY DEFINER
    -> COMMENT ''
    -> BEGIN
    ->     DECLARE lDataResult text;
    -> 
    ->        SELECT ColumnA INTO lDataResult FROM TestTable WHERE ColumnA='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    -> 
    ->     SELECT  lDataResult;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call testFunc();
+----------------------------+
| lDataResult                |
+----------------------------+
| 1234567890KLMNOPQRSTUVWXYZ |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
miguel@debianvb:~/mysql-5.6.27$ lsb_release -a
No LSB modules are available.
Distributor ID:	Debian
Description:	Debian GNU/Linux 8.2 (jessie)
Release:	8.2
Codename:	jessie
miguel@debianvb:~/mysql-5.6.27$

See http://bugs.mysql.com/bug.php?id=79184.

Noted in 5.5.48, 5.6.29, 5.7.11, 5.8.0 changelogs.

Data corruption could occur if a stored procedure had a variable
declared as TEXT or BLOB and data was copied to that variable using
SELECT ... INTO syntax from a TEXT or BLOB column.

The release notes for 2.6.29 contains this:

Data corruption or a server exit could occur if a stored procedure had a variable declared as TEXT or BLOB and data was copied to that variable using SELECT ... INTO syntax from a TEXT or BLOB column. (Bug #22203532, Bug #22232332, Bug #21941152)

I cloned the server source for 2.6.29 from github and searched through the commit logs for the three 8-digit bug numbers - couldn't find 1 and 3, but found 2 - here's the relevant link on github:

https://github.com/mysql/mysql-server/commit/863f7cebd79e76f90bd8f1e3e0c1a1de5fe77d07