Bug #42650 Character set is broken when using prepared statements inside procedures
Submitted: 6 Feb 2009 14:26 Modified: 12 Feb 2009 17:30
Reporter: Anca Dogaru Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1.30 OS:Linux (RHEL 4 )
Assigned to: CPU Architecture:Any
Tags: character set, prepared statments, regression, stored procedures

[6 Feb 2009 14:26] Anca Dogaru
Description:
If inside a procedure a prepared statement is used, data inserted have the character set broken (utf8 is no longer used).

This happens only when the prepared statement is before concatenated with a integer value.

The replication scenario contains 3 procedures that should have the same output, but the last one shows the bug.

How to repeat:
CREATE DATABASE `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE testdb;

CREATE TABLE `test_tbl` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
PRIMARY KEY ( `id` )
) TYPE = innodb CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO test_tbl VALUES (1, 'tst1'), (2, 'tst2'), (3, 'tst3');

DELIMITER //

DROP PROCEDURE IF EXISTS add_test //
CREATE PROCEDURE add_test (new_name VARCHAR(255))
DETERMINISTIC
BEGIN 
	UPDATE test_tbl SET name=new_name WHERE id=1;
END //

DROP PROCEDURE IF EXISTS add_test_prepared1 //
CREATE PROCEDURE add_test_prepared1 (new_name VARCHAR(255))
DETERMINISTIC
BEGIN 
	
	SET @query = CONCAT('UPDATE test_tbl SET name=', QUOTE(new_name), ' WHERE id=2');
	PREPARE new_query FROM @query;
	EXECUTE new_query;
END //

DROP PROCEDURE IF EXISTS add_test_prepared2 //
CREATE PROCEDURE add_test_prepared2 (new_name VARCHAR(255))
DETERMINISTIC
BEGIN 
	DECLARE new_id INT DEFAULT 0;
	
	SELECT id INTO new_id FROM test_tbl WHERE id=3;
	
	SET @query = CONCAT('UPDATE test_tbl SET name=', QUOTE(new_name), ' WHERE id=', new_id);
	PREPARE new_query FROM @query;
	EXECUTE new_query;
END //

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CALL add_test("Straße1");
Query OK, 1 row affected (0.01 sec)

mysql> CALL add_test_prepared1("Straße2");
Query OK, 1 row affected (0.00 sec)

mysql> CALL add_test_prepared2("Straße3");
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test_tbl;
+----+------------+
| id | name       |
+----+------------+
|  1 | Straße1   |
|  2 | Straße2   |
|  3 | Straße3 |
+----+------------+
3 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_bin        |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql>  SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| 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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql>

Note: The console also has UTF8 set as character set.

Suggested fix:
This problem was not on previous mysql versions ( 5.0.66a-standard )
[6 Feb 2009 18:49] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior.

Please check if you experience same behavior like in bug #26905: i.e. created procedure when default character set for the database was not UTF8, then altered it.

Also please indicate if database and stored procedures were created before you upgraded MySQL.
[7 Feb 2009 9:16] Anca Dogaru
I added all 3 procedures from file using command

mysql -uusernmae -ppass dbname < procedures_file.sql

Procedures were not created on a previous version of mysql, they were created on 5.1.30

I experience this problem on many operating systems with this mysql version.
[7 Feb 2009 9:26] Anca Dogaru
I tried to add procedures with

# mysql -u -p --default-character-set utf8 testdb < demotst.sql

and they seem to work this way.

But it still doesn't make sense why add_test_prepared1 and  add_test_prepared2 should act different, just because in   add_test_prepared2 after concatenating with an INTEGER the character set changes. 

This problem was not in previous versions
[12 Feb 2009 17:30] Sveta Smirnova
Thank you for the feedback.

Closing as "Not a Bug", because procedures should be created in proper way.

Regarding to the last comment please start mysql with option --column-type-info and see difference between types of 2 concatenated strings:

mysql> select CONCAT('UPDATE test_tbl SET name=', QUOTE('foo'),  ' WHERE id=', 3);
Field   1:  `CONCAT('UPDATE test_tbl SET name=', QUOTE('foo'),  ' WHERE id=', 3)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     42
Max_length: 41
Decimals:   31
Flags:      NOT_NULL BINARY 

+---------------------------------------------------------------------+
| CONCAT('UPDATE test_tbl SET name=', QUOTE('foo'),  ' WHERE id=', 3) |
+---------------------------------------------------------------------+
| UPDATE test_tbl SET name='foo' WHERE id=3                           | 
+---------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select CONCAT('UPDATE test_tbl SET name=', QUOTE('foo'),  ' WHERE id=2');
Field   1:  `CONCAT('UPDATE test_tbl SET name=', QUOTE('foo'),  ' WHERE id=2')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     126
Max_length: 41
Decimals:   31
Flags:      NOT_NULL 

+-------------------------------------------------------------------+
| CONCAT('UPDATE test_tbl SET name=', QUOTE('foo'),  ' WHERE id=2') |
+-------------------------------------------------------------------+
| UPDATE test_tbl SET name='foo' WHERE id=2                         | 
+-------------------------------------------------------------------+
1 row in set (0.00 sec)