Bug #51241 Field collation metadata not correct in 5.1
Submitted: 17 Feb 2010 12:25 Modified: 18 Feb 2010 8:14
Reporter: Tonci Grgin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.1.31, 5.1.43 OS:Any
Assigned to: CPU Architecture:Any

[17 Feb 2010 12:25] Tonci Grgin
Description:
create table bug51206 (id integer NOT NULL AUTO_INCREMENT PRIMARY KEY, VCCol VARCHAR(30)) DEFAULT CHARSET UTF8;

mysql> SELECT * FROM bug51206;

Field   2:  `VCCol`
Catalog:    `def`
Database:   `test2`
Table:      `bug51206`
Org_table:  `bug51206`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)  <<<<<<<<
Length:     30
Max_length: 0
Decimals:   0
Flags:

How to repeat:
Environment 1:
  o Remote MySQL server 5.1.31 on OpenSolaris x64, started with --default-charset=utf8
  o Cl. console both from Windows and from OpenSolaris, same results
  o SET NAMES utf8; also makes no difference
  o create database test DEFAULT CHARACTER SET utf8
  o drop table if exists bug51206
  o mysql> create table bug51206 (id integer NOT NULL AUTO_INCREMENT PRIMARY KEY, VCCol VARCHAR(30)) DEFAULT CHARSET UTF8;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM bug51206;
Field   1:  `id`
Catalog:    `def`
Database:   `test2`
Table:      `bug51206`
Org_table:  `bug51206`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

Field   2:  `VCCol`
Catalog:    `def`
Database:   `test2`
Table:      `bug51206`
Org_table:  `bug51206`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8) <<<<<
Length:     30
Max_length: 0
Decimals:   0
Flags:

5.0.90 on windoze localhost behaves correctly:
Server version: 5.0.90-enterprise-gpl-nt MySQL Enterprise Server - Pro Edition

mysql> select * from bug51206;
Field   2:  `VCCol`
Catalog:    `def`
Database:   `test`
Table:      `bug51206`
Org_table:  `bug51206`
Type:       VAR_STRING
Collation:  utf8_general_ci (33) <<<<<<
Length:     90
Max_length: 14
Decimals:   0
Flags:

However, there is a small matter of *bytes* and not characters returned but that's another story.

Environment 2:
  o MySQL server 5.1.43 on windows localhost - everything's the same.

Suggested fix:
This is a followup on Bug#51206
[17 Feb 2010 21:27] Alexander Barkov
This is not a bug.

The "Collation" metadata column displays what you get on the client side
after processing by client-server character set conversion,
according to @@character_set_results.

If you need the original column collation, i.e. switch off client-server
conversion,  you need to do:

SET @@character_set_results=NULL;

This is expected behaviour, I suggest to close as Not a bug.
[18 Feb 2010 8:14] Tonci Grgin
Verily, Bar is right as always. I presumed ini files to be the same but they are not.

set character_set_results=NULL; solves the problem and is probably the reason why connectors work cause they set it this way by default.

Thanks Bar.