MySQL Bugs: #73172: Mysql workbench hangs after idle

Bug #73172	Mysql workbench hangs after idle
Submitted:	2 Jul 2014 12:25	Modified:	24 May 2018 14:39
Reporter:	Vilhelm Heiberg	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	6.1.7	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	hangs, idle, MySQL, remote, windows, workbench

Description:
This is a new report of an old bug, because the old bug does not get any attention from the workbench team.

This problem has been in the workbench for at least a year and is still there in the newest version.

When the workbench is connected to a remote db (anything not localhost, it seems) and the program has been idle for a little while (a few minutes is enough, it seems) and I return to the workbench it hangs for a few seconds (up to a minute, sometimes) when I click somewhere. It hangs again for a little while when I do the first query. After that it has "refreshed itself" it seems, because then I can work normally. I can work normally as long as I work actively in the workbench. When it goes idle for a while (i do something else, get a cup of coffie or something) I have to start over again. The best workaround is to close the application and start it again before I do anything else in the workbench.

I experience this when connectiong to a mysql server on an Azure server.

The CPU is not doing much when it is hanging. It is probably doing some kind of network traffic/connection refresh, or something like that.

This is a serious problem because many users have reported this.
See bug #69241 for more details.

How to repeat:
Connect the workbench to a remote mysql server (on Azure, for instance). Do something. Take a break. Return to the workbench.

Suggested fix:
Find the piece of code that does some unneccessary slow network traffic on the UI thread.

Please try version 6.1.7. Thanks.

I just tried 6.1.7
It is the same problem there.

I will upload a screenshot of the problem.
I did a query that took 0.063 seconds to perform.
Then I waited 10 minutes and tried to do the same query again.
Then this query took 30 seconds. During that time it looked like the enclosed screenshot, with a "waiting-ring" next to "Query 1".

screenshot while hanging on a simple query

Attachment: workbenchHang1.PNG (image/png, text), 18.62 KiB.

Undoubtedly there is a timeout happenning somewhere, and reconnection is slow for some reason. It is probably not an issue with neither MySQL nor Workbench, but rather a networking issue haapening somewhere on the 'network road' from client to server.

We faced the same problem years back and blogged this:
http://blog.webyog.com/2009/09/02/%E2%80%9Cmysql-server-has-gone-away%E2%80%9D-part-2-sess... (read also comments!)

You can check what the server/global setting for 'wait_timeout' is.  If it is low, this explains why the MySQL server times out the connection. You can set a higher value for the sessison by executing someting like this: "SET wait_timeout = 28880;" after connection. 

However if the timeout happens in some networking somehwere this will not help. The MySQL protocol supports mysql_ping(http://dev.mysql.com/doc/refman/5.0/en/mysql-ping.html) for handling such cases (but I doubt Workbench supports this). 

In cncusion, I am not in doubt that the problem is a networking issue causing reonnects becoming slow. So either you will have to identify and solve this or you will have to eliminate the need for reconnects.

-- Peter
-- not a MySQL/Oracle eprsn

I agree it is a timeout issue.
I tried to set wait_timeout to a high number on the server (it was 60, I set it to 28800).
The experience improved a little. I still get the hanging behavior, but not every time. It appears it hangs if the idle period is more than 5 minutes.

I definitely think this is a workbench issue, even when it is a networking issue.
It affects the behavior of the workbench and it should be solved in the workbench.
If it can be solved with some parameters on the connection, then add those as default.

Other mysql query tools, like HeidiSql, does not have this problem. So it is solvable.

Workbench team, please look into this!

I am not convinced! :-)

However there are some differences between Workbench and most other GUI tools that may matter.

1) Workbench uses the C++ connector for connecting.  Most other GUI tools use the  C-API (directly as SQlLyog does), or through a wrapper (Delphi in case of HeidiSQL)

2) If you use SSH tunnel, they also use different code. Workbench uses a Python library. All other tools that I know (that have SSH tunnel as an option) use PLINK (a commandline implementaton of basic PuTTY funtionalities). This applies to both SQLyog and HeidiSQL.

.. so 1) and/or 2) above could theoretically be reasons why WB will not always reconnect as smoothly as other GUI tools (if it is the case).

"It appears it hangs if the idle period is more than 5 minutes".  Then I think some networking gear (router, gateway, hardware firewall, switch, whatever) will disconnect you after 5 minutes of inactivity. And is extremely slow to reconnect. 

For how long did you try to wait, BTW?

I found this in HeidiSQL forums: https://code.google.com/p/heidisql/issues/detail?id=3049 (from what I guess that heidiSQL uses mysql_ping()at some interval to keep the connection alive and thus prevent timeouts (it may be standard for the Delphi wrapper of the C-API that HeidiSQl uses).  In SQLyog is is an option to use mysql_ping(). But I don't think that Workbench has that option (and I don't even know if it is implemented in the C++ connector at al).

If the problem is that the network disconnects you after 5 minutes and also is extremely slow to reconnect, the only solution is to use a tool that mysql_ping()s the server at a shorter interval than the disconnect happens.

Bug #69241 marked as duplicate of this one.

halting a large query that will otherwise eventually finish will also cause mysql to stop responding. it gets worse the longer you wait before halting.

here is a simple repro that queries the information schema on a server with lots of databases, tables and columns (about 50 databases and 800k columns):

select t.table_schema, t.table_name, c.column_name
from information_schema.tables t
join information_schema.columns c on t.table_name = c.table_name and c.table_schema = t.table_schema
where lower(c.column_name) like '%somekeyword%'
and t.table_schema = 'somedb'
;

It happens to me as well. I used to work with sqlyog and I also have sqlpro - none of them has this issues (I run THE SAME queries so it's not a connection issue).

Thanks.

using version 6.3 and it possibly got worse than before.

Changed from 6.2 to 6.3 and I have the same issue

I was ready to throw away workbench due to the frequent freezes every time I worked with it. Found this thread, and noticing the timeout issue, tried this:

Edit > Preferences
SQL Editor > MySQL Session > DBMS connection keep-alive interval

The default here was set to 600 seconds. I've reduced that to 30 seconds.

No issues so far.

After a whole day of testing, leaving Workbench idle for long periods of time; I can no longer reproduce the issues seen before. (after reducing the interval)

I think this bug should be closed, but in future versions of the software, perhaps it would make more sense to set the default timeout interval much lower, so as to avoid the issues people have been experiencing with longer intervals.

Great! 
This setting does indeed solve the problem:

Edit > Preferences > SQL Editor > MySQL Session > DBMS connection keep-alive interval

I suggest a lower value than 600 for default setting.

I suggest the issue can be closed. But Oracle should look into why it takes 3 years to solve a simple but serious bug-report like this.

In addition to lowering the interval: instead of just hanging indefinitely, and freezing until the OS force quits it, there should be some logic added to detect the timeout. It should display a proper dialog, and be given the option to reconnect, restart or quit; rather than just freeze indefinitely.

Even if the issue can be solved by lowering the interval, the program still presents an unacceptable freezing behavior on timeout. This needs to be handled more gracefully.

definitely: the issue is NOT that the connection hits a timeout, BUT that the program does not handle it correctly, freezing until you force its termination

I realize that this is an old issue, but..
Since no one pointed out the real problem I felt it was justified to write my view on it.

The problem occurs because of azures habit to close idle connections after 4 minutes(Default setting). That's why it worked to ping the server more frequently. The timeout period can be configured and be between 4-30 minutes.

More information can be found here:
https://azure.microsoft.com/sv-se/blog/new-configurable-idle-timeout-for-azure-load-balanc...

Duplicate of Bug #82291