MySQL Bugs: #73468: Append into date field all dates 0000-00-00 00:00:00

Bug #73468	Append into date field all dates 0000-00-00 00:00:00
Submitted:	5 Aug 2014 6:33	Modified:	9 Oct 2014 5:21
Reporter:	Rod March	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S1 (Critical)
Version:	1.2.1	OS:	Windows (Windows 8)
Assigned to:		CPU Architecture:	Any

Description:
I just upgraded from MySQL for Excel 1.1 to 1.2.1 on a new Windows PC, and I'm having trouble with the Append Excel Data to Table functionality. Using 1.2.1, all my dates are imported as 000-00-00 00:00:00. I did a test of the exact same spreadsheet and database using MySQL for Excel 1.1 (on a Windows 7 machine), where dates are imported correctly. I then used the same spreadsheet and database but with MySQL for Excel 1.2.1 (on my new Windows 8 PC), and all the dates were imported as 000-00-00 00:00:00.

With 1.2.1, I tried formatting the date differently in Excel, including both the yyyy-mm-dd and yyyy-mm-dd hh:mm:ss formats. I also tried using text strings in Excel instead of date-formatted values. No matter what I did, I got 0000 values in my database when I used 1.2.1.

My "old" PC:
- Windows 7
- Excel 2010
- WAMPServer 2.4
- MySQL for Excel 1.1

My "new" PC:
- Windows 8
- Excel 2010
- WAMPServer 2.4
- MySQL for Excel 1.2.1 (from mysql-installer-web-community-5.6.19.0.msi)

How to repeat:
Create a simple MySQL table with three fields: an ID field, an integer field and a date field. In Excel, create two columns with a list of integers in the first column and date values in the second, formatted in the Excel default mm/dd/yyyy (or in the MySQL-style yyyy-mm-dd). Using MySQL for Excel 1.1, appending these two columns works correctly, and the date is interpreted in the yyyy-mm-dd mySQL format. Using MySQL for Excel 1.2.1, the dates in the date column are all converted to 0000:00:00 values in the database table.

Suggested fix:
Although I've been wandering the app and the support pages for some time, I'm still hoping there is some setting that I've missed that will make this work. Until I find a workaround, I cannot use mySQL for Excel 1.2.1 to append data to my database.

Please provide the dump file (create table command, data) and the MySQL server version (not the third version mentioned). Thanks.

Hi! Thanks for the quick reply. My MySQL version is 5.6.12. The dump file is over 100MB of confidential data, so I can't send it. Would you like me to create a 2-field test database? 

Meanwhile, is there any way for me to run the MySQL for Excel 1.1 version so I can continue working while we figure this out? I'm stuck, and haven't found any place to get old versions.

Thanks, Rod

Hi again, I've done some more testing and have an update to the characteristics of the bug. I created a test DB and at first everything worked with the Append functionality. Then I carefully adjusted the table structure for the test table to exactly match the table where I was having trouble. 

I found that after I added an initial field and made it the autoincrement key field, that's what triggered the problem. Until I added that field, the date field appends went through beautifully, but once I added that field and its attributes (autoincrement, only key field), then I got all 0000's with my date appends. 

I hope this helps. I'll try to use the "Files" tab to add a zip with my database dump and excel file. Let me know if you get it. 

Thanks again, Rod

Test DB and test Excel spreadsheet

Attachment: mysql-bug-id73468-TestDB.zip (application/x-zip-compressed, text), 7.57 KiB.

Hi again,

Any chance I could get instructions to download/run the older version (MySQL for Windows version 1.1)? I know this version works, and I'd sure like to get back to my data!

Thanks, Rod

Verified the bug still happens in the current released version (1.3.1).

Thanks for the bug report Rod, the issue was already verified so the bug will be scheduled to be fixed.

You asked about going back to a previous version, unfortunately we do not maintain a repository of previous versions of this product. You can check if in the old computer you can find the MySQL for Excel 1.1 in the MySQL Installer's product cache found at "C:\ProgramData\MySQL\MySQL Installer\Product Cache".

Sorry for the inconvenience.

Posted by developer:
 
Fixed with the code changes done for Bugs 19402572 and 73175.

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

Appended "DATETIME" fields would change to 000-00-00 00:00:00 if the table
also had an auto-increment field present.

Thank you for the bug report.