Bug #107235 Date Overflow error with MS-Access on all 64-bit versions after 8.0.26
Submitted: 7 May 22:25 Modified: 1 Aug 16:51
Reporter: dave wilson Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:8.0.30 OS:Windows (10)
Assigned to: CPU Architecture:x86 (64-bit)
Tags: Connector / ODBC, date, Microsoft Access

[7 May 22:25] dave wilson
Description:
In any version after 8.0.26, Microsoft Access (Office 365 ver.) cannot update or add a row When linked to a mysql table containing a column of DateTime. It works fine in version up to 8.0.26, but stopped working after. 

Receive this error when try to Save the row with a date and time in the column:
[mysql][odbc 8.0(w) driver][mysqld-8.0.23]date overflow #0
    

How to repeat:
Using any version ODBC version after 8.0.26...Create a table in Mysql using datatype of DateTime. Link from Microsft Access to this table. Add a row in the table, and value the DateTime column with mm/dd/yyyy hh:mm:ssPM (05/07/2022 05:34:42PM). Save row, and should get this error.     

uninstall and re-Install 8.0.26 - try to add/edit row, and should work fine. 

Suggested fix:
revert back to version 8.0.26
[7 May 22:28] dave wilson
error message

Attachment: Screenshot 2022-05-07 182624.png (image/png, text), 12.02 KiB.

[7 May 22:29] dave wilson
if only put date in the DateTime column, it works fine. If add time, then gets the DateOverflow error.
[10 May 7:53] MySQL Verification Team
Hello dave wilson,

Thank you for the bug report.
I tried to reproduce your issue on windows 10 with Connector / ODBC 8.0.29 and followed exact steps but I am not seeing any issues at my end.

Regards,
Ashwini Patil
[11 May 17:57] Phil R
I am using 8.0.29 and MS Access 365.  I have set up a linked table which, among other fields, contains two datetime fields.  One is used only for date and one only for time (for sorting purposes).  I also receive the exact overflow error if I use the time field.  If I leave it blank, then the update occurs without error, albeit with a blank time field.

Additionally, I am using the 32-bit ODBC driver because I had a conflict with the default install of Office and the default driver install for MySQL.  I found it easier to install the 32-bit connector, rather than reinstall Office and change to 64-bit.
[12 May 15:29] Phil R
My problem may not have been the same.  Upon further inspection, the field used for time was named Time. Upon checking the general log, I found that the commands that Access parsed to the server did not include quotes or brackets.  I renamed the field and I no longer experience the problem.
[16 May 23:06] dave wilson
Thanks Phil - this would happen if i put BOTH the data and time into a DATETIme column with version 8.0.29. If i left out the time, it does not produce the error.
[16 May 23:14] dave wilson
thanks Ashwini - so you were able to update a datetime column (using both date and time) in mysql table linked to Access using the 64-bit odbc version 8.0.29? I cannot get it - only when i back out to 8.0.26, does it let me save. 
I'm using Office 365 64 bit, Windows 10 -
[17 May 16:29] Stephen Parry
Is the bug CPU architecture correct (ARM)? I am getting this on Intel x86
I am getting this exact same problem on Connector/ODBC 8.0.29 x86 32 bit ODBC, connectiong from Access 2019 32 bit:
Version 1808 (Build 10348.20020 Click-to-Run)
THis bug smells like another regression (possibly related to #84399?) - we only upgraded to 8.0.29 because of a regression in 8.0.28 (#106204 - not sure of the orginal bug).
Come on Oracle, you seem to be dropping the ball a lot lately with this one and I do not have the time to run round 40 workstations reverting back to some other untested version like 8.0.25.
I have also spent several hours today bashing my head against what I think are SSPS issues in the new driver.
[17 May 16:51] Stephen Parry
Plus IMHO this is at least an S2. Any handling of datetime between access and MySQL is affected. That seems pretty serious to me.
[17 May 17:01] Stephen Parry
Workaround: NO_DATE_OVERFLOW in the connection string, both stops the error and allows the time to be saved.
[19 May 0:40] dave wilson
Stephen - thanks, yes it is x86 architecture...so looks to happen both on 32 and 64 bit versions.
[1 Jun 7:30] Zoltán Szalai
Same here. Client: Windows 10 20H2 (build 19042.1706), MS Office 365 64 bit (16.0 build 14326), MySQL ODBC driver 8.0.29. 
Server: Windows, MySQL 5.7.38 64 bit community edition. Charset & collation: utf8, utf8_unicode_ci.  Target table columns with timestamp, datetime, and datetime with unused date or time part.

I have MS Access error 3146, "[MySQL][ODBC 8.0(w) Driver][mysqld-5.7.38-log]Date overflow (#0)". No errors with "NO_DATE_OVERFLOW=1" connection option. 
Other connection options used for testing: "charset=UTF8;option=541067322;".

Test results:
5.3.10 passes
8.0.26 passes
8.0.29 fails
8.0.29 + "NO_DATE_OVERFLOW=1" option passes.

Remark: the changelog doesn't say anything about behaviour changes in datetime handling, so it was unexpected.
[1 Jun 14:53] Gregg Wonderly
Where in the DSN setup, for Access users that are linking tables, is this option set at?  It seems like this should be the default if it is breaking things otherwise and let users who need the check add the option value =0 for their own use.
[2 Jun 7:16] Zoltán Szalai
@  Gregg Wonderly: file dsn data source configuration --> details --> Misc --> "Disable Date Overflow error" checkbox.
[17 Jun 6:27] MySQL Verification Team
Hello,

Thank you for the feedback.
Verified as described.

Regards,
Ashwini Patil
[17 Jun 6:31] MySQL Verification Team
8.0.29 test results

Attachment: 107235_test_results.PNG (image/png, text), 32.70 KiB.

[17 Jun 6:36] MySQL Verification Team
Bug #107439 marked as duplicate of this one.
[2 Jul 12:13] Louis at oracle
I have the same problem. I hope to see it solved soon!
[4 Jul 9:59] Louis at oracle
Oeps! Note that I also have this problem when trying to modify a record via the MySQL WorkBench. 

So it is definitively broader than described before!

I partly worked around the problem by defining a view on the table leaving out the date field .....
[1 Aug 16:51] dave wilson
i did note that versions after 8.0.26 did NOT produce date overflow error if use a timestamp data type, as opposed to the datetime data type.
 
Hope this can be resolved for version 31, as changing from datetime to timestamp for all our production financial tables would be ALOT of testing, breath holding,  and potential dual processing.