Bug #72941 Unable to get collation from charsetnr of MYSQL_FIELD
Submitted: 10 Jun 2014 16:03 Modified: 15 Jul 2014 19:01
Reporter: Ashish Kumar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S2 (Serious)
Version:5.6.21, 5.5.39, 5.1.74 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[10 Jun 2014 16:03] Ashish Kumar
Description:
For getting collation of char/varchar/text columns , we tried using charsetnr field in MYSQL_FIELD structure on MySQL 5.6. This field should provide the collation id of that field ( column ).

But charsetnr always found to contain the collation value corresponding to either utf8 or utf8mb4 ( 33 or 45 respectively ). This is irrespective of any server charset / db charset / table charset / column charset. Sample example is given below.

1. create table a (   blah char(20) charset gbk ) ;
2. code to get value of charsetnr.
#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>

MYSQL conn;

int main(int argc,char *argv[]) 
{
        // run with      username port           as arguments
        char *tabname="a";
        char *db="test1";
        char *u;
        char *p;
        MYSQL_RES *result;
        MYSQL_FIELD *field;
        u=argv[1]; p=argv[2];
        mysql_init(&conn);
        if (!mysql_real_connect(&conn, NULL,u,p,db,0,NULL,0) );

        result = mysql_list_fields (&conn, tabname, NULL);

        field = mysql_fetch_field (result);
        printf("Field =%s character set=%d\n", field->name, field->charsetnr);
}
Here we always get a value of filed->charsetnr as 33 or 45 irrespective of server/db/table/column charset. We have tested it on MySQL 5.6.

But when I try query " show full columns from db.table like 'column name'" , it gives me the correct collation for that column which is the third column of the result set returned.

Can you please help in finding the correct collation using the mysql_list_fields() API as well ? We do not want to use the query to find out that. 

Thanks,
Ashish

How to repeat:
1. create table a (   blah char(20) charset gbk ) ;
2. code to get value of charsetnr.
#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>

MYSQL conn;

int main(int argc,char *argv[]) 
{
        // run with      username port           as arguments
        char *tabname="a";
        char *db="test1";
        char *u;
        char *p;
        MYSQL_RES *result;
        MYSQL_FIELD *field;
        u=argv[1]; p=argv[2];
        mysql_init(&conn);
        if (!mysql_real_connect(&conn, NULL,u,p,db,0,NULL,0) );

        result = mysql_list_fields (&conn, tabname, NULL);

        field = mysql_fetch_field (result);
        printf("Field =%s character set=%d\n", field->name, field->charsetnr);
}
Here we always get a value of filed->charsetnr as 33 or 45 irrespective of server/db/table/column charset. We have tested it on MySQL 5.6.
[15 Jul 2014 19:01] Sveta Smirnova
Thank you for the report.

Verified as described.