Bug #47833 2 byte Collation name not seen in column's meta-data
Submitted: 5 Oct 2009 11:04 Modified: 14 Dec 2009 11:14
Reporter: Nidhi Shrotriya Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:6.0, 5.5.0-beta OS:Linux (ubuntu)
Assigned to: Alexander Barkov CPU Architecture:Any
Tags: 5.5.0-beta, 6.0.14 alpha

[5 Oct 2009 11:04] Nidhi Shrotriya
Description:
Start 6.0 server with --character-sets-dir=$PWD/mysql-test/std_data to use
'Index.xml' file for 2 byte collation IDs. 

Start mysql client with --column-type-info enabled.

mysql> SHOW COLLATION LIKE 'ucs2_vn_ci';
+------------+---------+-----+---------+----------+---------+
| Collation  | Charset | Id  | Default | Compiled | Sortlen |
+------------+---------+-----+---------+----------+---------+
| ucs2_vn_ci | ucs2    | 359 |         |          |       8 |
+------------+---------+-----+---------+----------+---------+
1 row in set (0.00 sec)

mysql> create database db_collation_id;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE TABLE db_collation_id.tab_2byte_coll_id(c1 CHAR(1) CHARACTER SET ucs2 COLLATE ucs2_vn_ci);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO db_collation_id.tab_2byte_coll_id values (0x0061);
Query OK, 1 row affected (0.00 sec)

mysql> SET @@character_set_results=NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from db_collation_id.tab_2byte_coll_id;
Field   1:  `c1`
Catalog:    `def`
Database:   `db_collation_id`
Table:      `tab_2byte_coll_id`
Org_table:  `tab_2byte_coll_id`
Type:       STRING
Collation:  ? (359)
Length:     2
Max_length: 2
Decimals:   0
Flags:      

+------+
| c1   |
+------+
|  a   |
+------+
1 row in set (0.00 sec)

How to repeat:
As mentioned in the description.
[5 Oct 2009 13:32] MySQL Verification Team
Thank you for the bug report. Verified as described.
[24 Nov 2009 9:02] Nidhi Shrotriya
Present in betony (5.5.0-beta)
[14 Dec 2009 11:14] Alexander Barkov
This is not a bug. The priblem happens because your client
uses the standard character set directory.

You must run client with the same to server --character-sets-dir value
to be able to see the collation name. 

I checked:

mysql --column-type-info --character-sets-dir=$PWD/mysql-test/std_data test

and it works fine:

mysql> set @@character_set_results=null;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1;
Field   1:  `c1`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       STRING
Collation:  ucs2_vn_ci (359)
Length:     2
Max_length: 2
Decimals:   0
Flags:      

+------+
| c1   |
+------+
|  a   |
+------+
1 row in set (0.00 sec)