MySql UUID duplication BUG

Question

There is a bug that I found in MySql 5.5.19.

When executing:

select uuid(), uuid();

You are getting two equals ids.

I run in two this bug when inserted two uuids to my table, I always got same values.

Does anyone else run in two this bug? How can I perform insert command that requires two uuids for my keys?

Edit:

Actually I got wrong they are different in one digit so it was really hard to see

c3db9137-705e-11e1-ae17-1c6f6531b785
c3db913f-705e-11e1-ae17-1c6f6531b785

By the way, a hint: whenever you need to compare such long strings fastest way to do that is CTRL+C / CTRL+F (the Find option) — QuantumBlack, Aug 18 '18 at 08:31

score 11 · Answer 1 · edited Jul 27 '18 at 10:46

It is not uuid() function's bug

The unexpect result came out because there are inexplicit conversion happend when your charset client and charset result is not utf8;

1 uuid()output is utf8, no matter what your charset is ;

2 when your charset client and charset result is some of high priority than utf8 such as utf8mb4 , then the inexplicit conversion happen,other lower priority charset like latin1 that work fine;

3 inexplicit conversion will turn uuid() into constant string before sql execute,so after sql execute finish , the same uuid() return

you can see all these happend by using explain extended + your clause ,and then using show warnings;

how to solve ? 1 turn inexplicit convertion to explicit convertion for example : set names utf8mb4; replace( convert(uuid() using utf8mb4), '-','')

or

set names utf8mb4; replace( uuid(),_utf8'-',_utf8'')

2 aviod inexplicit convertion for example : set names utf8

enter link description here

This answer seems more relevant to https://stackoverflow.com/questions/45633863/mysql-generate-uuid-for-multiple-rows than to the current question, but it is a good answer. — J. David Beutel, Apr 17 '19 at 01:11
This may not have been the cause for this post's issue, but it was for mine (which uses an update): https://dba.stackexchange.com/a/278540/217576 So thank you :-) — Davi Cavalcanti, Oct 26 '20 at 23:40

QuantumBlack · Answer 2 · 2014-12-25T00:01:34.630

11

I strongly believe you are not getting duplicate values, but some almost-identical values (maybe 1 different character). Due to the fact that the UUID's first block is generated from the timestamp in milliseconds, it would mean the functions are executed in the same millisecond (are you running it on a super computer?), which, to be honest, is highly unlikely. If you are really getting duplicates, then run two separate SELECT uuid() queries and use the returned values in your desired query

edited Dec 25 '14 at 00:01

answered Mar 17 '12 at 14:06

QuantumBlack

1,549
11
27

Its common problem, I do not have super computers. – Abhishek Mar 14 '17 at 12:23
Supercomputer is a bit of an overstatement. I had a quite easily reproduceable case of MySQL generating duplicate UUIDs for a primary key field during fixture generation and loading on a 2015 model laptop. – Rimas Kudelis Aug 17 '18 at 19:07

score 6 · Accepted Answer · answered Mar 17 '12 at 14:01

6

From the docs A UUID is designed as a number that is globally unique in space and time. Since the query is compiled before being executed, you are calling UUID() twice at the same moment in time. Therefore, you cannot expect it to return two unique values in the same query since the same timestamp would be used for both values. This sounds like intended behavior to me.

answered Mar 17 '12 at 14:01

Michael Berkowski

267,341
46
444
390

11

Any ideas what I can do to make it unique in the same timestamp? – Ilya Gazman Mar 17 '12 at 14:09

score 3 · Answer 4 · answered Jul 15 '17 at 09:45

I have faced the same problem, since mysql is creating uuid_v1, there is not much to do but to use a different type of uuid. I have tried using uuid_v4 which generates uuid using random numbers. It works perfectly fine and you can also change back to uuid_v1 after you migrate to uuid, I hope it helps

-- Change delimiter so that the function body doesn't end the function 
declaration
DELIMITER //

CREATE FUNCTION uuid_v4()
    RETURNS CHAR(36)
BEGIN
    -- Generate 8 2-byte strings that we will combine into a UUIDv4
    SET @h1 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
    SET @h2 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
    SET @h3 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
    SET @h6 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
    SET @h7 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
    SET @h8 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');

    -- 4th section will start with a 4 indicating the version
    SET @h4 = CONCAT('4', LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'));

    -- 5th section first half-byte can only be 8, 9 A or B
    SET @h5 = CONCAT(HEX(FLOOR(RAND() * 4 + 8)),
                LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'));

    -- Build the complete UUID
    RETURN LOWER(CONCAT(
        @h1, @h2, '-', @h3, '-', @h4, '-', @h5, '-', @h6, @h7, @h8
    ));
END
//
-- Switch back the delimiter
DELIMITER ;

The code is taken from here

score 1 · Answer 5 · answered Dec 02 '20 at 08:45

1

It is a bug in MySQL 5.6 / 5.7 (corrected in 8.0). MariaDB has not this bug. bug is filled here : https://bugs.mysql.com/bug.php?id=101820 As explained by @user1862341 it's cause by inexplicit convertion, but that's still a bug

answered Dec 02 '20 at 08:45

Diego Dupin

1,106
8
9

score 0 · Answer 6 · answered Sep 23 '21 at 12:57

0

you can try it like this,it works for me

SELECT REPLACE(CAST(UUID() as char character set utf8), '-', '');

or

SELECT REPLACE(CAST(UUID() as char character set latin1), '-', '');

answered Sep 23 '21 at 12:57

yln snow

17
5

MySql UUID duplication BUG

6 Answers6

Linked