| Bug #25212 | Character set of string constant is ignored | ||
|---|---|---|---|
| Submitted: | 20 Dec 2006 11:52 | Modified: | 11 Jul 2007 19:52 |
| Reporter: | Alexander Nozdrin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | Any |
| Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
| Tags: | rt_q1_2007 | ||
[20 Dec 2006 18:24]
Alexander Nozdrin
See also BUG#25221 (the same problem in triggers).
[2 Jul 2007 15:06]
Bugs System
Pushed into 5.1.21-beta
[11 Jul 2007 19:52]
Paul DuBois
Noted in 5.1.21 changelog. (Description applies to Bug#11986, Bug#16291, Bug#19443, Bug#21249, Bug#25212, Bug#25221) Several issues were identified for stored programs containing non-ASCII symbols. These issues involved conversion errors due to incomplete character set information when translating these objects to and from stored format, such as: * Parsing the original object definition so that it can be stored. * Compiling the stored definition into executable form when the object is invoked. * Retrieval of object definitions from INFORMATION_SCHEMA tables. * Displaying the object definition in SHOW statements. This issue also affected mysqldump, which uses SHOW. The fix for the problems is to store character set information from the object creation context so that this information is available when the object needs to be used later. The context includes the client character set, the connection character set and collation, and the collation of the database with which the object is associated. As a result of the patch, several tables have new columns: * In the mysql database, the proc and event tables now have these columns: character_set_client, collation_connection, db_collation, body_utf8. * In INFORMATION_SCHEMA, the VIEWS table now has these columns: CHARACTER_SET_CLIENT, COLLATION_CONNECTION. The ROUTINES, TRIGGERS, and EVENT tables now have these columns: CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION. These columns store the session values of the character_set_client and collation_connection system variables, and the collation of the database with which the object is associated. The values are those in effect at object creation time. (The saved database collation is not the value of the collation_database system variable, which applies to the default database; the database that contains the object is not necessarily the default database.) Several SHOW statements now display additional columns corresponding to the new table columns. These statements are: SHOW CREATE EVENT, SHOW CREATE FUNCTION, SHOW CREATE PROCEDURE, SHOW CREATE VIEW, SHOW EVENTS, SHOW FUNCTION STATUS, SHOW PROCEDURE STATUS, SHOW TRIGGERS.
Description: Character set and collation of string literals used in stored routines should be fixed at compilation time. Now they depend on actual character set / collation of user connection. How to repeat: 1. Create stored routine, that contains string literal and outputs character set and collation of it; 2. Connect to the server, set connection character set to UTF8, invoke stored routine to initiate its compilation. Character set will be UTF8. 3. Connect to the server, set connection character set to KOI8-R, invoke stored routine to initiate its compilation. Character set will be KOI8-R. # # 1. Create store routine. # > ./mysql -u root db1 mysql> delimiter | mysql> create procedure p1() -> begin -> select charset('string constant'); -> select collation('string constant'); -> end| Query OK, 0 rows affected (0.01 sec) # # 2. Execute (compile) when connection character set is UTF8. # > ./mysql -u root db1 mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> call p1(); +----------------------------+ | charset('string constant') | +----------------------------+ | utf8 | +----------------------------+ 1 row in set (0.00 sec) +------------------------------+ | collation('string constant') | +------------------------------+ | utf8_general_ci | +------------------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) # # 3. Execute (compile) when connection character set is KOI8-R. # > ./mysql -u root db1 mysql> set names koi8r; Query OK, 0 rows affected (0.00 sec) mysql> call p1(); +----------------------------+ | charset('string constant') | +----------------------------+ | koi8r | +----------------------------+ 1 row in set (0.01 sec) +------------------------------+ | collation('string constant') | +------------------------------+ | koi8r_general_ci | +------------------------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)