Bug #77265 Collation not being set correctly
Submitted: 6 Jun 2015 8:42 Modified: 19 Jul 2018 9:01
Reporter: Charles Loh Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.22 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: collation

[6 Jun 2015 8:42] Charles Loh
Description:
When the following statements are executed in the same database using the same connection, the resultant collation for the fields are different.

(A)
***Column collation shows utf8_general_ci
create table `table111` (`UserId` nvarchar(128)  not null ,`RoleId` nvarchar(128)  not null ,`IdentityUser_Id` nvarchar(128) ,
primary key ( `UserId`,`RoleId`) ) engine=InnoDb auto_increment=0

(B)
***Column collation shows utf8_unicode_ci
create table `table222` (`UserId` varchar(128)  not null ,`RoleId` varchar(128)  not null ,`IdentityUser_Id` varchar(128) ,
primary key ( `UserId`,`RoleId`) ) engine=InnoDb auto_increment=0

While the paragraph below is indicated in the documentation, whether the collation should respect the system setting is not clear.

https://dev.mysql.com/doc/refman/5.6/en/charset-national.html

Standard SQL defines NCHAR or NATIONAL CHAR as a way to indicate that a CHAR column should use some predefined character set. MySQL 5.6 uses utf8 as this predefined character set. For example, these data type declarations are equivalent: 

How to repeat:
1)Server configured to use charset=utf and collation=utf8_unicode_ci
2) Create the database

CREATE DATABASE `aspidentitydb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

2) Execute the 2 statements (A) & (B) above and compare the column collation.

Server Settings:
show variables like 'char%';
=========================
character_set_client	utf8
character_set_connection	utf8
character_set_database	utf8
character_set_filesystem	binary
character_set_results	utf8
character_set_server	utf8
character_set_system	utf8

show variables like 'char%';
=========================
collation_connection	utf8_unicode_ci
collation_database	utf8_unicode_ci
collation_server	utf8_unicode_ci
[10 Sep 2015 15:09] Miguel Solorzano
Please provide the output where shows the different collation for both tables. Thanks.
[14 Sep 2015 1:19] Charles Loh
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME
   FROM INFORMATION_SCHEMA.COLUMNS where table_schema='aspidentitydb'

TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME
aspidentitydb,table111,UserId,utf8_general_ci
aspidentitydb,table111,RoleId,utf8_general_ci
aspidentitydb,table111,IdentityUser_Id,utf8_general_ci
aspidentitydb,table222,UserId,utf8_unicode_ci
aspidentitydb,table222,RoleId,utf8_unicode_ci
aspidentitydb,table222,IdentityUser_Id,utf8_unicode_ci
[19 Jun 2018 9:01] Miguel Solorzano
That issue happened still with newest release?. Thanks.
[20 Jul 2018 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".