Access 2016 linked to MySQL, ODBC call failed randomly

Hello,

Some background: We are using Microsoft Access as a front end, (2000 32-bit, to 2010 32-bit, and now 2016 64-bit over the last several years) and MySQL 5.5 as a backend on a Fedora Linux system. We are using MySQL ODBC driver 5.2.7. Our FE is very complex, our users would frequently receive System Resource Exceeded errors which is why we converted to 64-bit (which did resolve the system resource errors). As of about two weeks ago, the migration to 2016 64-bit was completed. We have at most 15 users accessing the database at the same time. Everyone has their own "local" copy of the front end DB on their own system.

In early January (before 2016 64-bit), we started receiving random ODBC call failed errors throughout the day from our users. Using the DAO.Errors collection, we were able to get the exact  error message which is this:

Error #0  [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.28-log]Date overflow (Source: ODBC.Database)
Error #3146 ODBC--call failed. (Source: DAO.Database)

These errors happen on several different SELECT queries that access different tables. Some of them are extremely complex queries, but some aren't. We are using error checking code now that emails us the error when it occurs, and then tries to resume the same query again. Usually, the 2nd time it tries the same query it will work with no error. Sometimes it will go 3 or 4 tries and then it will work.

I believe the only thing these queries have in common is that they all use some sort of date filtering using the Now() function, example might be having a datetime field with criteria "> Now() + 5" for 5 days from now ... thinking maybe there was a strange issue there, we wrapped all of those criteria with CDate() calls so now they look like "> CDate(Now() + 5)" ... which did not make a difference.

The errors also seem to occur in bunches ... we might get a handful around the same time (not exactly the same time) from a couple different computers each morning, then go several hours with none, then in the afternoon a few more. The times of days are similar but not exactly the same.

I've tried using ODBC tracing to see what exactly is failing on the Access side, but this is difficult to do because we can't duplicate the error on demand, and ODBC tracing is extremely slow (10+ minutes for a single complex query which without tracing runs in 1-2 seconds).

I also tried running the MySQL general query log and I can see when the error occurs, but it only shows the "prepare" statement which has "?" for date values, the "execute" statement with the final values in the SELECT query does not make it into the log.

I did try upgrading the ODBC driver to 5.3.4 but it made no difference.

I also tried a new MySQL server running newest version of Fedora Linux and MariaDB ... no difference.

We've been trying to fix this since it started occurring and tried many different fix attempts with no success. Google also doesn't seem to turn up anything related to this error except for one result (googled "Date Overflow (#0)" ) in Spanish which, when translated, doesn't help.

Looking for any assistance or insight, thank you.
wxitguyDirector of ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Looking for any assistance or insight, thank you.>>

 While not a direct answer, maybe some insight.

 The overflow simply means that your exceeding the data type in JET and my guess is that in some way, your problem is data driven.

 mySQL has had some bugs in this area as well:

https://bugs.mySQL.com/bug.php?id=68760

 a posting as late as Dec 2015 indicates this is still an unresolved issue.

 So first suggestion; make sure your using the appropriate data types and if possible, choose a larger/smaller one.

 if that's not possible, then I would do some rounding on the Access side to trim it down.

The other thing I would check is if your up to date on mySQL and the driver you using (really don't think it's a driver problem, but you never know).

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Also think about the use of any "dummy dates" by the users and/or software acting as a flag for something (i.e. year 9999).

Depending on the time attached to that, you might overflow.

Jim.
wxitguyDirector of ITAuthor Commented:
Thanks for your assistance. We did find some "dummy dates" being used in the code but they did not seem to affect the error when we fixed them.

We downgraded to Access 2013 64-bit and this seemed to resolve almost all of the ODBC errors. We still get them frequently from one system that runs scheduled tasks using ODBC, however our operations team sees the error maybe once/day or less.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wxitguyDirector of ITAuthor Commented:
Downgrading was not suggested, and it's not a perfect fix but did significantly reduce the occurrence of the error.
wxitguyDirector of ITAuthor Commented:
We eventually determined that this is a bug with MySQL ODBC driver and the version of MySQL we are using (5.5.28). Upgrading the MySQL server is not currently an option though was something we tried in the past (to MariaDB but I don't remember the specific version number) and the problem still occurred there too.

The only solution we have come up with is to downgrade the ODBC driver to 5.1.10 ... it seems like the problem starts occurring in 5.1.11. Here are direct links to the driver installers for future reference for anyone else struggling with this issue:

32 bit: https://dev.mysql.com/get/Downloads/Connector-ODBC/5.1/mysql-connector-odbc-5.1.10-win32.msi

64-bit: https://dev.mysql.com/get/Downloads/Connector-ODBC/5.1/mysql-connector-odbc-5.1.10-winx64.msi
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.