Bug #63410 string corrupted in stored procedure concat and cast
Submitted: 23 Nov 2011 21:17 Modified: 17 Feb 2012 18:49
Reporter: Mark Robson Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1.60, 5.1.61 OS:FreeBSD (x86_64)
Assigned to: CPU Architecture:Any
Tags: 5.1.61

[23 Nov 2011 21:17] Mark Robson
Description:
A stored procedure is calling CAST and CONCAT to produce some strings. Zeros or uninitialised memory is sometimes (intermittently) inserted in the middle of strings.

Run this script below on a recently-booted server. Two output lines should be similar.

Output for me (5.1.60, FreeBSD 7.3, x86_64) is:

QUERY IS2
INSERT INTO UserList(userEmail, domainID, timeAdded, passwordHas\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 VALUES( 'bbb',6,1322080568,'deprecated',1080413965)
EXPECTED OUTPUT
INSERT INTO UserList(userEmail, domainID, timeAdded, passwordHash, userEmailHash) VALUES('bbb',6,1322080568,'deprecated',1080413965)

Note the \0\0 !

How to repeat:
SET NAMES utf8;
DELIMITER //
DROP PROCEDURE IF EXISTS testproc1 //
CREATE PROCEDURE testproc1 ()
BEGIN
  DECLARE x int default 0;
  DO 0;
END//
DROP PROCEDURE IF EXISTS testproc2 //
CREATE PROCEDURE testproc2 ()
BEGIN
  DECLARE x int default 0;
  DO 0;
  CALL testproc1();
END//

DROP PROCEDURE IF EXISTS crashproc //
CREATE PROCEDURE crashproc (u_id BIGINT(20), u_email TEXT, u_d_id BIGINT(20), u_email_hash INT(11) unsigned)
BEGIN
	SET @query = "INSERT INTO UserList";
    SET @fields = "(userEmail, domainID, timeAdded, passwordHash, userEmailHash)";
    SET @values = "'bbb',6,1322080568,'deprecated',1080413965";
	-- select 'CRASHPROC' as `CRASHPROC`;
	-- select @values as `VALUES IS1`; 
	-- select @fields as `FIELDS IS1`; 
	-- select @query as `QUERY IS1`; 
        SET @query = CONCAT(@query, CAST(@fields AS CHAR), " VALUES( ", CAST(@values AS CHAR), ")");
	select @query as `QUERY IS2`; 
END //
DELIMITER ;

CALL testproc2();
call crashproc(null,null,null,null);

select 'INSERT INTO UserList(userEmail, domainID, timeAdded, passwordHash, userEmailHash) VALUES(\'bbb\',6,1322080568,\'deprecated\',1080413965)' AS `EXPECTED OUTPUT`;
[24 Nov 2011 11:03] Valeriy Kravchuk
Please, send the output of:

show variables like 'char%';

from your environment. 

I see no problems with 5.1.58 on Windows, for example:

mysql> CALL testproc2();
Query OK, 0 rows affected (0.03 sec)

mysql> call crashproc(null,null,null,null);
+-------------------------------------------------------------------------------
--------------------------------------------------------+
| QUERY IS2
                                                        |
+-------------------------------------------------------------------------------
--------------------------------------------------------+
| INSERT INTO UserList(userEmail, domainID, timeAdded, passwordHash, userEmailHa
sh) VALUES( 'bbb',6,1322080568,'deprecated',1080413965) |
+-------------------------------------------------------------------------------
--------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.1.58-community-log |
+----------------------+
1 row in set (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | utf8
     |
| character_set_connection | utf8
     |
| character_set_database   | utf8
     |
| character_set_filesystem | binary
     |
| character_set_results    | utf8
     |
| character_set_server     | utf8
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.1\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)

So either this is a recent regression, or I just missed some details.
[24 Nov 2011 13:36] Mark Robson
As far as I know:

1. The bug only occurs on FreeBSD x86_64.
2. The bug is a recent regression and did not occur in 5.1.52, but does exist in 5.1.58 and 5.1.60. We have not tested it with every released version.

Here are my charset variables:

mysql> show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)
[19 Dec 2011 18:30] Sveta Smirnova
Thank you for the feedback.

Please send output of \s command of MySQL command line client.
[19 Dec 2011 21:20] Mark Robson
Output is:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.1.54, for portbld-freebsd7.3 (amd64) using  5.2

Connection id:		2
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		more
Using outfile:		''
Using delimiter:	;
Server version:		5.1.60-log FreeBSD port: mysql-server-5.1.60
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock
Uptime:			10 sec

Threads: 1  Questions: 28  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 9  Queries per second avg: 2.800
--------------
[19 Dec 2011 21:22] Mark Robson
The bug is also reproducible with the official Oracle build. \s gives this output:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.1.54, for portbld-freebsd7.3 (amd64) using  5.2

Connection id:		2
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		more
Using outfile:		''
Using delimiter:	;
Server version:		5.1.60-log MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock
Uptime:			8 sec

Threads: 1  Questions: 28  Slow queries: 0  Opens: 16  Flush tables: 1  Open tables: 9  Queries per second avg: 3.500
--------------
[20 Dec 2011 11:26] Sveta Smirnova
Thank you for the feedback.

You use FreeBSD port. You also use pretty old client: version 5.1.54, not 5.1.60 as indicated in the report and you use for server. Please try our binaries available from dev.mysql.com/downloads to exclude possibility this is port issue. I think this can be client problem, so you can install our MySQL distribution into separate directory and try client first.
[20 Dec 2011 11:27] Sveta Smirnova
Sorry, replied before seeing your last reply. But you still use 5.1.54 client. Please try with official Oracle client too.

Thanks in advance.
[20 Dec 2011 11:40] Mark Robson
Hi, reproducible using offical Oracle client and server:

---

mysql> \s
--------------
/usr/local/mysql-5.1.60-freebsd7.0-x86_64/bin/mysql  Ver 14.14 Distrib 5.1.60, for unknown-freebsd7.0 (x86_64) using readline 5.1

Connection id:		4
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		more
Using outfile:		''
Using delimiter:	;
Server version:		5.1.60-log MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock
Uptime:			3 min 2 sec

Threads: 1  Questions: 57  Slow queries: 0  Opens: 16  Flush tables: 1  Open tables: 9  Queries per second avg: 0.313
--------------

mysql> Bye
nodelocaldb2# /usr/local/mysql-5.1.60-freebsd7.0-x86_64/bin/mysql test < crashproc.sql
QUERY IS2
INSERT INTO UserList(userEmail, domainID, timeAdded, passwordHas\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 VALUES( 'bbb',6,1322080568,'deprecated',1080413965)
EXPECTED OUTPUT
INSERT INTO UserList(userEmail, domainID, timeAdded, passwordHash, userEmailHash) VALUES('bbb',6,1322080568,'deprecated',1080413965)
[17 Feb 2012 18:49] Sveta Smirnova
Thank you for the feedback.

Verified as described. Bug is only repeatable if load SQL from the file. Bug is not repeatable on Linux.