Problems with hex data in VARBINARY fields?

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 =
0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c728504 1bbb17652a0f20;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT HEX(`field`) FROM `table` WHERE `field` =
0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c728504 1bbb17652a0f20;
Empty set (0.00 sec)

mysql> SELECT HEX(`field`) FROM `table`;
+----------------------------------------------------------- -------------+
| HEX(`field`) |
+----------------------------------------------------------- -------------+
| DEE96318A69C8BA3208E1B2E91233725F5BB99A4708DF7AD367C7285041B BB17652A0F |
+----------------------------------------------------------- -------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(`field`) FROM `table` WHERE `field` =
0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c728504 1bbb17652a0f;
+----------------------------------------------------------- -------------+
| HEX(`field`) |
+----------------------------------------------------------- -------------+
| DEE96318A69C8BA3208E1B2E91233725F5BB99A4708DF7AD367C7285041B BB17652A0F |
+----------------------------------------------------------- -------------+
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 =
0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c728504 1bbb17652a0f20;
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/bugs?unsub=gcdmb-bugs@m.gmane.org

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 =
>0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c72850 41bbb17652a0f20;
>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` =
>0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c72850 41bbb17652a0f20;
>Empty set (0.00 sec)
>
>mysql> SELECT HEX(`field`) FROM `table`;
>+---------------------------------------------------------- --------------+
>| HEX(`field`) |
>+---------------------------------------------------------- --------------+
>| DEE96318A69C8BA3208E1B2E91233725F5BB99A4708DF7AD367C7285041B BB17652A0F |
>+---------------------------------------------------------- --------------+
>1 row in set (0.00 sec)
>
>mysql> SELECT HEX(`field`) FROM `table` WHERE `field` =
>0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c72850 41bbb17652a0f;
>+---------------------------------------------------------- --------------+
>| HEX(`field`) |
>+---------------------------------------------------------- --------------+
>| DEE96318A69C8BA3208E1B2E91233725F5BB99A4708DF7AD367C7285041B BB17652A0F |
>+---------------------------------------------------------- --------------+
>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 =
>0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c72850 41bbb17652a0f20;
>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/bugs?unsub=paul@mysql.com


--
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/bugs?unsub=gcdmb-bugs@m.gmane.org