Hard to debug "Mysql server has gone away"

Question

I'm running a MsAccess with Mysql background database and today I am experiencing some problems.

Depending on how far I can get into the program I am able to get a list of all the names and entry. But once I go into the edit form of any entry I get a variety of results. Sometimes I am lucky and I can see the first entry. But any entry after that will always get a MsAccess error: Object invalid or no longer set and then any subsequent calls after will yield a Mysql ODBC error of Mysql server has gone away.

I've looked around at several websites and even all the stack overflow sites with the same question and I've tried a variety of solutions. (Keep in mind that this database has been running for years and this is the first time I am getting this message) It also takes about 2m14s to 2m59s before it displays any error messages.

Here is what I tried so far:

I've tried changing a few of the ODBC connection settings.
- Checked Allow Big Results - FAILED
- Checked Enable Auto Reconnect - FAILED
I've Checked and repaired tables - FAILED
- A good number of tables resulted in, "table needs update please repair table"
I've Doubled the numbers in the my.cnf configuration and restarted the mysql service as well as the msaccess software. FAILED
- These were the settings of my my.cnf after I made the changes.

Here:

set-variable = max_connections=500
safe-show-database
log-error=/var/log/mysqld.log
connect_timeout=1000
interactive_timeout=28800
wait_timeout=288000          <**This was changed**

join_buffer_size=6M
key_buffer_size=300M         <**THIS WAS CHANGED **
max_allowed_packet=300M      <**THIS WAS CHANGED **
myisam_sort_buffer_size=300M <**THIS WAS CHANGED **
read_buffer_size=6M
sort_buffer_size=6M
table_cache=12288
thread_cache_size=24
tmp_table_size=132M
query_cache_limit=3M
query_cache_size=64M
query_cache_type=1

Basically I've tried every suggestion I could find so far and I can't seem to figure out the problem.

I've also had a look at MYSQL: Has gone away If I go through the bulleted list there

Nobody has killed the running thread
I don't think the query was ran after the connection was closed (since it ran for years fine)
Client application does have the privileges needed
I don't know how to figure out if I had a timeout from the TCP/IP connection on client side
I don't know if I've encounted a timeout on the server side, but I do know that automatic reconnection in client is disabled
???
The query could potentially be large since it is a form with many subforms
???
DNS should be ok, since I can connect to it with a real sql viewer (HeidiSQL)
???Child forks???
???

I think this is one of those bugs that is hard to figure out since I'm exhausted :/ I'm probably missing some info but I am not sure what else to include.

---EDIT---

Thank you all for your comments, I'm still debugging this issue. It seems that it's not all the forms that are causing some issues. So I'm starting to think that this is a MSAccess issue more than it is an MySql issue. The forms that do break all have the same line in their VBA code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If Screen.ActiveControl.Name = "UnboundTextBox" Then
       Response = acDataErrContinue
    End If
End Sub

I don't recall putting this code in, so I assume it's an automatic thing, but I'm still going through all my forms to see which ones cause this error and which do not. So more in a few.

---Today I'm doing some debugging trying to find whatever I can from whatever log I can muster ----

 MSACCESS        17ec-b10   EXIT  SQLStatisticsW  with return code -1 (SQL_ERROR)
    HSTMT               0AF82920
    WCHAR *             0x00000000 [      -3] <empty string>
    SWORD                       -3 
    WCHAR *             0x00000000 [      -3] <empty string>
    SWORD                       -3 
    WCHAR *             0x0013AAE8 [      -3] "location\ 0"
    SWORD                       -3 
    UWORD                        1 <SQL_INDEX_ALL>
    UWORD                        0 <SQL_QUICK>

    DIAG [08S01] [MySQL][ODBC 3.51 Driver][mysqld-5.0.92-community]Lost   
 connection to MySQL server during query (2013)

----------------------------- FINAL EDIT --------------------------

The past week or so, my boss has been working hard to install a new internal server for our office. We also switched IPs several times and ISP providers. Turns out, as a result he created for me an administrative nightmare, as I attempted to debug issues with the server, and issues with the software ect.. ect.. when the issue was in the settings the ISP providers established with the router settings etc... etc... SO basically this whole headache was nothing wrong with server, nor software but hardware inbetween.

So now, I have to backwards engineer everything I did and try to get it to work on both ips. It works on the important one now. But it's not working on the old one so... I guess I just have to figure that out. [Waving imaginary fist in the air] Lol

Tried to Restart the VPN container (aka: I Rebooted the server that runs the mysql) **Failed took 3m22s this time to run the failing query** — Mallow, May 24 '11 at 21:39
did you know you can ping the server to keep it alive, *(not sure how to call that from msaccess though)*: http://dev.mysql.com/doc/refman/5.1/en/mysql-ping.html — Johan, May 24 '11 at 21:42
I found that out while I was trying to research to debug the problem. I read somewhere that the ODBC connector was suppose to do that, although I'm not sure if it's a setting that I need to turn on or if it is automatic. — Mallow, May 24 '11 at 21:47
Do you have more than one client box running? If yes, do you have this problem on all client machines? — HK1, May 25 '11 at 11:51
Have you recently made any changes to the MySQL tables? You need to drop the linked tables in Access and then re-link them in order to properly see any changes. — John M, May 25 '11 at 14:43
I have 4-5 client boxes, they all experience the same general issue. I did create one new table recently, and I relinked everything. What is weird is that as long as I don't cause the Object invalid or no longer set error I can see the tables and queries without much hassle. Upon this discovery I'm going to play around with some subform and see if the issue is MSAccess as opposed to MySql — Mallow, May 25 '11 at 16:15
What happens with a different mysql client from the same box? Maybe a simple command line interface? — wallyk, May 25 '11 at 19:17
all the mysql clients use MSAccess with the ODBC and it's the same version of the file throughout the network. There seems to be a connectivity issue, It's as if I can read data through the forms only half the time, but if I were to use the tables and queries exclusively I am fine. Right now I found the problem with linking tables. Some tables relink no problem, but others give me a 2013 error (Lost connection to MySQL server during query) I'm really at a lost... I don't understand why it would suddenly stop? I've even tried an older version of the file prior to the changes and I get the error — Mallow, May 25 '11 at 20:02
@John: You can use the linked table manager to update all linked tables in one go, without having to remove/add each individual one. — Marc B, May 26 '11 at 18:53
I started doing that, It stops updating the table links after doing 11 of them, after that it crashes. So since that's the easiest thing I can reproduce I use that to debug the problem — Mallow, May 28 '11 at 05:41
For ODBC linked tables, you don't want to update them, you want to DELETE them and recreate them from scratch. This is because there is metadata about the server cached in the linked table definition that is not updated when you refresh a link. Changing the structure of a linked table or linked view may cause it to break or become read-only, and the only way to fix it is to delete the linked table and recreate it. — David-W-Fenton, May 28 '11 at 21:13
The form error code you quote is something added by a programmer. It is not some standard Access code, nor something added by an Access wizard. — David-W-Fenton, May 28 '11 at 21:13
I've deleted one table (for a test) and since have not been able to add it again. So I put the delete all tables method on an absolute last resort. (Until I can relink the one I already deleted) [Besides the relationship structure is complex and I don't want to have to deal with rebuilding that up.] — Mallow, May 30 '11 at 19:11
I don't know what you're talking about here. Deleting and recreating a linked table should not be hard at all -- you do understand that I'm talking about deleting the link and recreating it, not deleting the actual table? Relationships are not in the front end, so they don't have anything to do with it. — David-W-Fenton, May 30 '11 at 21:58

score 2 · Accepted Answer · answered Sep 01 '13 at 08:13

The problem is that the connection is timing out. This is not a setting that you can change at the ODBC level. My MySQL provider had set the timeout at 30 seconds (you can find out what yours is set at my running the PassThrough query "SELECT @@wait_timeout"). I got round it by polling the connection every 10 seconds with a simple form. That keeps the connection alive. Make sure you have an AutoExec macro which opens the form.

Private Sub Form_Open(Cancel As Integer)
    Me.TimerInterval = 1000 ' 10 seconds
End Sub

Private Sub Form_Timer()
    Me.Requery
End Sub

I didn't know about that feature, that sounds awesome! Man I wish I could remember how or what I did to get around that problem since I haven't seen it in a long while. But I really like your solution, I wouldn't be surprised if this would have actually been the problem. I'm going to keep it in mind for the future. Thanks a million!! — Mallow, Sep 11 '13 at 19:21

score 1 · Answer 2 · answered May 28 '11 at 21:17

1

I have not used MySQL with Access in a production environment, but the problem you are having sounds suspiciously similar to one I've encountered with SQL Server. Basically, if the client workstation has incorrect DNS settings, it can get confused about how to resolve the connection to the SQL Server and lose the connection. In the cases where I've encountered this, the workstations were incorrectly configured with the Internet provider's DNS as primary DSN, but the SQL Server was a local server, available only on the local LAN (and not mapped by the ISPs DNS).

The solution is to use the local domain controller as the primary DNS. It will look up the local SQL Server's IP and pass off any non-local lookups to the Internet provider's DNS.

If you don't have a Windows/Samba domain controller, then you need to use whatever the local LAN's authorative name server is. Alternatively, you could connect to the MySQL server by IP address and avoid the problem, or map it in the client workstation's HOSTS file (which is not recommended -- it's a great way to cause really hard to troubleshoot problems when the IP addresses change).

answered May 28 '11 at 21:17

David-W-Fenton

22,871
4
45
58

1

I believe you were the closest to getting to the root of my problems. Albeit I was never going to delete all my tables to relink them again, (That's just plain insanity :P [Not really insanity but rather a measure of last resort and or a measure to test with one table at a time just not all of them in one go.]) I'm not sure I understood your answer here as our server is external (for the moment) But this may very well help somebody else with a similar issue. I seem to have found the cause to the problem, which I updated above. Thank you for the time you spent with me. – Mallow May 30 '11 at 19:29
2

ODBC linked tables MUST be deleted and recreated when the table/view they link to has been altered. Otherwise, they fail to work fully. There is no two ways around that, but it's really easy to code within Access, so I don't know why it would be a problem. – David-W-Fenton May 30 '11 at 21:56
Regardless of where the server is, if DNS can't be properly resolved (or is intermittently available for resolution), it can lead to this type of problem. I've encountered it when the server is local, but it could theoretically be just as much of a problem when it's remote. – David-W-Fenton May 30 '11 at 21:57
When I've altered table in the past I usually just re-link the tables. I haven't had much issue with this before, but I'm curious to find out and see how they can fail. Do you happen to have an example alteration I can do to a table in order to replicate these fails? I apologize in advance if I'm being a bit resistant. – Mallow May 31 '11 at 05:23
It may depend on the database server. With SQL Server, relinking (i.e., refreshing the linked tables) does not clear all the metadata. I've never tried it with MySQL (I just recreate the linked tables when I alter structure), so maybe there's a difference in the type of metadata stored in the linked tables that causes the problem. I still don't see what the issue is deleting the linked tables and recreating them. It's a pretty simple thing to do (walk the TableDefs collection, store the information for each table you're going to delete in an array, delete them, then restore from the array). – David-W-Fenton Jun 02 '11 at 19:57
Adding a column to a view will often make it read-only, for intance, or not display the new column. Adding a column to a table may result in the table being read-only, and the new column not being visibe. I can't give you specifics on what exactly happens, because as soon as I discovered that you can't rely on the linked table remaining fully operable, I stopped trying to updated them and changed to just deleting and recreating them. – David-W-Fenton Jun 02 '11 at 20:00
My only problem with creating a code that deletes and recreates code is that I cannot do that off hand, as I've never done it that way. So it would require time I don't necessarily have, unfortunately. Although It doesn't seem too difficult to find. (question 5695194) The only difference I possibly see between why we are having different experiences with linking tables could be the references we have on in our VBA (Tools->References) I remember having to turn something on in here, but it was so long ago I couldn't even tell you if what we have in here is something we need or not. – Mallow Jun 02 '11 at 20:47
For manipulating linked tables, there are no references required except the default Access and DAO references. I don't use code for this because I don't make lots of changes to my back-end tables, and a new update goes out along with any such change, so nobody ever has an out-of-date front end (i.e., one that would need to have the linked tables updated). – David-W-Fenton Jun 08 '11 at 23:47

score 0 · Answer 3 · answered May 27 '11 at 13:48

0

[mysqld-5.0.92-community]Lost   
connection to MySQL server during query (2013)

This genary means you have found a bug in MySQL-Server can you post a tail of your mysqld.err and mysqd.log

this makes the server restart and will effectefly shut down all open connections

answered May 27 '11 at 13:48

borrel

911
9
17

Thanks for the reply. Unfortunately I do not have a mysqld.err file on my server. (find / -name mysqld.err) My mysqld isn't crashing, and my server restarts once a day (It also isn't crashing) So there doesn't seem to be an error with the box itself. Unfortunately I'm remotely controlling my work pc at the moment so I will post my mysqld.log in the morning on the original post. Interestingly though nothing seems out of the ordinary. Since I was changing the my.cnf, it just shows the amount of times I rebooted. (Earliest error before that is on 11-05-15, the rest is just me rebooting) Although – Mallow May 28 '11 at 05:46
I cannot turn general_logs=on in the my.cnf, I get:: unknown variable 'general_log=on' – Mallow May 28 '11 at 05:47

Hard to debug "Mysql server has gone away"

3 Answers3

Linked