Missing/incomplete support for Binary TYPES

[atulwy]

Original issue 15 created by webyog on 2006-09-07T22:30:41.000Z:

There are several issues:

1) BLOB data: the BLOB has no option to display/edit binary data that are not IMAGE.

2) MySQL version 5 BIT TYPE not supported

3) BINARY and VARBINARY implemetation is 'poor' - it actually only supports CHARACTER DATA - and why then use a (VAR)BINARY? Maybe current implementation is 'reasonable' with MySQL 3.23 but absolutely not later versions!

4) All binary types have problems with DEFAULTs that are not printable.
applies to more areas of the program (CREATE/ALTER TABLE, sync tools)

[atulwy]

Comment #1 originally posted by webyog on 2006-09-08T05:47:00.000Z:

edited title

[atulwy]

Comment #2 originally posted by webyog on 2007-12-13T11:53:50.000Z:

came across the bug report with MySQL Query Browser: http://bugs.mysql.com/bug.php?id=33196

also no good with SQLyog!

[atulwy]

Comment #3 originally posted by webyog on 2008-10-13T19:11:43.000Z:

also a user requests (in a privat ticket) in CSV export/import an option to specify a non-printable character using 'b_' or 'x_* notation

[atulwy]

Comment #4 originally posted by webyog on 2008-10-30T11:52:53.000Z:

also see http://code.google.com/p/sqlyog/issues/detail?id=906

[atulwy]

Comment #5 originally posted by webyog on 2009-03-09T14:11:33.000Z:

one more discussion came here: http://www.webyog.com/forums//index.php?showtopic=4541

[atulwy]

Comment #6 originally posted by webyog on 2010-02-11T07:29:16.000Z:

Issue 1209 marked as duplicate of this

[atulwy]

Comment #7 originally posted by webyog on 2010-05-27T14:42:39.000Z:

Some of our schemas use BIT(1) to represent true/false. If nothing else, it'd be good if SQLyog supported this usage and showed '0' or '1', rather than '(Binary/Image)' or a small square.

[atulwy]

Comment #8 originally posted by webyog on 2010-10-25T13:26:08.000Z:

Also see issue # 1537

[atulwy]

Comment #9 originally posted by webyog on 2011-10-16T12:09:35.000Z:

2) is now fixed

[atulwy]

Comment #10 originally posted by webyog on 2011-11-11T08:38:49.000Z:

One more issue found (test case from Workbench bug report http://bugs.mysql.com/bug.php?id=63198).

CREATE TABLE customer ( uid BINARY(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', address VARCHAR(127) NOT NULL, htmlemail TINYINT(1) NOT NULL DEFAULT '1', PRIMARY KEY (uid) ); INSERT INTO customer VALUES (X'4B932678B24D21A851434D3EC31D6BFD', 'b', 7);

First observe that it displays as garbage.

Next try to UPDATE from grid. Enter x'abc' in uid column and syntax error 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'x'abc' where uid='K?&x?M!?QCM>?k?'' at line 1

The solutions were proposed

UPDATE `mydb`.`customer`
SET `htmlemail`=1
WHERE `uid`=unhex('4B932678B24D21A851434D3EC31D6BFD');

or

UPDATE `mydb`.`customer`
SET `htmlemail`=1
WHERE `uid`=CAST(0x4B932678B24D21A851434D3EC31D6BFD AS BINARY);

[atulwy]

Comment #11 originally posted by webyog on 2011-12-16T10:01:25.000Z:

One more reason why at least BINARY tyeps should be displayed in x'..' notation is

MySQL docs: http://dev.mysql.com/doc/refman/5.5/en/binary-varbinary.html

"When BINARY values are stored, they are right-padded with the pad value to the specified length. The pad value is 0x00 (the zero byte). Values are right-padded with 0x00 on insert, and no trailing bytes are removed on select. All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 bytes and spaces are different in comparisons, with 0x00 < space."

SQLyog docs:

"UPDATE a row of data from DATA or RESULT tab if any of the columns displayed for the actual row contains data identified as binary data by containing a NULL byte ('\0' in C-notation) no matter whether this occurs for binary datatypes (binary, varbinary, BLOB) or string types (char, varchar, TEXT). Instead an error message will print (reason for this: the UPDATE operation would store what was displayed - not the underlying binary data causing the display)."

.. so currently a BINARY result is not updatable unless the data-length for the column is same as the column-length in table definition.