Bug #78469 | MySQL for Excel 1.3.4 column mapping problem | ||
---|---|---|---|
Submitted: | 17 Sep 2015 12:11 | Modified: | 3 Feb 2016 20:59 |
Reporter: | Kjell Eikland | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL for Windows: MySQL for Excel | Severity: | S2 (Serious) |
Version: | 1.3.4, 1.3.5 | OS: | Windows (10 64 bit) |
Assigned to: | CPU Architecture: | Any |
[17 Sep 2015 12:11]
Kjell Eikland
[17 Sep 2015 17:27]
MySQL Verification Team
Please attach a screenshot of the issue. Thanks.
[17 Sep 2015 17:55]
Kjell Eikland
As requested, here is the screenshot. Hope it helps. I can also create an excerpt of the Excel file if you wish also. Best regards Kjell
[17 Sep 2015 19:59]
Kjell Eikland
Hello, Here is the table create statement also (with dummy foreign keys). I have verified that the issue exists with the Excel file sample sent a few minutes ago. Hope this will help. Kjell CREATE TABLE `contracts` ( `Site` varchar(30) NOT NULL, `Project` varchar(30) NOT NULL DEFAULT '', `Company` varchar(12) NOT NULL DEFAULT '', `CapMTPA` float unsigned NOT NULL, `Tolling` tinyint(1) unsigned DEFAULT NULL, `DES` varchar(30) DEFAULT NULL, `StartDate` date NOT NULL DEFAULT '0000-00-00', `Term` decimal(5,2) unsigned DEFAULT NULL, `EndDate` date DEFAULT NULL, `Phase` tinyint(2) unsigned DEFAULT NULL, `ContractId` tinyint(2) unsigned NOT NULL DEFAULT '1', `PriceAlpha` float DEFAULT NULL, `PriceBeta` float DEFAULT NULL, `PriceIndex` varchar(12) DEFAULT NULL, `Seller` varchar(12) NOT NULL DEFAULT 'ZZPROJECT', `Comments` varchar(100) DEFAULT NULL, PRIMARY KEY (`Site`,`Project`,`Company`,`StartDate`,`ContractId`,`Seller`), KEY `lngcontractsDES_idx` (`DES`,`Seller`), KEY `lngcontractsCompany_fkey_idx` (`Company`,`Site`,`Project`), KEY `lngcontractsSeller_idx` (`Seller`,`Company`,`Site`), CONSTRAINT `lngcontractsCompany_fkey` FOREIGN KEY (`Company`) REFERENCES `lngfirms` (`TICKER`) ON UPDATE CASCADE, CONSTRAINT `lngcontractsDES_fkey` FOREIGN KEY (`DES`) REFERENCES `t2` (`Site`) ON UPDATE CASCADE, CONSTRAINT `lngcontractsSeller_fkey` FOREIGN KEY (`Seller`) REFERENCES `t3` (`TICKER`) ON UPDATE CASCADE, CONSTRAINT `lngcontractsSite_fkey` FOREIGN KEY (`Site`) REFERENCES `t4` (`Site`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1;
[2 Nov 2015 11:18]
Chiranjeevi Battula
Hello Kjell Eikland, Thank you for your feedback and sample Excel file. Verified this behavior on MS Excel 2013. Thanks, Chiranjeevi
[2 Nov 2015 11:20]
Chiranjeevi Battula
screenshot.
Attachment: 78469.PNG (image/png, text), 88.64 KiB.
[2 Nov 2015 19:06]
Javier Treviño
Hi Kjell, Have you tried this in the latest released 1.3.5 version? I am pretty sure that you tested on version 1.3.4 and this was fixed in 1.3.5, can you please verify? Thanks.
[2 Nov 2015 19:07]
Javier Treviño
Chiran, The screenshot you attached shows the correct behavior of the automatic mapping. The product is mapping the columns on a 1:1 basis (1st - 1st, 2nd - 2nd, 3rd - 3rd, etc.) for columns that have "matching" data types. If you click on the "First column contains column names" in your specific test, you should notice all columns are mapped. You can see what I am explaining in our documentation: https://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-excel-append.html This bug should have not been set to a verified status.
[2 Nov 2015 21:36]
Kjell Eikland
Hi Javier, I installed v1.3.5 on Saturday and was not able to test a complete Excel-DB update cycle. However, I found that the automatic mapping now seems correct with the new version! It is definitely seems to be a big step forward, and it may actually have solved the issue. I expect to do a full-cycle test Tuesday and will revert with a status update then. Thanks. Best regards Kjell
[2 Nov 2015 23:11]
Javier Treviño
Sounds good Kjell, I will cancel this bug then. Unfortunately there is another known bug we introduced in 1.3.5 regarding boolean data types... it is creating a problem with both Export and Append Data operations. It is a different bug, that is why I will close this one. That other bug is already targeted for the next release of the add-in. Thanks.
[2 Nov 2015 23:14]
Javier Treviño
Posted by developer: Duplicate of already fixed bug 21799500.
[3 Nov 2015 9:02]
Kjell Eikland
Hi Javier, Did some further testing and can confirm that the bug with boolean values. Two further observations: 1) Columns are found to be varchar when the column is actually a series of empty values and integers. Automatic mapping is then not done for these "inconsistent" columns. While manual override seems to work, it does not "stick" when trying to save or use the mapping. 2) In addition, check-box activation of automatic mapping refuses to stick (check box untoggles by itself), but sticks when activating it via the dropbox. (Incidentally, why have both? Is there an additional relationship between the two I have not figured out?) Anyway, with these issues still open, I have not been able to successfully complete data export even with 1.3.5, so I am glad a new version may be around the corner. Best regards, Kjell
[4 Nov 2015 16:43]
Javier Treviño
Hi Kjell, The issue #2 you describe is caused by the boolean bug, which we already know about, so it will be fixed along. As for issue #1, I would need more specific steps, like for example, is the column with blanks and integers (treated as varchar) the first column of the source grid? Are you trying to map it automatically or manually? Against which type of column is being mapped? If you can consistently reproduce this #1 issue, it would greatly help if you raise a bug report for it, with steps to reproduce as specific as the ones you provided for this bug report, if you can provide also the script to create the target table and a sample .xls file it would be even better. Thanks.
[3 Feb 2016 20:59]
Javier Treviño
Duplicate of http://bugs.mysql.com/bug.php?id=76517