MySQL Bugs: #22385: Access 2002 linked tables stop working after client disconnect

Bug #22385	Access 2002 linked tables stop working after client disconnect
Submitted:	15 Sep 2006 10:33	Modified:	22 Sep 2006 10:16
Reporter:	Philip Stoev	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (Windows 2000 SP4)
Assigned to:		CPU Architecture:	Any

Description:
If the mysql database connection is terminated after a linked table has been created, the ODBC driver does not attempt to establish it again upon future accesses on the linked table. A "MySQL server is gone away" message is always displayed until Access is restarted.

The mysql server 5.0.16-standard-log, however this does not appear to matter.

How to repeat:
1. Create a mysql table
2. Open a new Access database and link the mysql table in it
3. Open the table from within Access in order to verify that it is accessible, and then close it;
4. Use the mysqladmin tool to kill the connection (thus simulating a crash and/or restart/reboot)
5. Try to open the table from within Access again. A "MySQL server has gone away" message is shown and all further efforts to connect fail
6. Restart Access and reopen the database. The table is now accessible and a new mysql connection will be made.

Suggested fix:
It appears that the ODBC driver does not even attempt to reestablish the connection after it fails and returns the error message immediately. Ethereal does not detect any network traffic from the Access host to the MySQL server before the message is popped up on the screen.

Hi Philip and thanks for your problem report. Reconnect problems are known to us and are addressed in latest version of connector/ODBC:
---------------------------------------------
25-AUG-2006 (3.51.14): pharvey@mysql.com
---------------------------------------------
-- Added support of HENV handlers in SQLEndTran(), updated tran
   test case
-- Added auto-reconnect option since default auto-reconnecting
   was disabled in mysqlclient library.

Can you please test with connector/ODBC 3.51.14 and get back to me with results?

Hello, unfortunately it did not work. I downloaded 3.51.14 from

ftp://ftp.mysql.com/pub/mysql/download/myodbc-3.51.14/mysql-connector-odbc-3.51.14-win32.m...

,rebooted, and enabled the "Enable Auto Recconect" for the data source (I think the option must be "on" by default), however I got the exactly the same behavoir as before. As before, no network traffic was observed and the message popped up without any processing or delay.

I also got some unspecified ODBC control panel crashes with the new version. I will open a separate bug for those if I am able to reproduce them consistently.

Thank you very much for your cooperation.

Philip, even though 3.51.14 is not GA (and will not be for a month or so) feel free to post bug reports. Before I try to recreate this problem I will need sme more info:
  o The MDAC version (if on Windows) (for JET engine and stuff)
    * Download verbose MDAC utility .EXE here
http://www.microsoft.com/downloads/info.aspx?na=46&p=9&SrcDisplayLang=en&SrcCate
goryId=&SrcFamilyId=cebf3c7c-7ca5-408f-88b7-f9c79b7306c0&u=http%3a%2f%2fdownload
.microsoft.com%2fdownload%2fb%2fb%2f1%2fbb139fcb-4aac-4fe5-a579-30b0bd915706%2fM
PSRPT_MDAC.EXE&oRef=http%3a%2f%2fmsdn.microsoft.com%2fdata%2fdownloads%2ftools%2
fdefault.aspx

MDAC information

Attachment: DAGMARA_MSADC_DLL.TXT (text/plain), 6.46 KiB.

Hello,

I have attached my MDAC report. Basically I have a Windows 2000 SP4 fully patched with Microsoft Office XP fully patched. The only special thing is that my Access kept on crashing due to bug #9932 so I installed an older msjet40.dll at some point (though right now I have no idea if Windows Update has against installed the most recent version).

Please let me know if you require any additional information or trace files.

Hi Philip. Please attach MS Jet engine versions too as it seems it's in the middle of this problem. 
As for "other drivers are working" stuff (there are several more reports like the one you mentioned), I've found that, for example, IBM had similar problems like we do. 
Now, on my fully updated Windows XP Pro, msjet40.dll is version 4.0.8618.0. I can't really know what's MS doing with "special" updates for w2k and w2k3, and why they are different from XP, since they tell nothing to customers and you can only dream of peeking into their code...
Also, please try using account without password, ie. UID=UserNoPwd;PASSWORD='' and see if it helps.

ODBC log

Attachment: sql.log (application/octet-stream, text), 19.65 KiB.

Hello. I tried with msjet40.dll versions 7328, 8618 and 9025 and I got identical results - it does not work.

I am also attaching an ODBC log of the problem. You see an initial failed connection attempt with a username Admin (I have no idea why the MySQL driver does that), than a successfull attempt and finally the "gone away" message. For the purpose of simplicity, I am linking a table with zero rows and a single column. Maybe the SQLExecDirectW function does not attempt to recconect even with the recconect option turned on?

Hi Philip and here's what I've found:
 - MyODBC 3.51.14 with Enable Auto Reconnect option turned on works as *expected* in standalone C++ test case, ie. C++ program continues to run after MySQL server restarts.
 - MyODBC 3.51.14 with Enable Auto Reconnect option turned on works as *expected* in MS own odbct32.exe (generic ODBC client), ie. you can continue issuing queries after MySQL server restarts.

The problem is *only* in MS Access, as your log too shows, searching for MSysConf  table as it shouldn't *or* MS should announce that Access could reliably be used as front end only for their databases.

Does any other ODBC driver survive SQL server restart (*not* MS SQL)?
Does Access have any config option for situations like this?

Still I'm setting this report to "Verified" state since I was able to repeat the behavior reported but in my opinion this question should go to Microsoft support...

Hello,

Thank you for your help. I do not think Microsoft Access is to blame, since the message "lost connection" that I am seeing comes from the mysql client library. Possibly there are several paths to be taken when executing a query within ODBC and the C++ code and the command-line client take one path, and Microsoft Access takes another (e.g. prepare + execute, rather than just execute).

Anyway, I will continue working on that on my own, however can you please tell me how can I get to compile the ODBC driver on my own under Windows? The documentation says QT 3 from trolltech is required, however this is no longer being sold.

Related links:
http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&uid=swg21179697&loc=en_US&cs...
http://www.mcse.ms/archive166-2004-5-644502.html
http://www.experts-exchange.com/Databases/Btrieve/Q_20772248.html
...
Philip, try typing "MySysConf" in Google.

Philip, compiling driver is in no way easy task! As for QT, you can use 4.1.1 and see which QT libraries you should remove from linker as they no longer exists. Some changes in library paths regarding QT will also be required.

Philip, I want to clarify one thing:
>Possibly there are several paths to be taken when executing a query within ODBC and the C++ code and the command-line client take one path, and Microsoft Access takes another (e.g. prepare + execute, rather than just execute).

Scenario 1: I stopped execution of program in the middle of code *not* made to handle lost connections, restarted server and continued program. Found no problems. 
Scenario 2: Using MS generic ODBC client, odbct32.exe, I connected to DSN, executed query, restarted server and reexecuted query. No problems found.

MyODBC 3.51.14 behaved as expected in two tests of which one was done with MS tool. Now tell me, how can I have reached any other conclusion?

I have provided you with links to other drivers having a lot of problems with MS Access. Did you check them?

If you can point me to a link that explains how Access is using ODBC or, even better, to Access source where creating linked tables happen, I'll be glad to look into it.

Thank you for keeping the bug open. I will try to provide a more detailed analysis of the issue with a patch.

While I agree that Microsoft Access is not an example of enterprise-grade robustness, I do believe that it is important for paying MySQL customers, therefore I will work on figuring this bug out one way or another.

Do you have any idea where I can find the debug version of myodbc3.dll? Any help on that would be appreciated.

After long search through Access options I've found the following:
  - Sometimes after server restart, using Tools/Database Utilities/Linked table manager helps - not allways
  - Disabling DDE in Tools/Options/Advanced tab definetly solved the problem. It might be worth investigating more.

Please test as shown on attached pictures and get back with your results.

Access settings - not working after restart

Attachment: 22385-1.jpg (image/jpeg, text), 39.98 KiB.

Access settings - working after restart

Attachment: 22385-2.jpg (image/jpeg, text), 40.29 KiB.

Hello,

Unfortunately neither of the two workarounds works for me. I do agree that it appears to be an Microsoft Access problem, however it is still worth fixing. My reasoning is that since I get the "connection lost" message from the mysql library, this means that the MySQL ODBC driver is being called at the time the message appears (the log appears to confirm that). If so, it does have the opportunity to reestablish the connection, unless Microsoft Access does indeed return a cached error message and does not give an opportunity to the MyODBC driver to restart the connection.

New example of Access settings

Attachment: 22385-3.jpg (image/jpeg, text), 40.00 KiB.

Philip, I'll try to explain more.
What I think is happening is this:
  - Access, with default settings, exhaust it's retry limit and considers connection to be dead. What is Access retry mechanism I really don't know.

So I changed settings as in 3rd picture increasing ODBC refresh interval (prevents Access from exhausting Number of retries quickly) and disabling automatic updates (for the same reason). This works fine for me.

I am receiving the same problem. I open up my MS Access database. I disconnect the network cable and try to open a linked table. Then I reconnect the cable and try to open the same table. I still get the "MySQL server is gone away" message. I am using Windows XP and MySQL driver 3.51.21. I have also set the settings in MS Access the same as the last picture that was posted.

Does this happen with newer versions of myODBC and AutoReconnect checked?

Autoreconnect works.

I Have this exact "MySQL server has gone away (#2006)” problem trying to use MS Access 2003 as a front end to process online orders received in my website MySQL database.

Connect timeout is set to 10 (I presume it's 10 seconds??!!) in MySQL and I don' believe I can change this (this is a shared server and even if the hosting company would allow it it might not be safe to increase this to 8 hours or so).

Is there any way around this or do I just quit on using MS Access as a front end? I tried chanching the DDE setting in MS Access but this made no diference.

Regards

Vitor

PS: Would it work if somehow I used an intermediate MySQL database on my PC (the same one running MS Access) to connect Access and the online database. I have no problem with local MySQL databases, but if it would work I would apreciatte a tip on how to establish the conncetion betwen the local and web server daabases (can i link tables just like in Access?)