MySQL Bugs: #72434: Mysql for excel will not import table and gives vague error message

Bug #72434	Mysql for excel will not import table and gives vague error message
Submitted:	23 Apr 2014 22:57	Modified:	30 Jun 2014 18:39
Reporter:	Johnnie Birch	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S2 (Serious)
Version:	1.2.1	OS:	Windows
Assigned to:	Javier Treviño	CPU Architecture:	Any

Description:
After upgrading to Mysql for excel 1.2.1, mysql for excel fails to import a table and reports error "Input string was not in a correct format." This same table was populated and updated with the previous version of mysql for excel. Frankly, I updated all mysql products the installer was trying to upgrade so I can't be sure this issue starts with mysql for excel, but that is where the symptom is seen. 

The error message does not say enough for me to give better clues as to what is going on. I can say may table is a small one ... about 23 rows. The first column does accept varchar of length (200), 2 other columns have varchar of (100), another column uses date, and another uses an enum. This database has about 9 tables that I import and this is the only one that won't import and this started right after the upgrade. The varchar length and the enum are probably the only things unique to this table that I can't input.

How to repeat:
Here is an example row that will not enter:

'XXXXXXX-XXXXXXX-XXXX-3-XXXX-XXXXX-XX-4.3.0-1.8Ghz-XX-XXX-XXXXXXXXXXX-CCCCC-NA', 'XXXXXX-XX-4.2.0-1.7Ghz-XX', 'XXXXXX-XXXXX-4', 'SSSSSSSS'; 'XXX-XXXXXXXXXXXX', 2013, NULL, 'XXXXXX', NULL, NULL, 'XXXX-XX-XX 15:47:43', 'XXXXXXX@%'

FYI ... I can use a regular data connection(ODBC)to import this same table. Just can't do it with the upgrade for MySQL for Excel to 1.2.1

Please provide the output of show create table offended_table_name (private if
you wish). Thanks.

So I posted "show create table xxx". I hope you can see it ... I marked it as hide from public. Anyway ... I discovered the offending column is one that uses an enum  as a data type.

ENUM('ScoreA','ScoreB','ScoreC')

If I change the data type to VARCHAR, the data loads fine.

Johnnie

Thank you for the feedback. I couldn't repeat on my own, please provide 2/3
rows data insert sample so I can test on my side. Thanks.

Hi ... The row included in the initial description is a sample. I just substituted "X" in place of several letters. Like I said, as soon as I changed the data type from one of my columns from Enum to VARChar(45) everything worked fine. My guess would have been that the tool was having trouble all of a sudden parsing the syntax for the enum ... not sure.:

'XXXXXXX-XXXXXXX-XXXX-3-XXXX-XXXXX-XX-4.3.0-1.8Ghz-XX-XXX-XXXXXXXXXXX-CCCCC-NA', 'XXXXXX-XX-4.2.0-1.7Ghz-XX', 'XXXXXX-XXXXX-4', 'SSSSSSSS'; 'XXX-XXXXXXXXXXXX', 2013, NULL, 'XXXXXX', NULL, NULL, 'XXXX-XX-XX 15:47:43', 'XXXXXXX@%'

'YYYYYYY-XXXXXXX-XXXX-3-XXXX-XXXXX-XX-4.3.0-1.8Ghz-XX-XXX-XXXXXXXXXXX-CCCCC-NA', 'XXXXXX-XX-4.2.0-1.7Ghz-XX', 'XXXXXX-XXXXX-4', 'SSSSSSSS'; 'XXX-XXXXXXXXXXXX', 2013, NULL, 'XXXXXX', NULL, NULL, 'XXXX-XX-XX 15:47:43', 'XXXXXXX@%'

'ZZZZZZZ-XXXXXXX-XXXX-3-XXXX-XXXXX-XX-4.3.0-1.8Ghz-XX-XXX-XXXXXXXXXXX-CCCCC-NA', 'XXXXXX-XX-4.2.0-1.7Ghz-XX', 'XXXXXX-XXXXX-4', 'SSSSSSSS'; 'XXX-XXXXXXXXXXXX', 2013, NULL, 'XXXXXX', NULL, NULL, 'XXXX-XX-XX 15:47:43', 'XXXXXXX@%' 

Johnnie

As Johnnie pointed out, this has to do with enum and set columns, it is a valid bug.

Posted by developer:
 
Possible to be fixed along with the solution for the following bug, but won't flag it as a duplicate since it may not be a duplicate one:
http://clustra.no.oracle.com/orabugs/bug.php?id=18531026

Posted by developer:
 
Fixed this with the code fix of Bug #18962214 / MySQL Bug: 72970

Fixed as of the upcoming MySQL for Excel 1.3.1 release, and here's the changelog entry:

Attempting to import a table with ENUM values would fail with an "Input
string was not in a correct format." error.

Thank you for the bug report.