Re: Problems with hex data in VARBINARY fields?

At 16:59 -0700 4/1/05, Adam Wilson wrote:
>OK.... so...
>I'm having this problem where I'm trying to store (rather small
>(36-byte)) hex values in MySQL, but some of them end up getting
>truncated, therefore breaking my app... I'm using 4.1.10, with
>--default-table-type=InnoDB... Or what ever option that is....
>anyway... point is, all of these tables are InnoDB...
>
>Here's the problem......
>
>--------------------------------------------------------------------------------------------------------------------------------
>
>mysql> CREATE TABLE `table` (`field` VARBINARY(36) DEFAULT NULL);
>Query OK, 0 rows affected (0.01 sec)
>
>mysql> INSERT INTO `table` SET field =
>0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f20;
>Query OK, 1 row affected (0.00 sec)

The final byte is 0x20, that is, space.  As noted here, BINARY and VARBINARY
have the same trailing space handling as CHAR and VARCHAR:

http://dev.mysql.com/doc/mysql/en/binary-varbinary.html

As as noted here, trailing spaces are trimmed before MySQL 5.0.3:

http://dev.mysql.com/doc/mysql/en/char.html

You could use a BLOB column instead or upgrade to MySQL 5.0.3.

>
>mysql> SELECT HEX(`field`) FROM `table` WHERE `field` =
>0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f20;
>Empty set (0.00 sec)
>
>mysql> SELECT HEX(`field`) FROM `table`;
>+------------------------------------------------------------------------+
>| HEX(`field`)                                                           |
>+------------------------------------------------------------------------+
>| DEE96318A69C8BA3208E1B2E91233725F5BB99A4708DF7AD367C7285041BBB17652A0F |
>+------------------------------------------------------------------------+
>1 row in set (0.00 sec)
>
>mysql> SELECT HEX(`field`) FROM `table` WHERE `field` =
>0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f;
>+------------------------------------------------------------------------+
>| HEX(`field`)                                                           |
>+------------------------------------------------------------------------+
>| DEE96318A69C8BA3208E1B2E91233725F5BB99A4708DF7AD367C7285041BBB17652A0F |
>+------------------------------------------------------------------------+
>1 row in set (0.00 sec)
>
>--------------------------------------------------------------------------------------------------------------------------------
>
>AND... to make things worse.... I have a unique index on the column,
>so it breaks even more....
>
>--------------------------------------------------------------------------------------------------------------------------------
>
>mysql> CREATE UNIQUE INDEX `index` ON `table` (`field`);
>Query OK, 1 row affected (0.02 sec)
>Records: 1  Duplicates: 0  Warnings: 0
>
>mysql> INSERT INTO `table` SET field =
>0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f20;
>ERROR 1062 (23000): Duplicate entry '??c???? ?#7%????p???6|r?e*' for key 1
>
>--------------------------------------------------------------------------------------------------------------------------------
>
>I'd appreciate any Ideas anyone has any ideas/suggestions... I'd hate
>to unnecessarily submit a bug report, if it's something on my end.....
>
>
>THANKS!!!
>
>--Adam
>
>--
>MySQL Bugs Mailing List
>For list archives: http://lists.mysql.com/bugs
>To unsubscribe:    http://lists.mysql.com/[email protected]


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]