troubleshooting Question

Access 2016 linked to MySQL, ODBC call failed randomly

Avatar of wxitguy
wxitguyFlag for United States of America asked on
DatabasesMicrosoft AccessMySQL Server
5 Comments2 Solutions3265 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
wxitguy
Director of IT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 2 Answers and 5 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 2 Answers and 5 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004