MySQL Bugs: #77265: Collation not being set correctly

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:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	collation

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

Please provide the output where shows the different collation for both tables. Thanks.

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

That issue happened still with newest release?. Thanks.

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