Posts

Showing posts from February, 2019

UUIDs in MySQL follow up

Last week my blog post on UUIDs in MySQL stirred a discussion on Hacker News.

I wrote the post in about half an hour and stupidly enough, I did not think about UUID v1. I knew about v1 (from the Melissa virus case), v4 and v3 & v5 (which I once used to generate deterministic UUIDs). I just didn't link my case to UUID v1 because I've hard-wired UUID with UUIDv4 in my head.

Here are two observations from the discussion on HN.

First: over time, the first characters of UUIDv1 are still uniformly distributed.



The only reason I had the same prefix was because I filled in all values with one UPDATE query when I initialized the column. If on the other hand you use UUID() whenever you create a new row, the first characters will be distributed evenly.

Second: the different UUID versions should be more clearly distinguished in general. The Zen of Python has a line:
"Explicit is better than implicit." In following this, I think it's better not to expose any UUID() functio…

UUIDs in MySQL are really not random

Universally Unique Identifiers (UUIDs) are great. I love how you can tell the progress of a batch job just by looking at the current UUID. If it starts with 0..., the task is less than 1/16th done. If it starts with 7d.., we're almost halfway there. At ff... we are nearing the end. The fact that you can tell this rests on two principles: 1) you sort your jobs by their uuid and 2) UUIDs are random, as in, distributed uniformly.

However, last week, I noticed a strange thing: a clearly visible pattern in the uuid column of a database table. It should be impossible, but there it was. It looked like this:

> SELECT uuid FROM example ORDER BY id;
4f95de28-0fd1-48db-ad2e-34ecd169c483
4331cb9e-1d91-11e9-be2c-45923c63e8a2
4331cc4c-1d91-11e9-be2c-45923c63e8a2
4331ccec-1d91-11e9-be2c-45923c63e8a2
4331cd7e-1d91-11e9-be2c-45923c63e8a2
c7e2f124-f6ba-4434-843f-89958a7436ec
4331ce10-1d91-11e9-be2c-45923c63e8a2
4331ce9e-1d91-11e9-be2c-45923c63e8a2
4331cf28-1d91-11e9-be2c-45923c63e8a2
4331cfaf-1d…