MySQL Workbench空闲时断开连接

I'm using MySQL Workbench 6.3 on my OS X 10.9.5 to manage several cloud databases (hosted on Rackspace), and I get the following issue:

我正在我的OS X 10.9.5上使用MySQL Workbench 6.3管理几个云数据库(托管在Rackspace上)，我得到了以下问题:

When inactive for 5 minutes, the following problems happen:

当不活动5分钟时，发生以下问题:

• I cannot run any query (error 2013: Lost connection to MySQL server during query)
• 我无法运行任何查询(错误2013:查询期间与MySQL服务器失去连接)
• when trying to browse tables on my db, I'm getting messages like "Tables could not be fetched", "Views could not be fetched", and so on
• 当我试图浏览db上的表时，我得到了“无法获取表”、“无法获取视图”等消息
• when refreshing the left panel, I get a "Error Code: 2006 MySQL server has gone away"
• 当刷新左面板时，我得到一个“错误代码:2006 MySQL服务器已经消失”

So basically the connection is gone.

所以基本上这种联系消失了。

This is really annoying since it happens after only 5 minutes of inactivity. Therefore I need to close the connection and reopen it each time.

这真的很烦人，因为它只发生在5分钟的不活动之后。因此我需要每次关闭连接并重新打开它。

I also tried this: MySQL Workbench: How to keep the connection alive, which didn't change anything. In my Workbench Preferences tab, I have the following setup:

我还尝试了这个:MySQL Workbench:如何保持连接的活跃性，这没有改变任何东西。在我的工作台首选项选项卡中，我有以下设置:

• DBMS connection keep-alive interval (in seconds): 600
• 数据库管理系统连接维持时间间隔(以秒计):600。
• DBMS connection read time out (in seconds): 600
• DBMS连接读取超时(以秒为单位):600
• DBMS connection time out (in seconds): 60
• DBMS连接超时(以秒为单位):60

Notice that this issue happens precisely after 5 minutes of inactivity! If I run two queries in a 4'59 minutes interval it works perfectly fine. Also my colleagues who connect to the same database on their Workbench don't have this issue.

注意，这个问题恰好发生在5分钟的不活动之后!如果我在4'59分钟的间隔内运行两个查询，效果会很好。我的同事在他们的工作台上连接到同一个数据库时也没有这个问题。

Does anybody have a solution for this?

有人能解决这个问题吗?

7 个解决方案

#1

Go to Edit -> Preferences -> SQL Editor and there you'll see:

点击编辑->首选项-> SQL编辑器，你会看到:

DBMS connection keep-alive interval (in seconds): 600
DBMS connection read time out (in seconds): 600
DBMS connection time out (in seconds): 60

The DBMS connection keep-alive interval means how often Workbench sends keep-alive request to the server to keep the connection alive.

DBMS连接保持活动间隔表示工作台向服务器发送保持活动请求以保持连接保持活动的频率。

Since 5 minutes == 300 seconds, set DBMS connection keep-alive interval < 300 (e. g. 250)

由于5分钟== 300秒，设置DBMS连接保持活动时间间隔< 300(例如250)

It will mean "send keep-alive request every 250 seconds". Click OK.

它将意味着“每250秒发送一个存活请求”。单击OK。

Then quit MySQL Workbench and relaunch it to make the changes take effect.

然后退出MySQL工作台并重新启动它，使更改生效。

If you use Standard TCP/IP over SSH connection method, it's also can be helpful to configure ssh ServerAliveInterval as well.

如果通过SSH连接方法使用标准的TCP/IP，那么配置SSH ServerAliveInterval也很有帮助。

#2

This bug exists in all versions of MySQL Workbench beyond 6.0 (at this time : 6.1, 6.2 and 6.3 have the bug).

这个错误存在于0以后的MySQL Workbench的所有版本中(此时:6.1、6.2和6.3都有这个错误)。

Downgrade to MySQL Workbench 6.0.x seems the only way to fix this problem.

降级到MySQL Workbench 6.0。x似乎是解决这个问题的唯一方法。

Download MySQL Workbench 6.0.x : http://dev.mysql.com/downloads/workbench/6.0.html

下载MySQL 6.0工作台。x:http://dev.mysql.com/downloads/workbench/6.0.html

#3

FWIW: Following Kosh's recommendation, I changed the settings as follows and it seems to have eliminated the issue on WB 6.3 running on Ubuntu 16:

FWIW:根据Kosh的建议，我更改了如下设置，似乎已经消除了wb6.3在Ubuntu 16上运行的问题:

DBMS connection keep-alive interval (in seconds): 60
DBMS connection read time out (in seconds): 60
DBMS connection time out (in seconds): 30

It may be overkill, but it works.

这可能有点过分，但确实有效。

#4

Kosh Very's Answer didn't work for me so I found a different solution for this:

Kosh Very的回答对我不起作用，所以我找到了一个不同的解决办法:

change max_allowed_packet in the my.ini file. (C:\ProgramData\MySQL\MySQL Server 5.6)

在my中更改max_allowed_packet。ini文件。MySQL(C:\ ProgramData \ \ MySQL服务器5.6)

max_allowed_packet=16M

max_allowed_packet = 16米

now restart the MySQL service once you are done.

现在重新启动MySQL服务。

#5

It solved me by setting tcp_keepalive_time to 120 seconds on Ubuntu 14.04 hosted on Windows Azure

它通过在Windows Azure托管的Ubuntu 14.04上将tcp_keepalive_time设置为120秒解决了我的问题

The TCP keepalive on the Azure load balancer is 240 seconds by default, which can cause it to silently drop connections if the TCP keepalive on your Azure systems is greater than this value. You should set tcp_keepalive_time to 120 to ameliorate this problem.

默认情况下，Azure负载均衡器上的TCP保持活动时间为240秒，如果Azure系统上的TCP保持活动时间大于这个值，那么TCP就会悄无声息地删除连接。您应该将tcp_keepalive_time设置为120，以改善这个问题。

1. To check the tcp_keepalive_time

   cat /proc/sys/net/ipv4/tcp_keepalive_time

2. 要检查tcp_keepalive_time cat /proc/sys/net/ipv4/tcp_keepalive_time。

7200 (by default 2 hours)

7200(默认2小时)

2.set value from 2 hours to 120 seconds.

2。设置值从2小时到120秒。

sudo sysctl -w net.ipv4.tcp_keepalive_time=120

net.ipv4.tcp_keepalive_time = 120

net.ipv4。tcp_keepalive_time = 120

3. recheck the value after changing.

   cat /proc/sys/net/ipv4/tcp_keepalive_time

4. 更改后重新检查值。猫/proc/sys/net/ipv4/tcp_keepalive_time

120

120年

4.Set the value in the sysctl file to remain the value even after reboot.

4所示。将sysctl文件中的值设置为在重新启动之后仍然保持该值。

vi /etc/sysctl.conf

Press i (To insert into file) net.ipv4.tcp_keepalive_time = 120(Add this line at the bottom of the file) :wq(Save and exit)

按i(插入文件)net.ipv4。tcp_keepalive_time = 120(在文件底部添加这一行):wq(保存和退出)

#6

Kosh Very's is the right answer. For anyone who couldn't get it to work, here's another solution:

Kosh Very是正确答案。对于任何无法让它发挥作用的人，这里有另一个解决方案:

Where I need to alter a huge table (drop or add column or such), is to run the query(s) by terminal:

我需要修改一个大表(删除或添加列等)的地方是通过终端运行查询:

1. Connect: mysql -u myusername -p

   连接:mysql -u myusername -p

2. You will be asked for password

   您将被要求输入密码

3. Run the long-taking query(s) you need. Note: writing a query in terminal requires an ending semi-colon (;) for each. Example: ALTER TABLE mydb.mytable DROP COLUMN mycol;
4. 运行您需要的长时间查询。注意:在终端机中编写查询需要为每个查询使用结束分号(;)。例子:ALTER TABLE mydb。列mycol mytable下降;

#7

This had driven me mental for months. My connections were to a Hostgator server. I'd connect and could edit a table for only 10 seconds or so after connecting, then I'd do, say, a table commit and the table would change to "Read Only" with a mouse-over message of, "Could not determine a unique row identifier (MySQL server has gone away) or "(Lost connection to MySQL server during query).

几个月来，这让我精神崩溃。我连接到一个主机服务器。我连接,只能编辑一个表连接后10秒左右,然后我会做,说,一个表提交和表将发生变化以“只读”的鼠标消息,“无法确定一个独特的行标识符(MySQL服务器已经消失)或“(查询期间失去了连接到MySQL服务器)。

The solution was, as per other suggestions here, to REDUCE the keep-alive setting. In my case, it had to come down to 10s (obviously, Hostgator if fairly miserly with their bandwidth!)

正如这里的其他建议一样，解决方案是减少保持生命的设置。在我的例子中，它必须降至10s(显然，如果主机商对其带宽相当吝啬的话!)

First I tried reducing SSH KeepAlive (under Preferences/Others/Timeouts) but this didn't work.

首先，我尝试减少SSH保持活动(在首选项/其他/超时下)，但这行不通。

What did the trick was reducing the DBMS connection keep-alive interval (under Preferences/SQL Editor/MySQL Session). I had to take it all the way down to 10s until the connection would remain stable. Your host might be different.

关键在于减少了DBMS连接的存活时间间隔(在Preferences/SQL Editor/MySQL Session下)。我不得不把它一直降低到10秒，直到连接保持稳定。你的主机可能不同。

Finally, no more "Refresh All", wait, do something, rinse and repeat.

最后，不再“刷新所有”，等等，做点什么，清洗和重复。