Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Workbench shows results as BLOB

I keep finding that MySQL Workbench shows query results as BLOB. e.g: SELECT INET_NTOA(167773449) --> BLOB

If I select to 'view value' I can determine the text value is '10.0.5.9' but it's pretty irritating when I SELECT multiple rows and want to glance at the contents.

Is there a way around this or is it a limitation of the tool?

like image 584
Mr. Boy Avatar asked Nov 29 '12 20:11

Mr. Boy


People also ask

What does BLOB mean in MySQL?

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB , BLOB , MEDIUMBLOB , and LONGBLOB . These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT , TEXT , MEDIUMTEXT , and LONGTEXT .

How can I change BLOB data in MySQL?

BLOB Editor is invoked from data grid of any table editor or the result tab of SQL Editor and Visual Query Builder by double clicking of the BLOB field to be edited or with the Edit BLOB link of the field's popup menu. The editor also can be called from BLOB Viewer with the Edit current BLOB button.


2 Answers

Background: This problem occurs when the binary string values (BINARY/VARBINARY type) are returned in the results. The binary strings contain the zero bytes and for some reason, apparently security, have not been shown by default. More details about binary strings here.

Even in the reported example SELECT INET_NTOA(167773449), the function returns binary string. Check this for reference.

Solution: Since MySQL Workbench v5.2.22, it can be set through preferences whether to SHOW or HIDE such values.

  1. In MySQL Workbench, go to: "Edit -> Preferences... -> SQL Queries" OR "Edit -> Preferences... -> SQL Editor -> SQL Execution" (depending upon what version of Workbench you have).
  2. Check the option 'Treat BINARY/VARBINARY as nonbinary character string' to show the actual value.

Reference: The original issue has been reported and answered with fix here.

like image 109
Tariq Avatar answered Sep 27 '22 17:09

Tariq


What you can do is Cast your BLOB type to a string. This will simply allow you to glance at whats in your BLOB type when browsing your select statement.

SELECT CAST(`blob_column_name` AS CHAR(10000) CHARACTER SET utf8) FROM `table_name`; 
like image 38
Sweet Chilly Philly Avatar answered Sep 27 '22 19:09

Sweet Chilly Philly