MySQL Bugs: #79989: "USE <db>" doesn't always change @@character_set

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)	Email Updates:
Status:	Verified	Impact on me:	None
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
Assigned to:		CPU Architecture:	Any
Tags:	character_set_database, Use

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>.

Hello Yura Sorokin,

Thank you for the report and test case.

Thanks,
Umesh

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).