MySQL Bugs: #107235: Date Overflow error with MS-Access on all 64-bit versions after 8.0.26

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:	None
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

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

error message

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

if only put date in the DateTime column, it works fine. If add time, then gets the DateOverflow error.

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

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.

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.

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.

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 -

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.

Plus IMHO this is at least an S2. Any handling of datetime between access and MySQL is affected. That seems pretty serious to me.

Workaround: NO_DATE_OVERFLOW in the connection string, both stops the error and allows the time to be saved.

Stephen - thanks, yes it is x86 architecture...so looks to happen both on 32 and 64 bit versions.

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.

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.

@  Gregg Wonderly: file dsn data source configuration --> details --> Misc --> "Disable Date Overflow error" checkbox.

Hello,

Thank you for the feedback.
Verified as described.

Regards,
Ashwini Patil

8.0.29 test results

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

Bug #107439 marked as duplicate of this one.

I have the same problem. I hope to see it solved soon!

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 .....

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.

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:
https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/data-type-identifiers-and-...

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.

Bug #109021 marked as duplicate of this one.

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
SQL_DATETIME.

Thank you for the bug report.

Worked for me but I have to do a refresh link on the table in Access