Save UUID as varbinary(16) in MySQL causes com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'ID' at row 1

eclipselink, jpa, mysql, uuid, varbinary

Solution

A `UUID` is a 128-bit number represented by a utf8 string of five hexadecimal numbers separated by hyphen( `'-'` ). Char length of the string returned by `UUID()` is `'36'`.

Hence column definition with `'16'` length is not sufficient. And when defined so, you will receive the said error.

mysql> create table tbl_so_q24028471_vb( v varbinary(16) );
Query OK, 0 rows affected (0.42 sec)

mysql> desc tbl_so_q24028471_vb;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| v     | varbinary(16) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.17 sec)

mysql> insert into tbl_so_q24028471_vb values( uuid() );
ERROR 1406 (22001): Data too long for column 'v' at row 1

Change the column definition to accommodate more length and use.

mysql> alter table tbl_so_q24028471_vb modify column v varbinary(36);
Query OK, 0 rows affected (0.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into tbl_so_q24028471_vb values( uuid() );
Query OK, 1 row affected (0.08 sec)

mysql> select * from tbl_so_q24028471_vb;
+--------------------------------------+
| v                                    |
+--------------------------------------+
| 630d3270-ebba-11e3-bd03-bc8556a95cc2 |
+--------------------------------------+

Problem

Original source