MySQL Bugs: #74446: Query returning any BINARY column crashes (all subsequent queries hang forever)

Bug #74446	Query returning any BINARY column crashes (all subsequent queries hang forever)
Submitted:	20 Oct 2014 8:25	Modified:	13 Mar 2015 23:21
Reporter:	Martin Lui	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	6.2.3 and beyond	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
Query table with BINARY field crashes (all subsequent queries hang/spin forever)

When querying a table that has a BINARY column, the first query will return with a valid response in the "Action Output" section.  For example, it will show query successful, duration (0.000 sec in my test case) and "X row(s) returned" in the "Response" column.  However, nothing renders in the Result Grid.

Any other future queries, including re-running the same query above, will not return.  Instead, it will hang forever with the spinning in-progress icon next to the query tab name.  Expanding other schemas will also hang with "Tables fetching...", "Views fetching...", etc.  

Only a "Quit" of the application can make the it functional again, obviously for queries that does NOT return binary columns.

Only workaround found is to manually write a query that does NOT return the binary column or returns that column with it wrapped in HEX(column_name).

How to repeat:
1.  Create a test table:

CREATE TABLE `test`.`new_table` (
  `id` INT NOT NULL,
  `bin` BINARY(16) NOT NULL,
  PRIMARY KEY (`id`));

2.  Insert some test data, such as a UUID in the 'bin' column.

3.  Query that table, either by right-click on the table and select "Select Rows - Limit 1000" or manually writing "SELECT * FROM test.new_table" in a new Query tab.

Suggested fix:
Query should return results in the Result Grid as expected...

FYI:  Only things found in log files indicates a success.  No error messages.  For example:

[2, 01:29:20] SELECT * FROM new_table
LIMIT 0, 1000
: Running...
[2, 01:29:20] SELECT * FROM new_table
LIMIT 0, 1000
: Fetching...
[2, 01:29:20] SELECT * FROM new_table
LIMIT 0, 1000
: 1 row(s) returned
[3, 01:29:41] SELECT * FROM new_table
LIMIT 0, 1000
: Running...
[3, 01:29:41] SELECT * FROM new_table
LIMIT 0, 1000
: Fetching...
[3, 01:29:41] SELECT * FROM new_table
LIMIT 0, 1000
: 1 row(s) returned

Sorry, forgot to include:  Nothing new (since application startup) gets appended to wb.log upon executing the problematic query.

Thank you for the bug report. I couldn't repeat inserting uuid values in column bin running on Yosemite OS X.

select result

Attachment: 0bin.png (image/png, text), 204.50 KiB.

Hi,

Sorry for the late reply.  I just tested again after updating to 6.2.4 and still the same problem.  

What data do you add to the binary column?

When I did this (ignoring my change in table name):
INSERT INTO test.bug_binary_field VALUES (1, UNHEX('4e'));

A SELECT * FROM test.bug_binary_field returns fine.  But once I do:
INSERT INTO test.bug_binary_field VALUES (2, UNHEX('ad8b0751'));
subsequent selects will not return (or render in the UI).

To be clear, the 2nd time I do the select, the Action Output section does show "2 row(s) returned".  But the result grid would not render or update (in my case, the one row from my first select).  Trying to rerun the query again will cause a spinning icon to appear on the query tab, which will spin until I quit the application.  

I'll attach a screenshot too.  Let me know if there are more things I can do to send you more information (logs, etc.).

Screenshot of issue on MySQL Workbench 6.2.4

Attachment: MySQL Workbench Binary Column Query Bug.png (image/png, text), 856.03 KiB.

Small updates.  Did more testing since there has been no response here.  I tested MySQL Workbench 6.3.2 rc, and verified this problem exists.

However, I did find the uninstall instructions, which listed the location of the application data files.  (Ref:  http://dev.mysql.com/doc/workbench/en/wb-mac-uninstalling.html)

I've found that deleting the ~username/Library/Application Support/MySQL/Workbench directory solved my issue in 6.2.4.  Knowing that, through process of elimination, found that deleting wb_options.xml was enough to resolve this problem.  

At this point, I suspect the issue is a backwards incompatible change in 6.2.3 that caused old versions of wb_options.xml to not work.  In my case, I upgraded from 6.1.6 to 6.2.3.

This has been a problem for me in both 6.3.9 and 6.3.10. Deleting wb_options.xml workaround did not fix it for me.

This is a blocker.

Working back on Martin Lui's suggestion, deleting wb_options.xml and wb_state.xml fixed it for me. Of note, 6.3.9 was the first version I installed on my machine.

Last update.

As soon as I checked "Treat BINARY/VARBINARY as nonbinary character string" problem came back. When I uncheck it again, queries work again.

Please fix.

I'm also experiencing exactly the same issue

I am having the same issue, any suggestions for fixing it or ignoring those columns that are binary when doing SELECT?