Inserting binary data results in "Data too long for column..."

user_15006555 0
Viewed 72

I have a MySQL table created with the following code:

CREATE TABLE `uuid` (
    `id` BINARY(16) NOT NULL,
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

I'm trying to insert a binary value to the id record.

If I do SELECT UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea44403293',1); it shows me Ossï{çÚõ§„FêD@2“

Now if I try to insert that output with this query INSERT INTO uuid VALUES ('Ossï{çÚõ§„FêD@2“'); I get SQL Error (1406): Data too long for column 'id' at row 1.

This question might seems wired but that is how I receive my binary data from the back-end and I'm trying to troubleshoot here. Any help why I'm getting that error please?

Thanks.

mysql
2 Answers

The reason for the length discrepancy is that some of the weird characters produced by your binary output are being encoded as UTF-8 characters, which take more than one byte per character.

For example, look at the first five characters, "Ossï{"

mysql> select hex(UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea44403293',1)) as hex;
+----------------------------------+
| hex                              |
+----------------------------------+
| 4F7373EF7BE7DAF5A78446EA44403293 |
+----------------------------------+
  O s s ï {

The byte EF is the Latin1 encoding for ï, so the nearest printable character to that byte is output as ï.

But then you copy & paste the string, and it gets converted to UTF-8 encoding, where ï has a multibyte sequence.

mysql> select hex('Ossï{çÚõ§„FêD@2“') as hex;
+------------------------------------------------------+
| hex                                                  |
+------------------------------------------------------+
| 4F7373C3AF7BC3A7C39AC3B5C2A7E2809E46C3AA444032E2809C |
+------------------------------------------------------+
  O s s ï   {

You can see that ï is encoded in UTF-8 as C3AF (two bytes), followed by the { as 7B.

There are several other multibyte characters in this string; I'm only calling attention to the first one.

When you copy & paste a string with funny non-ASCII characters, it's not as binary bytes, it's a string of UTF-8 characters, some of which are multibyte.

If you avoid copying & pasting the string, you can get the expected length of binary bytes:

mysql> SELECT LENGTH(UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea44403293',1)) as len;
+------+
| len  |
+------+
|   16 |
+------+

So use that when you insert into your table:

mysql> insert into uuid set id = 'Ossï{çÚõ§„FêD@2“';
ERROR 1406 (22001): Data too long for column 'id' at row 1

mysql> insert into uuid set id = UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea44403293',1);
Query OK, 1 row affected (0.00 sec)
Bill_Karwin_208600

If you perform those two queries on the command line, character set conversion might be a problem. You can insert the value directly:

INSERT INTO `uuid` VALUES (UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea444032
93',1));

or, if you want to have separate queries, convert it using e.g. hex and unhex:

SELECT hex(UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea44403293',1));

results in 4F7373EF7BE7DAF5A78446EA44403293.

INSERT INTO `uuid` VALUES (unhex('4F7373EF7BE7DAF5A78446EA44403293'));
user_196571830
Related