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:Microsoft Windows
Assigned to: Javier Treviño CPU Architecture:Any

[23 Apr 2014 22:57] Johnnie Birch
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@%'
[23 Apr 2014 23:54] Johnnie Birch
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
[24 Apr 2014 12:49] MySQL Verification Team
Please provide the output of show create table offended_table_name (private if
you wish). Thanks.
[25 Apr 2014 2:39] Johnnie Birch
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
[28 Apr 2014 15:46] MySQL Verification Team
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.
[1 May 2014 21:25] Johnnie Birch
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
[8 May 2014 23:40] Javier Treviño
As Johnnie pointed out, this has to do with enum and set columns, it is a valid bug.
[8 May 2014 23:44] Javier Treviño
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
[19 Jun 2014 23:33] Javier Treviño
Posted by developer:
 
Fixed this with the code fix of Bug #18962214 / MySQL Bug: 72970
[30 Jun 2014 18:39] Philip Olson
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.