| 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: | |
| 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 | ||
[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.
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`;