难以调试"Mysql服务器已消失"； [英] Hard to debug "Mysql server has gone away"

问题描述

我正在使用Mysql后台数据库运行MsAccess，今天我遇到了一些问题.

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

根据进入程序的距离，我可以获取所有名称和条目的列表.但是，一旦进入任何条目的编辑形式，我都会得到各种各样的结果.有时我很幸运，可以看到第一个条目.但是此后的任何条目都将始终出现MsAccess错误:对象无效或不再设置，然后此后的任何后续调用将产生 Mysql服务器消失的Mysql ODBC错误.

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.

我环顾了几个网站，甚至所有堆栈溢出站点，都提出了相同的问题，并且尝试了各种解决方案. (请记住，该数据库已经运行了多年，这是我第一次收到此消息.)在显示任何错误消息之前，大约还需要2到14毫秒到2到59毫秒.

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. 我尝试更改一些ODBC连接设置.
   • 选中允许重大结果-失败
   • 选中启用自动重新连接-失败

1. I've tried changing a few of the ODBC connection settings.
   • Checked Allow Big Results - FAILED
   • Checked Enable Auto Reconnect - FAILED

• 大量表导致表需要更新，请修复表"

• 这些是我进行更改后my.cnf的设置.

这里:

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.

我还查看了 MYSQL:已经消失了离开 如果我在其中浏览项目符号列表

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

• 没有人杀死正在运行的线程
• 我不认为该查询是在连接关闭后运行的(因为它可以运行多年)
• 客户端应用程序确实具有所需的特权
• 我不知道如何确定客户端的TCP/IP连接是否超时
• 我不知道服务器端是否超时，但是我知道客户端中的自动重新连接已禁用
• ???
• 由于该查询包含许多子表单，因此该查询可能会很大
• ???
• DNS应该可以，因为我可以使用真正的sql查看器(HeidiSQL)连接到它
• ???儿童叉子???
• ???

• 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.

-编辑---

谢谢大家的评论，我仍在调试此问题.似乎不是所有导致某些问题的表格.因此，我开始认为这是MSAccess问题，而不是MySql问题.确实损坏的表单在其VBA代码中具有同一行:

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 --------------------------

过去一周左右，我的老板一直在努力为我们的办公室安装新的内部服务器.我们还多次交换了IP和ISP提供商.结果是，当我尝试调试服务器问题以及软件问题等时，他为我创建了一个管理上的噩梦.当问题出在设置中时，用路由器设置建立的ISP提供程序等等...等等...因此，基本上，这整个头痛都与服务器无关，也没有软件，而是两者之间的硬件.

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.

因此，现在，我必须向后工程我所做的一切，并尝试使其在两个ip上均可使用.它现在适用于重要的产品.但这不适用于旧版本，所以...我想我只是必须弄清楚这一点. [挥舞着假想的拳头]哈哈

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

推荐答案

问题是连接超时.这不是您可以在ODBC级别更改的设置.我的MySQL提供程序已将超时设置为30秒(您可以在运行PassThrough查询"SELECT @@ wait_timeout"时找出设置的超时时间).我通过使用简单表格每10秒轮询一次连接来解决此问题.这样可以使连接保持活动状态.确保您具有打开表单的AutoExec宏.

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

这篇关于难以调试"Mysql服务器已消失"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！