MySQL Bugs: #74557: Workbench 6.2 hangs on basic queries with large record sets over SSH tunnel

Bug #74557	Workbench 6.2 hangs on basic queries with large record sets over SSH tunnel
Submitted:	24 Oct 2014 21:49	Modified:	19 Sep 2015 1:33
Reporter:	Marc Peterson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	6.2.3.12313 b2280 32-bit	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I upgraded from Workbench 6.0 and now it hangs frequently, particularly when large record sets are involved.

SELECT * FROM table LIMIT 0,1000;

I'm tunneling over SSH to an Amazon EMI, which then connects to an Amazon RDS database.  Queries with small record sets work, but large ones will hang for  5+ minutes then return the error message:

Error Code: 2013 Lost connection to MySQL server at 'waiting for initial communication packet', system error: 10060

Once this happens, if I try to do anything else I get the error:

Error Code: 2003 Can't connect to MySQL server on '127.0.0.1' (10061)

I cannot do anything until I restart Workbench.

Or if I click the "stop query" hand icon during the 5 minute wait, Workbench will then crash.

My laptop still has 6.0 and it works flawlessly with the same queries, so I doubt it's a server setup issue.  Running these queries with 6.2 to a local database is also fine.  Something changed between 6.0 and 6.2 in relation to SSH tunneling.

How to repeat:
Setup an SSH tunnel to an Amazon AMI, connected to an Amazon RDS.

SELECT * FROM table LIMIT 0,1000;

Thank you for the bug report. Looks like a timeout issue please check with server administrator about. Thanks,

I'm the server admin too.  This doesn't feel like a timeout issue.  Why would it affect only some queries?  Why would it affect 6.2 and not 6.0?

Thank you for the feedback. Are you able to test with the mysql command client tool if it is affected as well with same queries?. Thanks.

I've spent 2 hours trying to configure an SSH tunnel on windows using Putty (and Plink).  I can create the tunnel, but cannot get mysql.exe to use it via command line.  If you have instructions, I'd be happy to follow them.

I have the same problem.  The command works without hesitation on the command line.  But it causes Workbench to hang for a long time and then produce:

Error Code: 2013 Lost connection to MySQL server at 'waiting for initial communication packet', system error: 10060

Here is the command, anonymized somewhat:

SELECT lf.name as FileName, concat(c.firstName," ",c.lastname) as Author, 
    lf.creationTime as Created, lf.lastModifiedTime as 'Last Modified', 
       lf.numDownloads as Downloads, lf.numViews as Viewed 
FROM table_lf lf 
join table_lm lm on lf.libraryVolumeId=lm.id 
left outer join table_c c on lf.contributorId=c.id 
WHERE lm.customerId = 12345 
order by Downloads;

I have the same problem, only it's any limit > 200. Even on a table that has just 1500 rows. 200 rows works fine. Set it to 5000 rows and it hangs, even if there aren't 5000 rows in the table.

I have switched computers and installed MySQL Workbench with same DB settings and Workbench settings (I think) on the new computer. However, on the new machine I cannot do big queries.

Simple query like "select * from table limit 1000;" works fine. However, if I increase limit to 10000, I get the following error after 600 seconds:

Lost connection to MySQL server at 'waiting for initial communication packet', system error: 10060

What can be an issue and how to solve it? Thanks

I have the same problem with Workbench version 6.2.4, 64bit on windows 7.

Extra info: I do not have the problem if I connect to a server version 5.5
Working on
mysql Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.2

NOT working on 5.6 server
mysql  Ver 14.14 Distrib 5.6.22, for Linux (x86_64) using  EditLine wrapper

The data are the same on both servers.
It works via the console.

Here is the output from the output window when it fails:
SELECT * FROM cnexxus_eventmin.bank_transactions	Error Code: 2013 Lost connection to MySQL server at 'waiting for initial communication packet', system error: 10060	

After I execute the query, the message says "running" for like 5 minutes.
Then I get the above error.
If I try and stop the query, Workbench crashes.

I connect with Standard TCP/IP over SSH

Are you able to repeat the problem, and are there a solution in it's way?

Best Regards
Thomas

I have now tested it on a direct connection to port 3306 without a SSH tunnel.
Then the problem is gone.

Any clues on what it can be?

Reverted back to 6.0.9 and everything works fine.

I can confirm with works with 6.0.9.

Thank you for the tip.

I look forward to we can use the latest version again.

I too had to revert back to 6.09 in order to use MySQL Workbench.

I can add that I'm having this problem as well. I have a MySQL server instance on my local network. I've not set up an SSH tunnel to communicate with the server, just the normal default connection over port 3306. So I don't think the problem is limited to large record sets over an SSH tunnel to a server on a remote network. It happens with large record sets under any circumstances.

The problem is not exhibited in 6.1.7, although 6.1.7 exhibits Bug #72214 (Filter Rows: non matching rows are displayed with NULL rows), which is sufficiently problematic that I have to go back to 6.0.9.

The difference between 6.0.9/6.1.7 and 6.2.4 (or, really, 6.2.x) is dramatic. I tested a table that is 50MB on disk on the server itself and simply did a "select * from tablename" without the 1000 row result limit. There are 78,334 records in this table.

6.0.9 and 6.1.7 were both 6 seconds from query execution to full display in the query result window. They both used roughly 60MB of RAM for the entire MySQL Workbench instance while viewing the query result, after briefly hitting 110MB during query execution, which is reasonable given that the table is 50MB and that MySQL Workbench was using 50MB-55MB of RAM before I even ran the query.

6.2.4 took over 3 minutes to accomplish the same query against the same server on the same machine. The CPU and I/O on the server is effectively idle. On the client, MySQL Workbench is utilizing 100% of one core throughout the wait process. RAM usage climbed steadily to 1.35GB and stayed there after query execution. Worse yet, if I ran the same query again, RAM usage went from 1.35GB to 2.35GB, whether I closed the tab and ran the query again or just ran the same query in the same sql query tab. The older versions exhibited no such memory leaking issues with repeated multiple runs of the same query in the same query window (and/or after closing the original query window and opening up a new one). The 32 bit machine I tried it on took over 5 minutes before Workbench simply crashed having said that the mysql client ran out of memory. Same machine had no issues with the older versions.

I have tried all these tests against multiple servers on different networks from different client machines. Clients are all Windows 7, servers are all different versions of MySQL 5.1.x on RHEL. The results are the same whether I have a MySQL Workbench window open that I've been using for a while or for clean restarts of MySQL Workbench.

I believe Bug #74785 (http://bugs.mysql.com/bug.php?id=74785) is a reflection of the same problem being exhibited.

What helped me was to enable the "WAN" compression protocol using the SSH tunnel..

Afterwards I'm able to retrieve to 1000 rows again.

I never had an issue with 1,000 rows, but larger datasets than 1,000 rows are problematic whether you're over an SSH tunnel or not.  And it wasn't an issue under 6.0.x/6.1.x.

I am able to repeat this issue with how [19 Feb 17:20] BJ Quinn commented disregarding using SSH tunnel or not.

http://bugs.mysql.com/bug.php?id=75808 marked as duplicate of this one.

I can confirm that this appears to be fixed on 6.3.2 RC.  It's in fact maybe even faster than 6.1.7 and earlier, though I haven't done any direct tests.  23k records in a fairly wide table loads nearly instantaneous now.

Thanks for the feedback. Glad to hear it is fixed now.

This bugs still affects me, I'm working on 6.3.2 win32 no-install.

This bug is really a pain and affects me also in the newest version. I can confirm it from 6.1 up to 6.3.
It occurs on completely different systems. Production databases and vagrant test systems.
As written before one can do selects on lightweight tables. But on tables with much data and with many rows it hangs and for me it never recovers from freeze. I always have to kill the process.
Like this I cant use mysql workbench at all anymore in the projects relying on databases only available over tunneling.

I'm willing to give you any information I can to help fixing this issue. It's definitely a problem in mysql workbench because I can run those configurations with HeidiSQL without troubles.

Please take this problem serious and try to fix it. It's a very old issue and seems to affect many. Also it occurs here in so many environments I can't believe it is not reproducible.

It's definitely an issue with SSH Tunnel.  We recently moved our database from our app server to Rackspace's Cloud Database connecting through SSH tunnel.  The day we changed, this issue started for me.  Just downloaded 6.3.3.0 (Windows, 64bit), still happening. It just hangs for a long time (10 minutes-ish) then displays the message "OK".  
I see the status is Verified - is there an ETA on a fix?  Disappointed that I'm going to have to look for another program until then.

The issue is definitely with the SSH tunnel in workbench.  You can "get around" the error by tunneling through a different program.  Very annoying but a workable solution.

I have the same issue with MysQLWorkbench 6.2 or 6.3.3 (64 Bits, Windows 7), when connecting to MySQL 5.6 using the built in SSH connector.

I don't have this problem when I connect to MySQL 5.0 or 5.5

I noticed  that activating the option "use Compression protocol" for the WAN network didn't solve my problem.

I guess I will have to downgrade to Workbench 6.0.9 :'(

This bug is reproduced in my env. Version 6.3.3.0 build 592 (64 bits)

I'm having the same issue. Confirming that it appears to be an issue with the SSH tunnel. I'm using putty (plink) to create a tunnel and switched my MYSQL Workbench to use that tunnel instead (127.0.0.1:PORT) and all my queries suddenly work fine. I would suggest this until they figure this out.

I am seeing the same behavior as described in the original bug using MySQL Workbench v6.3.4 running on a Windows 8.1 machine. I am connecting via an SSH Tunnel to a MySQL 5.6.22 RDS instance on AWS. The table being queried had 150 records. It takes on average 11 minutes for the query to fail with the 'waiting for initial communication packet' error.

I can also confirm John Bonds's comment. When using PuTTY Plink to create the SSH tunnel and switching MySQL Workbench to use that tunnel, the query began to work fine.

For those noting how they're tunneling in via plink, can you offer some quick instructions on doing so, or a link?  Thanks.

To tunnel via PLINK:
1. Navigate to plink.exe
2. run this command: plink -L 3306:127.0.0.1:3306 ServersIPHere
3. Log into the host server with your credientials
4. Connect with workbench to 127.0.0.1 @ port 3306

You can do this with putty as well under SSH->Tunnels.  You simply do
1. Source port: 3306
2. Destination: 127.0.0.1:3306
3. Add, then connect as you normally would to the host server.

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.3.5 release, and here's the changelog entry:

The MySQL server connection would sometimes timeout (error 10060) while
processing the result set when combined with a large number or rows (e.g.,
10,000) and an SSH tunneled MySQL connection.

Thank you for the bug report.

I have been having this hang problem doing a 1000 record SELECT query using MySQL Workbench 6.2.4 on Windows 8.1 when using an SSH tunnel through a AWS Ubuntu EC2 server to our AWS RDS-MySQL database while not having any problem when using a direct port 3306 connection (with or without SSL encryption), so I just downloaded and trested 6.3.5 as suggested above. I found that my first such query now works but a subsequent one still hangs.

I tested with:
Windows (x86, 64-bit), MSI Installer	6.3.5	27.1M	Download
(mysql-workbench-community-6.3.5-winx64.msi)	MD5: 0c6d3e66b44f1a4ee49a824c71312546 | Signature

Anyone else still having this problem?

Close but no cigar,
Greg Thomspon

same problem after upgrading to newer version. How come it get closed!

Still does not work on the latest and greatest (6.3.5) build 201.

Just hangs indefinitely without any way to stop it except killing the process.

Query>Stop does nothing but freeze the UI as well, after which windows starts to kill the process.

Server version is MySQL 5.6.25 on the latest Ubuntu LTS 14.X.X version (64-bits)
Default config except that it's in TRADITIONAL mode.

This bug is definitely not done yet.

I know the description of this bug is for Windows 7, but I am also having this issue on OSX 10.11. I am also using an SSH tunnel through an AWS EC2 instance to an RDS server running MySQL 5.6.

Still happening here on 6.3.6, Windows 64 bit, also SSH tunnel

I switched to MysqlBench after I could not get my PHPStorm to hold tunneled connections alive. With MBench 6.3.6 a simple JOIN totally hangs the program. Made my day.

Confirm existence of this bug in latest Workbench version 6.3.6 64 bit.

Version 6.3.6 buid 511 CE (64 bits) continues with same bug.
Can't work through included ssh tunnel, nor through putty tunnelling

But it works correct in 6.2.5.0 build 397 (64 bits)

This problem is definitely around and i see many with this problem.
I have two servers, one with MySQL 5.5.43 and one with 5.6.29.
I work with different mysql workbench  versions and there is no problem working with MySQL 5.5.43. When working with 5.6.29 and SELECTING with LIMIT over 500 workbench hangs...

6.2.4.12437 Build 2426 (64 bit) hands
Latest build also hangs.

I realize this bug was closed but I encountered it today. Enabling compression fixed the issue, as was suggested by a previous comment.

6.3.6 511 64bit
Compression was always enabled and the bug exists.

working with a direct connection to the DB over 3306 with no problem.
Using a tunnel and working locally has this bug.

This bug is still happening on both Mac and Windows Workbench installations, even with the latest 6.3.7. Had to roll back to 6.1.7 to perform a very basic query. This should not have been closed.

did you make sure compression is active in your connection?

Good point--sorry I missed that suggestion in this thread. With compression for WAN enabled in my advanced connection settings I do not experience this problem anymore. Thanks!

I take that back. After working for a few minutes, the compression doesn't seem to fix this always. Queries are still hanging even with compression on. Windows 10 with Workbench 6.3.7.

For those still experiencing this issue, http://bugs.mysql.com/bug.php?id=79840 seems to be the same thing and hasn't been closed

Same problem with 6.3.7 on OSX, both El captain and Sierra
When ssh is used, 90% of the time query will hang on select if ssh tunnel is used to connect to MySQL. Problem exists with 5.6.30 and 5.7.14 servers. Does't depend on number of rows requested or packet size. Hangs with or without WAN compression.
This problem renders workbench practically unusable!!

We were experiencing this issue with direct connections to port 3306 and we fixed the issue by disabling SSL

On Ubuntu 16.04, Workbench hung whenever a did a select over an ssh connection. Almost regardless of query size.  When I installed additional software libraries required for vmware horizon client, the problems went away.  I don't know which software library fixed the issue, but by following the installs on this thread it fixed Workbench for me.  https://communities.vmware.com/thread/499473?start=0  note, you don't need to install horizon, just the extra libraries

I'm experiencing this issue as well, MySQL workbench 6.3.8 on macOS Sierra.

skip-name-resolve fixed this issue for me

6.3.9 fixed this issue for us. Note that you might have to go to the download page to find it, going to Help | Check for Updates said there were no new versions even though there is a newer version!