Bug #64468 Show Columns produces byte array, Describe does not.
Submitted: 27 Feb 2012 13:57 Modified: 28 Feb 2012 14:51
Reporter: Michael B Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.2.5.0 OS:Windows
Assigned to: CPU Architecture:Any

[27 Feb 2012 13:57] Michael B
Description:
When using connector.net 6.2.5.0 in visual studio 2005 with Mysql 4.1.13a the following query produces the unexpected result of a dataset filled with byte array[]. 

"show columns from mytable"

the following query produces the expected result of a dataset filled with casted datatypes such as datetime and string:

"describe mytable"

Since we are upgrading a project to the new connector we were wondering if this is by design or unexpected and might cause other queries to fill byte arrays instead of the casted type. Note that the "show columns from" did work as expected as "describe" in connector.net 1.0.4

How to repeat:
Install connector.net 6.2.5.0 in visual studio 2005.
Install Mysql server 4.1.13a

CREATE TABLE  `mytable` (
  `id` int(11) NOT NULL auto_increment,
  `address` varchar(105) default NULL,
  `housenr` varchar(25) default NULL,
  `housenr_suffix` varchar(15) default NULL,
  `box_nr` varchar(15) default NULL,
  `zipcode` varchar(15) default NULL,
  `city` varchar(45) default NULL,
  `country_id` int(11) default '0',
  `phone_private` varchar(45) default NULL,
  `phone_mobile` varchar(45) default NULL,
  `phone_work` varchar(45) default NULL,
  PRIMARY KEY  (`id`),
  KEY `Index_2` (`gender_id`),
  KEY `Index_3` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Run query: "show columns from mytable"
Run query: "describe mytable"

Suggested fix:
"show columns from mytable" returns typecasted dataset.
[27 Feb 2012 14:28] Valeriy Kravchuk
These are all ages old versions, and we do not fix bugs in old versions... We need to know if the problem is repeatable with server 5.0.91+ and Connector/Net 6.3.8+ at least.

Let's first check if the problem is in server. Please, run mysql command line client with -T option, like this:

macbook-pro:5.0 openxs$ bin/mysql -T -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.95-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> desc t1;
Field   1:  `Field`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     64
Max_length: 1
Decimals:   0
Flags:      NOT_NULL 

Field   2:  `Type`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     196605
Max_length: 7
Decimals:   0
Flags:      NOT_NULL BLOB 

Field   3:  `Null`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     3
Max_length: 3
Decimals:   0
Flags:      NOT_NULL 

Field   4:  `Key`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     3
Max_length: 0
Decimals:   0
Flags:      NOT_NULL 

Field   5:  `Default`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     196605
Max_length: 0
Decimals:   0
Flags:      BLOB 

Field   6:  `Extra`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     20
Max_length: 0
Decimals:   0
Flags:      NOT_NULL 

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> show columns from t1;
Field   1:  `Field`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     64
Max_length: 1
Decimals:   0
Flags:      NOT_NULL 

Field   2:  `Type`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     196605
Max_length: 7
Decimals:   0
Flags:      NOT_NULL BLOB 

Field   3:  `Null`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     3
Max_length: 3
Decimals:   0
Flags:      NOT_NULL 

Field   4:  `Key`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     3
Max_length: 0
Decimals:   0
Flags:      NOT_NULL 

Field   5:  `Default`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     196605
Max_length: 0
Decimals:   0
Flags:      BLOB 

Field   6:  `Extra`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     20
Max_length: 0
Decimals:   0
Flags:      NOT_NULL 

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

and check if you see any difference in column types for your table. If you do, this will be an indication of server problem.
[27 Feb 2012 15:26] Michael B
Unfortunately we can't upgrade to Mysql 5.0 right now and are therefore dependent on connector 6.2.5 (last version to support 4.1). If this is not something that can't be fixed or explained in that specific version then we will have to do some experiments to see if all the rest is working so we can or cannot upgrade to 6.2.5.

I was hoping there was still support since it seems to indicate in the manual (http://dev.mysql.com/doc/refman/5.6/en/connector-net-versions.html) however i completely understand your point. 

Thanks for the trouble.

FYI: the requested queries on the command line for Mysql 4.1.13 both returned the expected result.
[27 Feb 2012 16:32] Valeriy Kravchuk
OK, then we have a reason to suspect connector bug here.
[28 Feb 2012 14:51] Michael B
This bug can be closed. After more research and reading more in the documentation we found the flag Respect Binary Flags. Setting this to false as reported in the following document seems to be exactly what we needed.

http://dev.mysql.md/doc/refman/5.0/en/connector-net-programming-binary-issues.html 

My apologies and thanks for the fast response!