Why are my GUIDs too short?

Imagine my database table looked like this in MySQL:

CREATE TABLE foo (
  id BINARY(16)
);

Pretty simple, right? The id column stored v4 GUIDs, which are always 16 bytes in length. Yet, when I SELECT‘d some of the GUIDs from the table I discovered one of the GUIDs did not look like the others:

mysql> SELECT hex(id) FROM foo LIMIT 5;
+----------------------------------+
| hex(id)                          |
+----------------------------------+
| 4859d76c08bc6f4db54e0c7bb73a869b |
| dcf0c7901f68c04cb36b5a3c1e7a71ca |
| 4be84c3b1f3dab44b57f7a010e4325   | <-- Wha?
| 243372fedfc8584e9756c6df8522c995 |
| a6551d443660504f898f996274a0edde |
+----------------------------------+

The third GUID, 4be84c3b1f3dab44b57f7a010e4325, only had 15 bytes. One byte was missing! How could that be?

My first hypothesis was that my GUID generator was failing to generate GUIDs of the correct length. All GUIDs were generated by boost::uuids::random_generator. I was new to using that library so it was possible I was not using it correctly. Internally, the random generator was relying on the mt19937 variant of boost::random::mersenne_twister to actually generate the random numbers. I re-read the documentation for both libraries and confirmed my code was invoking them correctly according to the documentation.

Seeing no obvious problems in the code, I enabled verbose logging in my application to see if I could catch it in the act of generating a bad GUID. Over the course of a day, I exercised the relevant program behaviors to force it to generate several GUIDs. At the end of the day I saw thousands of GUIDs had been generated in the log files. Unfortunately, all of them had 16 bytes. Bummer!

Thinking I had failed to reproduce the problem, I queried my database table again. This time I saw that many of the GUIDs from the log files were missing bytes in the database. Aha! I actually had reproduced the problem but not in the way I had expected.

That’s when I realized my initial hypothesis was wrong. The GUID generator was working correctly, but GUIDs were losing bytes somewhere between when they were generated and when they were stored in the database. I quickly checked a different set of logs containing all the queries that had inserted data into the database that day. Fortunately, all query parameter values were logged next to the actual SQL.

Once again, I was surprised to see all GUIDs sent to the database had exactly 16 bytes. So if the GUIDs were generated correctly and never lost bytes on their way to the database, then when and where were they losing bytes?

It was the end of the day so I decided to sleep on it.

The next morning, I decided to see if there were any similarities between the GUIDs that had lost bytes. I compared the GUIDs from the query log files to the GUIDs in the database and noticed a pattern. All of the GUIDs that had lost bytes were losing bytes at the end of the GUID. For example, the query log file showed a GUID like this was inserted into the database:

567dc7956ac04cd4aa3dfc379590e420

And the database showed this was the actual GUID that was stored:

567dc7956ac04cd4aa3dfc379590e4

Notice the 0x20 byte at the end was missing. In fact, all short GUIDs in the database were missing a 0x20 byte at the end. Or at least that’s what I thought until I saw one GUID with two bytes missing! The query log file showed this GUID was inserted:

b72a5fb0c15e467d855d6a4c58ec2020

And the database showed this was the GUID that was actually stored:

b72a5fb0c15e467d855d6a4c58ec

Notice the two 0x20 bytes at the end were missing. Armed with some test data, I decided to see if this problem was reproducible in the database. I ran this query to insert one of the problematic GUIDs into the database:

mysql>INSERT INTO foo VALUES (0xb72a5fb0c15e467d855d6a4c58ec2020);
Query OK, 1 row affected (0.00 sec)

And then I SELECT‘d the GUID back out:

mysql>SELECT hex(id) FROM foo ...;
+------------------------------+
| hex(id)                      |
+------------------------------+
| b72a5fb0c15e467d855d6a4c58ec |
+------------------------------+

GOTCHA! The last two 0x20 bytes were missing! This showed there was something wrong with how MySQL was storing the GUIDs. After some quick googling that included various combinations of the terms “truncate”, “binary” and “mysql”, I found this bug report: Trailing spaces and BINARY. This comment in that bug report explained the problem:

Another part of this problem is trailing spaces truncation in a BINARY column:

When inserting a binary record as I have listed below, the last hex value ‘20’ is dropped when the data is returned. If you run the sql in the ‘How to repeat’ section, you will see what I mean.

create table Test ( t1 char(2) binary ); insert into Test ( t1 ) values ( Cast(0x4020 as binary) ); insert into Test ( t1 ) values ( Cast(0x4002 as binary) ); insert into Test ( t1 ) values ( Cast(0x2002 as binary) ); insert into Test ( t1 ) values ( Cast(0x1020 as binary) ); select t1, Hex(t1) from test;

See http://bugs.mysql.com/bug.php?id=8688 for more details

The mystery was solved! MySQL was silently truncating trailing spaces on all binary data inserted into the database. The bug was already fixed in MySQL 5.0.15. Unfortunately, due to a variety of reasons I was unable to upgrade MySQL.

So how could I fix the problem?

Sometimes simple problems require simple solutions. If MySQL couldn’t store GUIDs that ended in 0x20 then I would make sure the code never generated a GUID that ended in 0x20. Inside of the GUID generator function where I called boost::uuids::random_generator I added a check to see if the last byte in the GUID was 0x20. If it was, then I simply generated another GUID. This process repeated indefinitely until a valid-for-MySQL GUID was generated. The performance concerns from generating an extra GUID every now and then were negligible, and this solution preserved as much entropy in the GUIDs as possible.