Bug #53758 .NET Connector
Submitted: 18 May 2010 18:49 Modified: 27 Jun 2010 18:28
Reporter: join u Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version: OS:Windows
Assigned to: CPU Architecture:Any

[18 May 2010 18:49] join u
Description:
Hi Guy sorry for bad english..
i have problens using mysql and .NET Connector.
Im using 2 syntax to retrive mysql data.

"SHOW TABLE STATUS"
and
"SHOW CREATE TABLE" TABLE_NAME

My Table name
bd-alteração-universo
using "SHOW TABLE STATUS" connetctor i get "bd-alteração-universo" its OK
using "SHOW CREATE TABLE" bd-alteração-universo i get 

"CREATE TABLE `bd-alteração-universo` (\n  `Auto` int(11) NOT NULL auto_increment,\n  `Retirar` varchar(50) default NULL,\n  `Região` varchar(255) default NULL,\n  `Campanha` varchar(255) default NULL,\n  `Produto` varchar(255) default NULL,\n  `Porte` varchar(50) default NULL,\n  `Universo` decimal(13,5) default NULL,\n  PRIMARY KEY  (`Auto`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1" ITS NOT OK

"SHOW CREATE TABLE" bd-alteração-universo need return its

CREATE TABLE `bd-alteração-universo` (
  `Auto` int(11) NOT NULL auto_increment,
  `Retirar` varchar(50) default NULL,
  `Região` varchar(255) default NULL,
  `Campanha` varchar(255) default NULL,
  `Produto` varchar(255) default NULL,
  `Porte` varchar(50) default NULL,
  `Universo` decimal(13,5) default NULL,
  PRIMARY KEY  (`Auto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

How to repeat:
Try to figuri it out.
A new projetc in c# "Visual Studio 2008" and create this table

CREATE TABLE `bd-alteração-universo` (
  `Auto` int(11) NOT NULL auto_increment,
  `Retirar` varchar(50) default NULL,
  `Região` varchar(255) default NULL,
  `Campanha` varchar(255) default NULL,
  `Produto` varchar(255) default NULL,
  `Porte` varchar(50) default NULL,
  `Universo` decimal(13,5) default NULL,
  PRIMARY KEY  (`Auto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now try to execute 

"SHOW CREATE TABLE bd-alteração-universo"
[19 May 2010 4:56] Susanne Ebrecht
Where do you do SHOW CREATE TABLE?

Which OS is used for storing the database/table?

Please paste output from:
SHOW VARIABLES LIKE '%char%';

Which encoding is used from your editor?

Please paste output from cmd for CHCP.
[19 May 2010 5:47] Tonci Grgin
This is what we need to see:
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Program Files\MySQL\MySQL Server 5.1\bin>chcp
Active code page: 437

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -p -hxx
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.31-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "%char%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /opt/mysql/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.03 sec)
[19 May 2010 15:05] join u
Im Using windows xp to debug and windows server 2003 to release.

MySqlCommand Comando = new MySqlCommand("SHOW TABLE STATUS", Conx);
...
...
LerTabelas = Comando.ExecuteReader();
while (LerTabelas.Read())
...
...
MySqlCommand Comando2 = new MySqlCommand("SHOW CREATE TABLE `" + LerTabelas.GetString(0) + "`", Conx2);
...
...
LerEstruturas = Comando2.ExecuteReader();
...
...
Buffer.WriteLine(LerEstruturas.GetString(1)+ ";\r\n");//here save wrong data

C:\Arquivos de programas\MySQL\MySQL Server 5.0>chcp
Página de códigos ativa: 850

mysql> show variables like "%char%";
+--------------------------+----------------------------------------------------
-------------+
| Variable_name            | Value
             |
+--------------------------+----------------------------------------------------
-------------+
| character_set_client     | latin1
             |
| character_set_connection | latin1
             |
| character_set_database   | latin1
             |
| character_set_filesystem | binary
             |
| character_set_results    | latin1
             |
| character_set_server     | latin1
             |
| character_set_system     | utf8
             |
| character_sets_dir       | C:\Arquivos de programas\MySQL\MySQL Server 5.0\sha
re\charsets\ |
+--------------------------+----------------------------------------------------
-------------+
8 rows in set (0.00 sec)
[21 May 2010 8:54] Susanne Ebrecht
Which encoding is your editor using?
[27 May 2010 15:04] join u
I Dont Know which encoding is mine editor using.
But See in the same Editor the first query return all fine and in the second dont return...
[27 May 2010 18:28] Sveta Smirnova
Thank you for the feedback.

Which exact version of MySQL server do you use?
[28 May 2010 6:52] Susanne Ebrecht
Which editor are you using?
[28 May 2010 6:56] Tonci Grgin
Join, I think this is actually a duplicate of Bug#10491 (at least a good part of it). Let me quote myself from that report:
 [20 Mar 2007 10:48] Tonci Grgin
This problem represents grave issue for connectors. They can't work around it, at least not reliably. Ad hoc user queries like this do not allow connector to distinguish between "SHOW CREATE TABLE", where it should treat BINARY as UTF8, and "SELECT varbinary_col FROM some_table", where it really should be binary...

So, besides what is already asked from you, we need a complete test case too, including the connection string (!), because you can ignore BINARY flag there. The Susanne can check if some of the characters are outside latin1 bmp and UTF8 should be used.

This also relates to Bug#25385 and Bug#31185.
[27 Jun 2010 23: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".