Bug #68358 Coolation gets reste to default
Submitted: 13 Feb 2013 9:59 Modified: 15 Feb 2013 18:22
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.5.30 - likely any 4.1+ OS:Any ((I guess))
Assigned to: CPU Architecture:Any

[13 Feb 2013 9:59] Peter Laursen
Description:
Why reset collation to default on multiple SET NAMES statement like

SET NAMES 'utf8' COLLATE 'utf8_polish_ci';
SET NAMES 'utf8';

SHOW VARIABLES LIKE 'collation_connection'; -- returns 'utf8_general_ci'

How to repeat:
SET NAMES 'utf8' COLLATE 'utf8_polish_ci';
SET NAMES 'utf8';

SHOW VARIABLES LIKE 'collation_connection'; -- returns 'utf8_general_ci'

Suggested fix:
A SET NAMES statements with no COLLATE clause should not change 'collation_connection' it it already was a collation belonging to current charset, I think

I don't know (or even care) if current behavior is documented or not.  In either case it seems wrong to me in particular in cases where default collation for a server is a non-default collation for a given charset. I did not specify a collation change but collation changed anyway.
[13 Feb 2013 10:37] Hartmut Holzgraefe
I'd actually say "expected behavior" as SET NAMES is supposed to set a sane state, and that would mean to me that after any SET NAMES charset and collation settings are always getting the same values no matter what the previous state was
[13 Feb 2013 10:50] Peter Laursen
So localized server settings are *insane*? :-)
[15 Feb 2013 18:22] Sveta Smirnova
Thank you for the report.

This behavior is consistent in all places where character sets and collations are used. For explanation see http://dev.mysql.com/doc/refman/5.5/en/charset-column.html:

 If both CHARACTER SET X and COLLATE Y are specified, character set X and collation Y are used.

CREATE TABLE t1
(
    col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci
) CHARACTER SET latin1 COLLATE latin1_bin;

The character set and collation are specified for the column, so they are used. The column has character set utf8 and collation utf8_unicode_ci.

If CHARACTER SET X is specified without COLLATE, character set X and its default collation are used.

CREATE TABLE t1
(
    col1 CHAR(10) CHARACTER SET utf8
) CHARACTER SET latin1 COLLATE latin1_bin;

The character set is specified for the column, but the collation is not. The column has character set utf8 and the default collation for utf8, which is utf8_general_ci. To see the default collation for each character set, use the SHOW COLLATION statement. 

This is not a bug.