MySQL Bugs: #77443: hangs returning a large result set from MySQL 5.7.4

Bug #77443	hangs returning a large result set from MySQL 5.7.4
Submitted:	23 Jun 2015 2:13	Modified:	16 Feb 2016 12:02
Reporter:	Michael Cannon	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S1 (Critical)
Version:	6.3.6.0 Build 511 64 bit	OS:	Windows (win 7 64 bit)
Assigned to:		CPU Architecture:	Any

Description:
Workbench 6.3.4 hangs indefinitely ("Running...") when attempting to query and return a result set that exceeds a certain size from a remote connection to a newly installed MySQL 5.7.4 server. Large result sets can be retrieved from the same server with Workbench 5.2. Workbench 6.3.4 will retrieve large result sets from several other servers (5.1 and 5.5) using the same type of connection (Standard TCP/IP over SSH). The server with 5.7.4 on it also has a 5.1.7 version on it. Large queries using Workbench 6.3.4 to the 5.1 version work. 

The other main connection parameters used were SSH port 7022, hostname 127.0.0.1 and MySQL port 3307. Other SQL editor functions (Create Table, Drop Table, Alter Table, Insert statements) seem to work fine. The Action pane only shows the message "Running..." and you cannot interrupt the query- aborting the query and clicking a few times anywhere results in a "Workbench Not Responding" message. The query will hang like this until you close the window.

How to repeat:
Use Workbench 6.3.4.0 to run queries on an ISAM database on MySQL server 5.7.4. The query operates normally if returning a small or empty dataset but will hang at "Running..." when there is an amount of data returned from the query past a certain threshold. In my tests a table with ~35 columns would hang if the result set contained more than 90 rows. A table with ~15 columns would hang if the result set contained more than 200 rows.

Thank you for the bug report. Are you tested the command client tool mysql.exe in similar conditions?. Thanks.

Yes. No problems with large result sets in the local command line client and no problems with large result sets in Workbench 5.2 using the same connection parameters.

I uninstalled Mysql 5.7.4 and installed Mysql 5.7.7 RC. Workbench 6.3.4 has the same problem with queries that return a dataset larger that a certain threshold. The same query on Workbench 5.2 works. Further information: These are remote queries connecting with Workbench on a win 7 x64 system with TCP/IP over SSH to a remote Linux Debian 6.0 system with Mysql 5.1 and Mysql 5.7.7. The internal connections are to 127.0.0.1.

This should help. The .cnf for the 5.7.7 server had the bind_address set to 127.0.0.1 (networking was through the SSH server and the connection was made to 127.0.0.1- not localhost). I changed the bind_address to 0.0.0.0 and the problem went away.

So... at this point I do not know if this is a bug in Workbench or in MySQL server, but it should be easy to find out now. If it is a bug in Workbench, it goes all the way back to version 6.0. It is more likely that it is a bug in the 5.7 Servers.... Should I make a bug report there?

Seems related to bug #76909.

Try version 6.3.5. Thanks.

I use the V6.3.5 and i have the same issue.
It works with SSH Tunnel with Vagrant VM on the localhost but freeze with remote server.

This bug #77443 still exists! I have upgraded to Workbench 6.36b511. Remote SSH connections to the Mysql Command Line Tool on the server work when retrieving a large result set. Application programs making remote connections work when retrieving a large result set. The only thing that does NOT work is Workbench when retrieving a large result set. 

The Mysql 5.7.7 server query log excerpt below shows the query being executed successfully when asked to return only 10 lines, but when asked to return 1000 lines, Workbench freezes after the query and the first of the performance queries is received by the server. Maybe the performance queries are the problem? Workbench 5.2 works, but uses different performance queries.

2016-02-03T19:46:58.956290Z  131 Query	SELECT * FROM order_head2 WHERE Order_Num + 0 <'40000' Order By Order_Num DESC
LIMIT 0, 10
2016-02-03T19:46:59.245017Z  130 Query	SELECT st.* FROM performance_schema.events_statements_current st JOIN performance_schema.threads thr ON thr.thread_id = st.thread_id WHERE thr.processlist_id = 131
2016-02-03T19:46:59.374646Z  130 Query	SELECT st.* FROM performance_schema.events_stages_history_long st WHERE st.nesting_event_id = 18
2016-02-03T19:46:59.474680Z  130 Query	SELECT st.* FROM performance_schema.events_waits_history_long st WHERE st.nesting_event_id = 18
2016-02-03T19:46:59.594866Z  130 Query	SHOW INDEX FROM `shopflo100001001`.`order_head2`
2016-02-03T19:47:15.365945Z  131 Query	SELECT * FROM order_head2 WHERE Order_Num + 0 <'40000' Order By Order_Num DESC
LIMIT 0, 1000
2016-02-03T19:47:15.647345Z  130 Query	SELECT st.* FROM performance_schema.events_statements_current st JOIN performance_schema.threads thr ON thr.thread_id = st.thread_id WHERE thr.processlist_id = 131
2016-02-03T19:49:47.324434Z  132 Connect	root@localhost on 

The last log entry was written upon restarting Workbench after the freeze.
Here are the results of the first performance query when run on a second instance of Workbench immediately after the first instance freezes:

# THREAD_ID, EVENT_ID, END_EVENT_ID, EVENT_NAME, SOURCE, TIMER_START, TIMER_END, TIMER_WAIT, LOCK_TIME, SQL_TEXT, DIGEST, DIGEST_TEXT, CURRENT_SCHEMA, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, OBJECT_INSTANCE_BEGIN, MYSQL_ERRNO, RETURNED_SQLSTATE, MESSAGE_TEXT, ERRORS, WARNINGS, ROWS_AFFECTED, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_DISK_TABLES, CREATED_TMP_TABLES, SELECT_FULL_JOIN, SELECT_FULL_RANGE_JOIN, SELECT_RANGE, SELECT_RANGE_CHECK, SELECT_SCAN, SORT_MERGE_PASSES, SORT_RANGE, SORT_ROWS, SORT_SCAN, NO_INDEX_USED, NO_GOOD_INDEX_USED, NESTING_EVENT_ID, NESTING_EVENT_TYPE, NESTING_EVENT_LEVEL
'163', '19', '19', 'statement/sql/select', 'socket_connection.cc:98', '492466478651735000', '492466556632036000', '77980301000', '303000000', 'SELECT * FROM order_head2 WHERE Order_Num + 0 <\'40000\' Order By Order_Num DESC\nLIMIT 0, 1000', '239a9c63ea4e3116622a3b2b94a41f55', 'SELECT * FROM `order_head2` WHERE `Order_Num` + ? < ? ORDER BY `Order_Num` DESC LIMIT ?, ... ', 'shopflo100001001', NULL, NULL, NULL, NULL, '0', NULL, NULL, '0', '316', '0', '1000', '12572', '0', '0', '0', '0', '0', '0', '1', '0', '0', '0', '0', '0', '0', NULL, NULL, '0'

It appears that the query is executed successfully and 1000 result lines are returned by the server. I also checked the performance schema for the first performance query and it appears that it also was executed successfully with one result line, but the subsequent performance queries were not sent by Workbench.

The only other possibly pertinent pieces of information that I have not reported are that the server OS is Debian 6.0, and no errors are logged by the server or Workbench. I will post any option or variable states if requested. I tried to monitor the network to acquire more information, but Workbench will not make or maintain an ssh connection when the network monitor is active...

Sure would be nice to have this working...

Thanks Michael for the feedback.
This seems to be duplicate of Bug #79840, please see Bug #79840

Thanks,
Umesh

I am also having this issue. In my case I need to return less then 200 rows on a database with 150 columns. Workbench stops responding and I have to kill it in the task manager. The exact same query works when returning less the 10 rows and when I grab it in PHP.