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

Question

I have a field id defined as below. It's varbinary(16) in database, when i am inserting a new record through JPA, i got "com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'ID' at row 1". What am I doing wrong?

@Id
@Column(name="ID")
private UUID id;

score 3 · Accepted Answer · answered Jun 04 '14 at 07:35

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 |
+--------------------------------------+

I altered ID to varbinary(36), but still having the same issue. BTW, i am using UUID.randomUUID() in java to generate my UUID. Does it make any different? — topcan5, Jun 04 '14 at 16:33
No. It won't make any difference. `String` form of Java `UUID` too is of length `'36'`. If you want to save the same in database, pass it as a `'String'`. Example: `pst.setString( fieldNumber, randomUuid.toString() );` — Ravinder Reddy, Jun 04 '14 at 16:57

score 2 · Answer 2 · answered Nov 18 '14 at 20:34

Your trying to store a 36 character string in a 16 byte space. That's not going to work.

You need get at the bits behind the UUID, which will fit in a binary(16). Since you're using Java, you can do something like:

ByteBuffer bb = ByteBuffer.wrap(new byte[16]);
bb.putLong(uuid.getMostSignificantBits());
bb.putLong(uuid.getLeastSignificantBits());
return bb.array();

and then insert the resulting byte array into the DB.

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

2 Answers2