Bug #86037 UPPER and LOWER randomly return incorrect values when used in prepared statement
Submitted: 21 Apr 2017 10:12 Modified: 24 Apr 2017 9:53
Reporter: Jack O'Sullivan Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:5.6.27, 5.6.34 OS:Linux (AWS RDS)
Assigned to: CPU Architecture:Any

[21 Apr 2017 10:12] Jack O'Sullivan 
Description:
I run a number of scripts that create prepared statements from parameters, and then execute those, e.g.

SET @sql = CONCAT('CREATE USER ', LOWER(@username),'@', '''', @clienthost, '''', ' IDENTIFIED BY ''', @password, '''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

...

SET @sql = CONCAT( 'INSERT INTO mytable (SELECT * FROM ', LOWER(@source_schema),'.theirtable WHERE mycolumn = ''', UPPER(@filter_value),''')' );
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

Occasionally, the LOWER and UPPER functions return something other than the expected value, which in the first case can cause the create user statement to fail, but in the second case might result in the wrong data (or no data) being copied.

In order to test this, I created a very basic test script, which I will attach separately. Most of the time when I run this, I get the correct data, but I have seen the following output:

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

Copyright (c) 2000, 2017, 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> select * from pairs;
+------------+-------------+
| first      | second      |
+------------+-------------+
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
| firstvalue | SECONDVALUE |
| firstvalue | SECONDVALUE |
| h          | SECONDVALUE |
+------------+-------------+
100 rows in set (0.00 sec)

----------

There doesn't seem to be a consistently reproducible pattern to this unfortunately, and the data returned from the method call sometimes contains special or non-printing characters, which makes it seem like it is just grabbing a random block of memory to return.

I have observed this only on Amazon RDS instances with server versions 5.6.27 and 5.6.34. RDS instances running 5.6.21 and 5.6.23 don't seem to be affected and neither do servers running locally on Centos (5.6.35) and Windows 10 (5.6.25), although the workloads particularly on the Windows server are much lower, and as this appears to be an indeterminate issue, that might just be coincidence. Unfortunately I don't have access to enough other servers to determine if this is entirely server version based or whether it has something to do with Amazon RDS implementation.

How to repeat:
Run the attached script (you may need to repeat this a number of times before you see the issue).
[21 Apr 2017 10:13] Jack O'Sullivan 
Test script that created the output in the description

Attachment: mysqlbugtest.sql (text/plain), 15.95 KiB.
[24 Apr 2017 9:33] MySQL Verification Team 
Hello Jack O'Sullivan,

Thank you for the report and test case.
On freshly built GA 5.6.36 with non-default settings I've imported provided test case some 100K times, and inserted 10M rows but not seeing the reported discrepancies.  Could you please confirm if you are able to reproduce this on latest GA?  If you are able to reproduce on latest build, and have reproducible test case then feel free to add it to this bug along with conf file used and change the status back to 'Open'.

Thank you for your interest in MySQL.

######
root@localhost [test]> select count(*) from pairs;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.93 sec)

root@localhost [test]> show create table pairs\G
*************************** 1. row ***************************
       Table: pairs
Create Table: CREATE TABLE `pairs` (
  `first` varchar(20) DEFAULT NULL,
  `second` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Thanks,
Umesh
[24 Apr 2017 9:53] Jack O'Sullivan 
Thanks for your investigation. I will see if I have a spare system that I can install the 5.6.36 GA on. 

My main concern now is that it is going to work on that (as I say, we have a 5.6.35 build that doesn't show this behaviour, so it may be something that has already been fixed), but that it will continue to happen on the Amazon RDS systems. Do you provide direct support of those images or are they possibly using AWS specific builds that differ from the default shipped binaries, in which case, should I be directing this to Amazon support rather than here?