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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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