RE: Hex data in VARBINARY fields -- Is it me, or MySQL?
On Friday, April 01, 2005 17:57, 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) > > 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) ...[snip]... > 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 You need to you a blob column type instead. Varbinary strips trailing spaces (0x20). Refer to the folowing page for further explanation. http://dev.mysql.com/doc/mysql/en/blob.html Quote from page: "There is no trailing-space removal for BLOB and TEXT columns when values are stored or retrieved. Before MySQL 5.0.3, this differs from VARBINARY and VARCHAR, for which trailing spaces are removed when values are stored." -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hex data in VARBINARY fields -- Is it me, or MySQL?
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) 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
