MySQL Bugs: #55209: UUID() generates the same value if it is run in the same time

Bug #55209	UUID() generates the same value if it is run in the same time
Submitted:	13 Jul 2010 13:17	Modified:	5 Aug 2011 17:23
Reporter:	Libing Song	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1, all	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	5.1

Description:
UUID is made of time and MAC address, so it is passable to generate the same UUID if two user call UUID() at the same time, the MAC address is always same for the same server.

How to repeat:
After WL#4677, each server generates a UUID at the first time launch.  The failure 'master and slave have equal MySQL server UUIDs' happens sporadically on mysql-5.1-rep+2 and mysql-5.1-rep+3 tree.

Davi Arnaut checked the code and he thinks there are some problems for initializing random seeds.

Suggested fix:
Mix some other seeds into the UUID. example: PID, Thread id etc.

Thank you for the report.

Verified as described:

mysql> select UUID(), UUID();
+--------------------------------------+--------------------------------------+
| UUID()                               | UUID()                               |
+--------------------------------------+--------------------------------------+
| dc788736-8e82-11df-9d5d-2b667d43d871 | dc788ac4-8e82-11df-9d5d-2b667d43d871 |
+--------------------------------------+--------------------------------------+
1 row in set (0.00 sec)

It seems we use time based UUIDs as they can be better indexed in some cases, but on the other hand, the server might resort to random based UUID if the MAC address can't be used for some reason. In the time based case, the documentation should be updated to reflect there is a probability of collisions if multiple instances of the server are on a single machine.

Nonetheless, I would vote to always make UUID() be random based and introduce a new function or parameter indicating whether a time based UUID is wanted. For new stuff, we should move away from this method of UUID generation.

Oops! My test is wrong. Assigning to myself.

Thank you for the report.

My test was wrong: dc788736 is different from dc788ac4. Currently I am doubt this can be repeatable: there is static variable uuid_time which ensures timestamp part is always different and there is a mutex LOCK_uuid_generator which locks any access to uuid_time.

Do you have any example or analysis which can prove same UUID can be repeatable?

As internally discussed case still can be repeatable if 2 servers run on same machine same time: UUID generated from thd, global_query_id, server_start_time, bytes_sent which can be same on 2 servers if these run on same box same time. Something like PID should be also added to this function.

Hello!

I am having the same issue running version 5.5.22.
I can't get the MySQL monitor to work properly. I have two instance on the same machine. They endup with the same uuid. I had it working before on version 5.1.40

Hassan

Please discard my previous comment. It was related to MEM. The mysql monitor agent generate a uuid for the server and stores it in mysql.invetory table. since I cloned my server instance from a mysqlbump of the other server, both have the same inventory table. The solution was to trancat the mysql.inventory table and resatrt the mysql monitor agent. Sorry this is not related to this bug issue.

While possible in theory it should hardly ever be an issue in real life, with the timer part of MAC/time based UUIDs being at 100ns resolution. Not sure whether the UUID generator within mysqld has access to a time source that accurate though, but there are a few extra bits reserved for if two UUIDs are requested within the same 100ns interval, instead of using them as a sequence pointer it would also be possible to put some random or PID based value in there to make collisions even less likely ...