Bug #79989 "USE <db>" doesn't always change @@character_set_database to <db>'s charset

• Submitted: 14 Jan 2016 16:25
• Modified: 18 Jan 2016 12:31
• Reporter: Yura Sorokin (OCA)
• Status: Verified
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 5.5.47, 5.6.28, 5.7.10, 5.5.48, 5.6.29, 5.7.11
• OS: Any
• CPU Architecture: Any
• Tags: character_set_database, Use

[14 Jan 2016 16:25] Yura Sorokin

Description:
Provided that @@character_set_server is assigned a non-default value, after "USE <db>" statement, the value of @@character_set_database variable may not be set to <db>'s default charset.

This is in contrast to what the documentation says
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_d...

"... The server sets this variable whenever the default database changes..."

How to repeat:
Run the following code fragment in MTR environment

********************************
SELECT @@character_set_database;

SET character_set_server = ucs2;
CREATE DATABASE d1;
USE d1;
SELECT @@character_set_database;

DROP DATABASE d1;
USE test;
SELECT @@character_set_database;
********************************

Output:
********************************
SELECT @@character_set_database;
@@character_set_database
latin1
SET character_set_server = ucs2;
CREATE DATABASE d1;
USE d1;
SELECT @@character_set_database;
@@character_set_database
ucs2
DROP DATABASE d1;
USE test;
SELECT @@character_set_database;
@@character_set_database
ucs2
********************************

Suggested fix:
"USE <db>" statement must always set @@character_set_database (along with @@collation_database) to the corresponding values taken from <db>.

[15 Jan 2016 6:20] Umesh Shastry

Hello Yura Sorokin,

Thank you for the report and test case.

Thanks,
Umesh

[18 Jan 2016 12:31] Yura Sorokin

Just want to give another example which shows that users might encounter unexpected results when executing their statements because of this bug.

If the statement "SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmp.txt';" is executed before the code fragment in the bug description, 'tmp.txt' will be 6 bytes in size (written in latin1 encoding).

However, if the same statement is executed after that code fragment (bacause @@character_set_database has silently changed), 'tmp.txt' will be 10 bytes in size (written in ucs2 encoding).