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: | |
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
[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)