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:
None 
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 11:52] Alexander Nozdrin
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)
[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.