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:

1. I've tried changing a few of the ODBC connection settings.
   • Checked Allow Big Results - FAILED
   • Checked Enable Auto Reconnect - FAILED
2. I've Checked and repaired tables - FAILED
   • A good number of tables resulted in, "table needs update please repair table"
3. 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

Answer 1

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