MySQL Bugs: #71004: Only ONE table from a database won't import

Bug #71004	Only ONE table from a database won't import
Submitted:	25 Nov 2013 15:10	Modified:	11 Mar 2014 1:22
Reporter:	Jennifer Graham	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S3 (Non-critical)
Version:	1.1.3	OS:	Windows (Windows 8, MS Excel 2013)
Assigned to:		CPU Architecture:	Any

Description:
I have successfully connected to a database in MySQL for Excel. I can successfully import data using 'Import MySQL Data' for all tables in the database except for one table. There is only ONE table that will never import and I get the error: "A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond". I have increased the timeout to 200 seconds but this does not resolve the problem. This particular problem table only has 191 rows and 18 fields so it is not big at all.

How to repeat:
I could give temporary access to my database, which is the backend database to a website.

Suggested fix:
Figure out why this particular table is giving me problems.

Thank you for the bug report. Please provide the create table statement
for the referred table. Thanks.

Using the mysql.exe client do SHOW CREATE TABLE table_name and print here
the output i.e:

mysql> SHOW CREATE TABLE foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

'CREATE TABLE `figurine_customization` (\n  `figurine_id` int(11) NOT NULL AUTO_INCREMENT COMMENT \'Figurine Customization Id\',\n  `cart_id` int(11) NOT NULL COMMENT \'Cart Id for this Figurine\',\n  `cart_detail_id` int(11) NOT NULL COMMENT \'Cart Detail Line for this Figurine\',\n  `item_id` int(11) NOT NULL COMMENT \'Item Id for this record\',\n  `color_earring` int(11) NOT NULL DEFAULT \'0\' COMMENT \'Earring Color\',\n  `color_necklace` int(11) NOT NULL DEFAULT \'0\' COMMENT \'Necklace Color\',\n  `base` int(11) NOT NULL DEFAULT \'0\' COMMENT \'Base or No Base\',\n  `position` int(11) NOT NULL DEFAULT \'0\' COMMENT \'Figurine Position\',\n  `pet_name` varchar(20) NOT NULL DEFAULT \'\' COMMENT \'Pet Name\',\n  `order_id` int(11) NOT NULL,\n  `use_other_photos` tinyint(1) NOT NULL COMMENT \'Use Other Photos\',\n  `photo_1` varchar(125) DEFAULT NULL COMMENT \'Photo 1\',\n  `photo_2` varchar(125) DEFAULT NULL COMMENT \'Photo 2\',\n  `photo_3` varchar(125) DEFAULT NULL COMMENT \'Photo 3\',\n  `photo_4` varchar(125) DEFAULT NULL COMMENT \'Photo 4\',\n  `photo_5` varchar(125) DEFAULT NULL COMMENT \'Photo 5\',\n  `notes` varchar(255) NOT NULL COMMENT \'Additional Customer Notes\',\n  `base_personalization` varchar(255) CHARACTER SET utf16 NOT NULL,\n  PRIMARY KEY (`figurine_id`),\n  UNIQUE KEY `cart_id` (`order_id`,`cart_id`,`cart_detail_id`,`item_id`),\n  UNIQUE KEY `figurine_id` (`figurine_id`)\n) ENGINE=MyISAM AUTO_INCREMENT=197 DEFAULT CHARSET=utf8 COMMENT=\'Figurine Customizations\''

Also, here is the same thing for a table for which the import IS working. Some of the differences I see are the 1) NULL/NOT NULL presets, 2) auto increment number and 3) that UNIQUE appears twice in the figurine_customization table where figurine_id is both primary and unique. Maybe that's causing the error?

'CREATE TABLE `collar_customization` (\n  `collar_id` int(11) NOT NULL AUTO_INCREMENT COMMENT \'Collar Customization Id\',\n  `cart_id` int(11) NOT NULL DEFAULT \'0\' COMMENT \'Cart Id for this Collar\',\n  `cart_detail_id` int(11) NOT NULL DEFAULT \'0\' COMMENT \'Cart Detail Line for this Collar\',\n  `item_id` int(11) NOT NULL DEFAULT \'0\' COMMENT \'Item Id for this record\',\n  `collar_weight` int(11) NOT NULL DEFAULT \'0\' COMMENT \'Collar Weight\',\n  `collar_charm` int(11) NOT NULL DEFAULT \'0\' COMMENT \'Collar Charm\',\n  `collar_color` int(11) NOT NULL DEFAULT \'0\' COMMENT \'Collar Color\',\n  `collar_name` varchar(7) NOT NULL DEFAULT \'\' COMMENT \'Collar Name\',\n  `order_id` int(11) NOT NULL,\n  PRIMARY KEY (`collar_id`),\n  UNIQUE KEY `cart_id` (`order_id`,`cart_id`,`cart_detail_id`,`item_id`)\n) ENGINE=MyISAM AUTO_INCREMENT=911 DEFAULT CHARSET=utf8 COMMENT=\'Collar Customizations\''

Fail

Attachment: 0000_mysql_for_excel_import.png (image/png, text), 194.10 KiB.

Thank you for the feedback.

Posted by developer:
 
Connector .NET, an internal component to MySQL for Excel had a bug that occurred when working with Unique keys, that is what caused this problem. Now this will no longer happen.

Posted by developer:
 
fix confirmed with version 1.2.1 using the testdata found in the report.

Hello, I checked this today because the bug was marked as fixed. This problem is still happening. I even started a new spreadsheet and new connection to the database. I am able to import all tables except for the one table. It's the same error message. Please let me know if you need a screen shot or an actual connection to my database.

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

A MySQL database with unique keys would sometimes fail to import, when
executing "Import MySQL Data".

Thank you for the bug report.

Ah ok, thanks, I didn't realize it was for the next release