MySQL Bugs: #4869: MS Access/ODBC: Lost connection to MySQL server during query

Bug #4869	MS Access/ODBC: Lost connection to MySQL server during query
Submitted:	3 Aug 2004 13:14	Modified:	24 Mar 2010 15:13
Reporter:	Gavin Crossley	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
I am using MS Access 2003 to connect to several MySQL databases via ODBC. The connections are very unstable and frequently produce this error:

ODBC--call failed.

[MySQL][ODBC 3.51 Driver][mysqld-3.23.49-log]Lost connection to MySQL server during query (#2013) 

Access will sometimes cope with this error, but typically hangs after three or four occurences. Can you please advise what might be wrong?

How to repeat:
The problem is intermittent, so it's difficult to reproduce. Just create a link table in Access to a MySQL database using the ODBC driver. Try and open the table: sometimes it will work, sometimes it won't.

I have Access 2003 and I don't have that problem. It sounds like a timeout
issue, by default MySQL has on Windows 8 hours for connection timeout.
Please verify your environment regarding timeout, if you find another cause
for this please feel free for to re-open this bug report.

Thanks

I have done some further testing and identified that the problem only occurs when the ODBC database in question has not been accessed in the last 60 seconds. It seems that Access expects it to be there, but it isn't.

Does the ODBC driver manager perform some kind of caching that might confuse Access when it makes a request or do you think this is an Access issue?

I am having the exact same problem.  Access Database using MySQL ODBC 3.51.  Get the same error message.  Hit OK and try again and it usually works.  Sometimes it doesn't.  Sometimes Access crashes.  Has to be something on the server or with the latest ODBC drivers.  Have been using this database for 3+ years with no issues.  Recently switched servers and updated the ODBC drivers and now am having this issue.

I am having the same problem too, using Access 2002. I frequently get both:

ODBC --call failed. [MySQL][ODBC 3.51 Driver][mysqld-4.1.14]Lost connection to MySQL server during query (#2013)

AND

ODBC --call failed. [MySQL][ODBC 3.51 Driver][mysqld-4.1.14]MySQL server has gone away (#2006)

I get these errors more often than not, and its pretty much not usable...

I logged the first request for help on this issue. I was unable to find a solution and in the end had to develop a work around. I wrote some code to drop the link tables and restablish them each time I requested data from the ODBC databases. Not elegent, but effective. There are two routines: one to drop the tables, another to reestablish them. If you want to borrow the code, here it is:

'Delete the link tables to avoid the timeout issue
Function RemoveLinkTables()
Dim TableList, TableName
On Error Resume Next
TableList = Array("link_table_1", "link_table_2", "link_table_3")
For Each TableName In TableList
    CurrentDb.TableDefs.Delete TableName
Next

End Function

Function CreateLinkTables2()

   Dim Mytdf As TableDef, MyLocal As String, MySource As String, MyConnectStr As String
    
    'Define the ODBC connect strings
    MyConnectStr = "ODBC;DSN=commerce;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=odbc_database_name;SERVER=odbc_server_name.com;UID=MyUserId;PASSWORD=MyPassword;PORT=3306;OPTION=131075;STMT=;"
        
    'Create the ODBC table
    'Repeat this for each table
    MySource = "odbc_table_name"
    MyLocal = "access_table_name"
    GoSub MakeItNow
    
Exit Function
    
MakeItNow:
    Set Mytdf = CurrentDb.CreateTableDef(MyLocal)
    Mytdf.Connect = MyConnectStr
    Mytdf.SourceTableName = MySource
    CurrentDb.TableDefs.Append Mytdf
    Return

End Function

Hi, I got the problem like this. I setup a MySQL server on my machine, the other machine run the client using ODBC to connect to the MySQL, It is OK. but, I run the cilent on my machine to make that the client and the server are on the same machine.It frequently produces the error:
[MySQL][ODBC 3.51 Driver][mysqld-5.0.15-nt]Lost connection to MySQL server during query.
It is true that it is unstable. The MySQL only let the other IP connecting normally, but self connecting always produce errors.

Hey Gavin,

I really need a solution to this problem... Your solution sounds fine to me..  I have everything setup.  

My question is:  Can you give me a step-by-step walkthrough to inserting your code into my Microsoft Access software.  

Hope to hear from you soon.
Thanks

--Ben

Hi Ben,

Sorry you've got the same problem, and also for being tardy in replying. I've been on holiday. To implement the solution I described above you'll need to know how to write VBA code in Access. Are you ok with that? If you are, here's what to do:

1. Create a new module and paste the two functions into it.
2. Edit the list of tables in the RemoveTablesNow function to match the ones you want to drop from your own database.
3. Edit the CreateLinkTables2 function:
   a. edit the ODBC connection settings for your environment
   b. repeat the three commands which call the subroutine for each table you want to reestablish, using your table names where appropriate.

    'Repeat this for each table
    MySource = "odbc_table_name"
    MyLocal = "access_table_name"
    GoSub MakeItNow

4. Now you just need to call these two functions from your own code whenever you need reliable access to the link tables, as follows:

   Call RemoveLinkTables()
   Call CreateLinkTables2()

That should do the trick mate.

Cheers,
Gavin

I've experience the error when I try to change the system date.  I do that because I want to see if my code works alright coz it checks for the system date so I change it...but instead it creates and error:
ODBC --call failed.  [MySQL][ODBC 3.51 Driver][mysqld-5.0.16-nt]MySQL server has gone away (#2006)....sometimes it works and often times it doesn't.  I will still have to wait for a period of time before I can use the connection and relink it again....

Why is this bug closed?
It still does not work!

tried the access code and it doesn't work, i still get the same error. also tried using the refreshlink method and that's not working either.

This can be fixed by adjusting the wait_timeout mysql variable on the server.  ODBC doesn't seem smart enough to reconnect, so you need to set the timeout high enough it doesn't happen often.

I had this problem and what I did was set a simple timer that made a request to the database every 30 to 45 sec no matter what, (Just has to be under 60sec).  This seemed to solve the problem.  I have a form that is alway in the background and i just attached the event to that form.  I open a simple query and then close it.  Problem solved

Excellent suggestion !.
I created a form that autoloads on opening access,
Added and event that was on a timer for every 30seconds, with docmd.requery
The form was tied to one small static refference table and simply showed the first line.
By requerying a tiny table it worked !

Thanks

Simon

I just wanted to say that the timer trick works, thanks. But this is hardly an "elegant" solution. The elegant thing would be for this bug not to exist at all...

As Miguel said, this is not easy to repeat although you might think it is. As for "I just wanted to say that the timer trick works, thanks. But this is hardly an elegant solution. The elegant thing would be for this bug not to exist at all..."
I am not convinced this is our bug at all and to write c/Access which would circumvent all shortcomings of Access is not in our plans.

On newer versions of c/ODBC you might use "Automatic reconnect" option though this will not help if one is using many long-running transactions (as they will be restarted after each reconnect).

And big thanks to Doug! This is what bugsdb is about, helping each other.

Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:satya.bn@sun.com-20091130085008-x83dn1al9wp7b5ar) (merge vers: 5.1.42) (pib:13)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:satya.bn@sun.com-20091202114649-zt975apdali0jy3c) (merge vers: 5.5.0-beta) (pib:14)

Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

Please disregard the commit messages : they're caused by a typo in the fix for bug #48469.

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

it was re-opened by mistake

I have experienced this - but can add something to the discussion: 
I am running the same Access front end against the same MySQL database from multiple locations. The locations using cable internet (Time Warner) never exhibit the problem. The locations using Verizon DSL exhibit the problem regardless of DSL/Modem/Router equipment.
Perhaps someone with telecom background could weigh in on this?

Wow, I tried with the code you made, and works great with MS Access 2010 and MySQL 5.6.26, now I only need some changes to adapt to my database and it's done. Thanks.