MySQL Bugs: #30603: Plugin doesn't fill a datatable with MySQL Server V4.1

Bug #30603	Plugin doesn't fill a datatable with MySQL Server V4.1
Submitted:	23 Aug 2007 17:07	Modified:	16 May 2008 13:14
Reporter:	Ivan Lowe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.1.4GA	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
I am using Visual Studio 2005 with the MySQL Connector/NET V5.1.2

The plugin works fine when interacting with MySQL Server versions 5.1 but with MySQL Server version 4.1.14 it is not working correctly.  Either I cannot create the TableAdapter or when the TableAdapter has been successfully created, the application freezes when applying the "Fill" function.

Neither of these occur when operating with identical data tables in MySQL servers V5.1

How to repeat:

Specifically:

If I drag a table adapter on to the dataset designer - I can proceed through the new adapter wizard:  selecting a connection, creating a SELECT query for a table up to where "Finish" is selected.  I then get a MsgBox with error:  Specified cast is not valid" and the table is removed from the designer.

If as an alternative I open the "Server Explorer" - navigate to the relevant table in the connection and drag that table onto the designer.  The tableadapter is created fine.  But when I run the application I find that the application freezes when the "Fill" function is called to load data into the dataset.

Suggested fix:
Can bypass by manually configuring the commands to call the MySQL client functions.

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.

I am having this same issue with 5.1.4.

Platform - Windows 2003 Server
DB version 4.1

c/NET 5.1.4GA + .NET FW 2 SP1, MySQL server 4.1.22 on WinXP Pro SP2 localhost:
1) Expand table 'blacklist' to show fields (works):
080229  9:32:55	      4 Query       SHOW TABLE STATUS FROM `test` LIKE 'blacklist'
		      4 Query       SHOW CREATE TABLE `test`.`blacklist`
		      4 Query       SHOW TABLE STATUS FROM `test` LIKE 'blacklist'
		      4 Query       SHOW CREATE TABLE `test`.`blacklist`
		      4 Query       SHOW COLUMNS FROM `blacklist` FROM `test`
		      4 Query       SELECT 'localhost' AS SERVER_NAME, NULL AS CATALOG_NAME, database() AS SCHEMA_NAME
--<cut>--
2) Double click 'blacklist' table (ERROR):
		      4 Query       SELECT 'localhost' AS SERVER_NAME, NULL AS CATALOG_NAME, database() AS SCHEMA_NAME
		      4 Query       SHOW TABLE STATUS FROM `test` LIKE 'blacklist'
		      4 Query       SHOW CREATE TABLE `test`.`blacklist`
		      4 Query       SHOW INDEX FROM `test`.`blacklist`
		      4 Query       SHOW CREATE TABLE `test`.`System.Byte[]` <<<
Error: 
 1)  "Incorrect table name 'System.Byte[]'"
 2)  "Failed to reload document data! Document will be closed."

3) Drag table 'blacklist' to project dataset:
Error:
 1) "Failed to merge object(s). Specified cast is not valid..."
--------
Open new C# project, add datasource, Choose DB odjects (tables only) -> Error:
"Failed to merge object(s). Specified cast is not valid..."
Explanation of error:
<`test`.`??ttest????????`>
Could not get column information for database object named '`test`.`??ttest????????`'

<`test`.`bug21770`>
Input string was not in a correct format.Couldn't store <'New'> in NUMERIC_PRECISION Column.  Expected type is Int64.

<`test`.`GROUND_STATION_ANTENNA`>
Input string was not in a correct format.Couldn't store <'DSN'> in NUMERIC_PRECISION Column.  Expected type is Int64.

Now, error on table `??ttest????????`, actually `žttestšđčć` can be ignored as it was made for multilingual test.
As for other two tables, it seems ENUM fields can't be processed:
| bug21770 | CREATE TABLE `bug21770` (
  `data_type_id` int(10) unsigned NOT NULL auto_increment,
  `char_type` varchar(30) default NULL,
  `varchar_type` varchar(30) default NULL,
  `tinyBlob_type` tinyblob,
  `blob_type` blob,
  `mediumBlob_type` mediumblob,
  `longBlob_type` longblob,
  `tinyText_type` tinytext,
  `text_type` text,
  `mediumText_type` mediumtext,
  `longText_type` longtext,
  `enum_type` enum('New','Used') NOT NULL default 'New',
  `set_type` set('a','b','c') default NULL,
  `tinyInt_type` tinyint(4) default NULL,
  `smallInt_type` smallint(5) unsigned default NULL,
  `mediumInt_type` mediumint(9) default NULL,
  `int_type` int(11) default NULL,
  `bigInt_type` bigint(20) default NULL,
  `float_type` float default NULL,
  `double_type` double default NULL,
  `decimal_type` decimal(16,2) default NULL,
  `date_type` date default NULL,
  `time_type` time default NULL,
  `dateTime_type` datetime default NULL,
  `timeStamp_type` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `year_type` year(4) default NULL,
  PRIMARY KEY  (`data_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

| GROUND_STATION_ANTENNA | CREATE TABLE `ground_station_antenna` (
  `ANTENNAID` int(11) NOT NULL default '0',
  `LOCATION` varchar(16) NOT NULL default '',
  `NETWORK` enum('DSN','GN') NOT NULL default 'DSN',
  PRIMARY KEY  (`ANTENNAID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

mysql> select * from GROUND_STATION_ANTENNA
+-----------+-----------+---------+
| ANTENNAID | LOCATION  | NETWORK |
+-----------+-----------+---------+
|         0 | McMurdo   | GN      |
|        24 | Goldstone | DSN     |
|        25 | Goldstone | DSN     |
|        26 | Goldstone | DSN     |
|        34 | Canberra  | DSN     |
|        54 | Madrid    | DSN     |
|        55 | Madrid    | DSN     |
+-----------+-----------+---------+
7 rows in set (0.00 sec)

In log one can see:
080229  9:55:59	      8 Connect     root@localhost on test
8 Query       SHOW VARIABLES
8 Query       SHOW COLLATION
8 Query       SET character_set_results=NULL
8 Init DB     test
8 Query       SELECT 'localhost' AS SERVER_NAME, NULL AS CATALOG_NAME, database() AS SCHEMA_NAME
080229  9:56:02	      8 Query       SHOW DATABASES
8 Query       SHOW TABLE STATUS FROM `mix_test_ucl`
8 Query       SHOW TABLE STATUS FROM `mixtestucl`
8 Query       SHOW TABLE STATUS FROM `test`
8 Query       SHOW TABLE STATUS FROM `test_ucl`
8 Query       SHOW TABLE STATUS FROM `testcl`
8 Query       SHOW TABLE STATUS FROM `testdb`
080229  9:56:07	      8 Query       SHOW DATABASES
8 Query       SHOW TABLE STATUS FROM `mix_test_ucl` LIKE 'GROUND_STATION_ANTENNA'
8 Query       SHOW TABLE STATUS FROM `mixtestucl` LIKE 'GROUND_STATION_ANTENNA'
8 Query       SHOW TABLE STATUS FROM `test` LIKE 'GROUND_STATION_ANTENNA'
8 Query       SHOW TABLE STATUS FROM `test_ucl` LIKE 'GROUND_STATION_ANTENNA'
8 Query       SHOW TABLE STATUS FROM `testcl` LIKE 'GROUND_STATION_ANTENNA'
8 Query       SHOW TABLE STATUS FROM `testdb` LIKE 'GROUND_STATION_ANTENNA'
8 Query       SHOW DATABASES

So I would say, "Verified" that 5.1.4GA VS plugin, when picking up certain fields from 4.1 server (ENUM in particular), fails... Same table in same environment but on MySQL server 5.0.58pb1083 is accepted and no error occurs. I am wondering about c/NET looking up table 'GROUND_STATION_ANTENNA' in all of databases matching pattern(!) of database I have put into connection string ('test'). This could represent separate bug.

Verified that c/NET 5.1.4 VS plugin has problems with 4.1.x server in:
 1) Picking up ENUM fields
 2) Searching for table in all databases that match "*database_name*" pattern instead in only database passed in connection string

A note to reporters. Guys, I could have tested this numerous times and it would never had failed. I had to take entire database with many tables in it to find out what went wrong. I presume you could have detected this problem just like I did and shorten the time of verification process.

Thanks for your interest in MySQL.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46486

Fixed in 5.1.7 and 5.2.3+

A note has been added to the 5.1.7 and 5.2.3 changelogs: 

Using the MySQL Visual Studio plugin and a MySQL 4.1 server, certain field types (ENUM) would not be identified correctly. Also, when looking for tables, the plugin would list all tables matching a wildcard pattern of the database name supplied in the connection string, instead of only tables within the specified database.