Search code examples
mysqldatetimems-accessodbcms-access-2003

ODBC call failed - Random date overflow errors after moving from 32-bit ODBC 5.1 to 64-bit 5.3

Background:

  • Legacy code running in MS Access 2003.
  • Sqls run by CurrentDB in Access.
  • Currently running on Windows 7 32-bit machine.
  • Connecting to MySQL Server 5.5 through a ODBC 5.1 Driver.

Problem:

  • Trying to migrate to Windows server 2012 64 bit.
  • ODBC 5.3 Unicode Driver (32 bit).
  • Don't want to use time rewriting everything as there is a lot of code, and it will in a not to distant future be removed.

Issue:

  • Several sql statements fail when running on the new servers. Worked on old servers.
  • All the failed sqls have now() in the statement.
  • Error description says ODBC call failed. While the more detailed description says date overflow - "[MySQL][ODBC 5.3(w) Driver][mysqlid-5.5.28-log]Date overflow".
  • Happens randomly, and when it happens and Access stops one can usually just choose continue and the sql will then work. It fails less than 1% of the times it runs (of thousands).
  • The only dates in the sql are in the where clause: "and fieldA > now()", where fieldA is a datetime column. This is a get recordset sql. Another error during an insert is the same, but where an integer was subtracted from now() before compared to a datetime.

I don't understand why it says date overflow when there doesn't seem to be a reason for the time of either datetime or "now" to be removed? And since the datetime field is already in the database and now() will get current date and time there shouldn't be any invalid dates?

Any help in what the problem might be or how to debug/log anything that might help would be highly appreciated.

Turning trace on in the ODBC driver is not an option because it happens so randomly, there is so much traffic therefore this would slow down everything such that nothing happens.

Note that I also did encounter one sql where the date overflow error message was correct. It seems that prior to 5.3 when inserting a datetime into a date field it was automatically truncated, because a sql which had been successfull 3000 times started failing. Therefore this sql has been fixed by extracting the date from the field first. But the other errors must be something different.

Solution

  • New version containing a bugfix has been released by Oracle: 5.3.8

    This error was a bug that seems to have been introduced in version 5.1.11.

    In advanced options there is now a Date overflow check box which has to be ticked for the code to continue when there is an error.

    Reply from Oracle about the fix: "For your information the fix approach was that in C or C++ it is possible to read or write DATE type using SQL_TIMESTAMP_STRUCT. This struct can hold both date and time. The error (Date overflow) was generated when with operations that are supposed to be DATE-only this struct got non-zero values for time. That is the canonical approach as ODBC API requires, however, it causes inconveniences sometimes when for instance the app did not bother to initialize the whole structure with 0 values because it knows it will only need the DATE part but the random values for TIME fraction could cause the errors despite of being truncated. A new option was introduced to continue with the query execution rather then return error. The server will ignore the TIME part and the result is the same as if there were zeroes."