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

[7 May 2022 22:25] dave wilson
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 2022 22:28] dave wilson
error message

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

[7 May 2022 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 2022 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.

Ashwini Patil
[11 May 2022 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 2022 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 2022 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 2022 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 2022 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 2022 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 2022 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 2022 0:40] dave wilson
Stephen - thanks, yes it is x86 architecture...so looks to happen both on 32 and 64 bit versions.
[1 Jun 2022 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 2022 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 2022 7:16] Zoltán Szalai
@  Gregg Wonderly: file dsn data source configuration --> details --> Misc --> "Disable Date Overflow error" checkbox.
[17 Jun 2022 6:27] MySQL Verification Team

Thank you for the feedback.
Verified as described.

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

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

[17 Jun 2022 6:36] MySQL Verification Team
Bug #107439 marked as duplicate of this one.
[2 Jul 2022 12:13] Louis at oracle
I have the same problem. I hope to see it solved soon!
[4 Jul 2022 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 2022 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.
[4 Nov 2022 6:53] Bogdan Degtyariov
Posted by developer:
The problem with Access occurred because MySQL ODBC driver reported the wrong value for DATA_TYPE in SQLColumns() call.
The DATA_TYPE in the result set produced by SQLColumns() should be a concise type, not a verbose type.

The description of type identifiers explaining the difference between verbose and concise type identifiers:

The correct concise type ID for DATETIME column in MySQL table would be SQL_TYPE_TIMESTAMP whilst the verbose type ID is SQL_DATETIME.
This caused the confusion to Access, which used the wrong parameter type (SQL_TYPE_DATE) instead of (SQL_TYPE_TIMESTAMP).

This is fixed now. The patch is pushed into the source tree.
[8 Nov 2022 6:01] MySQL Verification Team
Bug #109021 marked as duplicate of this one.
[5 Dec 2022 22:31] Philip Olson
Posted by developer:
Fixed as of the upcoming MySQL Connector/ODBC 8.0.32 release, and here's the proposed changelog entry from the documentation team:

The MySQL ODBC driver would report the incorrect DATA_TYPE value for
DATETIME when calling SQLColumns(). The correct concise type for DATETIME
is SQL_TYPE_TIMESTAMP, and this is now used instead of the verbose type

Thank you for the bug report.
[10 May 2023 20:27] Rob Sedgwick
Worked for me but I have to do a refresh link on the table in Access