MySQL Bugs: #53920: MySQL WB 5.2.21 OSS Remote Connection returns BLOB where none exists

Bug #53920	MySQL WB 5.2.21 OSS Remote Connection returns BLOB where none exists
Submitted:	23 May 2010 4:00	Modified:	4 Jun 2010 14:59
Reporter:	Thomas Ferraro	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.21 Rev 5918	OS:	Windows (XP)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	BLOB, Remote Connection

Description:
SQL Select Output from Remote Connection using both MySql Workbench 
(Truncated for brevity) 

Month	Credit	Debit
'April 2010', BLOB, BLOB
'March 2010', BLOB, BLOB
'February 2010', BLOB, BLOB
'January 2010', BLOB, BLOB
...

How to repeat:
This is not version specific.... but it is related to MySql... but I also get the feeling that it would or could happen in other databases. I have tested this on MySql versions 5.1 through 5.5 running on XP and connected via both Query Analyzer on the console as well as PHP code running on IIS.

When I write and execute code on the console of my database server I get this and other similar queries to work quite well.... but when I execute the same sql code on a remote connection (currently using My Sql Workbench 5.22 but also tested on other tools such as OOBase and I get something funny.... I hope someone can shed some light on this...

SQL Code
select date_format(`mwac`.`transaction`.`Date`,_utf8'%M %Y') AS `Month`,concat(_utf8'$',sum(`mwac`.`transaction`.`Credit`)) AS `Credit`,concat(_utf8'$',sum(`mwac`.`transaction`.`Debit`)) AS `Debit` from `mwac`.`transaction` group by monthname(`mwac`.`transaction`.`Date`),(((year(`mwac`.`transaction`.`Date`) * 12) + month(`mwac`.`transaction`.`Date`)) - 1) having (sum(`mwac`.`transaction`.`Debit`) > 0) order by (((year(`mwac`.`transaction`.`Date`) * 12) + month(`mwac`.`transaction`.`Date`)) - 1) desc

SQL Select Output from Console (Truncated for brevity)

Month	Credit	Debit
Apr-10	$1,600.00 $1,714.08 
Mar-10	$2,400.00 $2,505.11 
Feb-10	$1,600.00 $1,823.80 
Jan-10	$1,013.34 $1,579.75 
...

SQL Select Output from Remote Connection using both MySql Workbench and Open Office Base (Truncated for brevity) In OOB BLOB is listed as <OBJECT> but the result is the same.

Month	Credit	Debit
'April 2010', BLOB, BLOB
'March 2010', BLOB, BLOB
'February 2010', BLOB, BLOB
'January 2010', BLOB, BLOB
...

If I create a view on the MySql Server using the above code, even though the table column definitions are correct, MySql Workbench sees the columns in the view as having the definition of VarChar, VarBinary, VarBinary instead of DATETIME, Integer, Integer respectively.

Now this evening I tested this query on Excel 2010 using MS Query through an ODBC connection and I get the same data as being on the console

SQL Select Output from Console (Truncated for brevity)

Month	Credit	Debit
Apr-10	$1,600.00 $1,714.08 
Mar-10	$2,400.00 $2,505.11 
Feb-10	$1,600.00 $1,823.80 
Jan-10	$1,013.34 $1,579.75 
...

So why am I seeing blobs where the the numeric sums are suppose to be on MySql Workbench and OOB.... this only happens on the remote connections through TCP/IP and not on the console or in the output of any web pages I write or MS Excel using MSQuery through ODBC. 

I figure that is because those pages are executing code on the server and sending the result... but why am I seeing blobs through remote connection and how do I fix that?

Please, send the results of:

show create table `mwac`.`transaction`\G

What exact version of MySQL server are you working with?

MySql Version is: 5.5.3

Mwac.Transaction Table Create Script is:

CREATE TABLE `transaction` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Date` datetime DEFAULT NULL,
  `Type` varchar(255) DEFAULT NULL,
  `Description` varchar(255) DEFAULT NULL,
  `Debit` decimal(18,2) DEFAULT NULL,
  `Credit` decimal(18,2) DEFAULT NULL,
  `Balance` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`)
);

Looks like these tools can not distinguish VAR_STRING column with binary collation and BINARY flag from real BLOBs:

mysql> select date_format(`transaction`.`Date`,_utf8'%M %Y') AS
    -> `Month`,concat(_utf8'$',sum(`transaction`.`Credit`)) AS
    -> `Credit`,concat(_utf8'$',sum(`transaction`.`Debit`)) AS `Debit` from
    -> `transaction` group by
    -> monthname(`transaction`.`Date`),(((year(`transaction`.`Date`) * 12) +
    -> month(`transaction`.`Date`)) - 1) having (sum(`transaction`.`Debit`) > 0)

    -> order by (((year(`transaction`.`Date`) * 12) + month(`transaction`.`Date`
))
    -> - 1) desc
    -> ;
Field   1:  `Month`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     207
Max_length: 0
Decimals:   0
Flags:

Field   2:  `Credit`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     43
Max_length: 0
Decimals:   31
Flags:      BINARY

Field   3:  `Debit`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     43
Max_length: 0
Decimals:   31
Flags:      BINARY

0 rows in set (0.06 sec)

mysql> create table tblob(c1 blob);
Query OK, 0 rows affected (0.13 sec)

mysql> select * from tblob;
Field   1:  `c1`
Catalog:    `def`
Database:   `test`
Table:      `tblob`
Org_table:  `tblob`
Type:       BLOB
Collation:  binary (63)
Length:     65535
Max_length: 0
Decimals:   0
Flags:      BLOB BINARY

0 rows in set (0.00 sec)

And yes, I confirm that columns in your query are marked as BLOB by Workbench 5.2.21 on Windows XP SP3. This is a bug in Workbench.

Bug #54032 was marked as a duplicate of this one.

Bug #54038 was marked as a duplicate of this one.

This is not a bug in WB. As I commented earlier for bug report #53335, "VARBINARY" and "VARCHAR BINARY" are 2 different types. For details please refer to http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html
Since binary byte strings tend to contain zero-bytes in their values, for safety reasons they are not shown in results grid and allowed to be viewed/edited only by means of BLOB editor to avoid data truncation when they are being viewed or edited (see bug #52954 for precedent). In reported case CONCAT returns VARBINARY value because not all arguments are nonbinary character strings. Details here: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_concat
This can be resolved using CAST function for arguments that need it.

OTOH according to this and other reports the current state of things makes inconveniences in other ways. To resolve this contradiction the new global option was introduced, which determines whether to treat BINARY/VARBINARY values as nonbinary character strings. And I tend to set its default value to 'false'.

Sergei Tkachenko wrote on 28 May 10:40:
... To resolve this contradiction the new global option was introduced, which determines whether to treat BINARY/VARBINARY values as nonbinary character strings. And I tend to set its default value to 'false'.

I assume this is a WB option?  Where can it be set?  In which WB version does it exist?

This option will become available in v5.2.22.
You will be able to set it via Menu->Edit->Preferences form, SQL Editor settings page.

fix confirmed in repository.
option: "Treat BINARY/VARBINARY as nonbinary character string" has been added to app Preferences "SQL Editor" section

An entry has been added to the 5.2.22 changelog:

In the SQL Editor, performing a query on a remote server returned BLOB values for columns that were not of type BLOB.

Since binary byte strings tend to contain null bytes in their values, for safety reasons they were not displayed in the results grid. They could only be viewed or edited by means of the BLOB editor to avoid data truncation.

MySQL Workbench has been changed to include a new global option which has been added to the SQL Editor tab of the Workbench Preferences dialog. The option is “Treat BINARY/VARBINARY as non-binary character string”. By default it is not selected.

Bug #54353 was marked as a duplicate of this one.

Using MySQL Workbench 5.2.47 CE. In fact, the mentioned option is in the SQL Queries tab.

Its not about the option existing, its about the change you made that you should not have... the option need not exist... the original problem was that the client was treating the result set as a blob in computed columns.

MySql Query Browser does not do this, MySql Command Center did not do this, Get external data from MS Excel does not do this.  Only MySql does treat computed column and computed value queries as blobs when it shouldn't be doing so.  That was the intent of the original bug report but I can't seem to get anyone to pay attention to the most significant part of this whole thing!

You put a broken fix on a broken database function... you still have it wrong!!!