MySql generating the same UUID when INSERT INTO... SELECT

Question

I have this bug happening only on the production server (of course). I'm not able to replicate this on any machine.

Let's say I have to perform a query made like this

INSERT INTO my_table(UUID, Name, Surname) SELECT UUID(), Name, Surname FROM other_table

I expect to have a different UUID for any new generated row, that is exactly what happens in any environment. Except production.

There, if I run that query, MySQL generates all identical UUID. Looks like that, instead of generating a new one for every row, it runs the function only once and then replicates the value.

I'm pretty sure it's due to some kind of configuration, but I can't understand which one.

Also, it might not be strictly MySQL related: if I run the query from my PC but on the production server using SQLyog, the query works fine and generates all different UUIDs.

Could it be PHP fault? I'm currently using mysqli driver.

Just to make it clear, if I run this query from my PC

SELECT UUID()
FROM (SELECT 1 AS id UNION ALL
      SELECT 1 AS id UNION ALL
      SELECT 1 AS id UNION ALL
      SELECT 1 AS id UNION ALL
      SELECT 1 AS id UNION ALL
      SELECT 1 AS id UNION ALL
      SELECT 1 AS id UNION ALL
      SELECT 1 AS id UNION ALL
      SELECT 1 AS id UNION ALL
      SELECT 1 AS id
     ) i

the result is

bb79716f-f0ba-11e9-a154-f23c91fbfa4a
bb79717a-f0ba-11e9-a154-f23c91fbfa4a
bb79717c-f0ba-11e9-a154-f23c91fbfa4a
bb79717e-f0ba-11e9-a154-f23c91fbfa4a
bb79717f-f0ba-11e9-a154-f23c91fbfa4a
bb797181-f0ba-11e9-a154-f23c91fbfa4a
bb797183-f0ba-11e9-a154-f23c91fbfa4a
bb797185-f0ba-11e9-a154-f23c91fbfa4a
bb797187-f0ba-11e9-a154-f23c91fbfa4a
bb797189-f0ba-11e9-a154-f23c91fbfa4a

But if I run the same query using PHP on the server, the result is

0aef9b78-f0bc-11e9-bfe5-7a7919257c79
0aef9b78-f0bc-11e9-bfe5-7a7919257c79
0aef9b78-f0bc-11e9-bfe5-7a7919257c79
0aef9b78-f0bc-11e9-bfe5-7a7919257c79
0aef9b78-f0bc-11e9-bfe5-7a7919257c79
0aef9b78-f0bc-11e9-bfe5-7a7919257c79
0aef9b78-f0bc-11e9-bfe5-7a7919257c79
0aef9b78-f0bc-11e9-bfe5-7a7919257c79
0aef9b78-f0bc-11e9-bfe5-7a7919257c79
0aef9b78-f0bc-11e9-bfe5-7a7919257c79

tried in db-fiddle, its working as expected db-fiddle.com/#&togetherjs=h90C4RGw1L — Ed Bangga
– Ed Bangga, Commented Oct 17, 2019 at 9:01
MySQL 5.6.45-log PHP 5.6.40 (I know, I know, we are in the process of upgrading everything) — bimbo1989
– bimbo1989, Commented Oct 17, 2019 at 9:41

devaga · Accepted Answer · 2021-09-23 07:45:16Z

7

I stumbled upon same issue with MySQL and was trying to work with the solution proposed by @bimbo1989.

I spent a good hour trying different solutions, and it turns out, these UUIDs just looked the same, but actually, three characters were granting the uniqueness (6,7,8).

Make sure if the UUIDs are same or just look such.

answered Sep 23, 2021 at 7:45

devaga

4003 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bimbo1989 Over a year ago

In my case I'm 100% they were the same 😅

sushil Over a year ago

@devaga, good observation.... I spent almost half a day on this and finally after reading your reply I found different results as mentioned by you. Thanks a lot.

bimbo1989 · Accepted Answer · 2021-06-07 08:22:02Z

0

Not really the perfect answer to the question, but if anybody has the same problem it can be solved with a workaround. Just create a "new_uuid" function in MySQL, in use it instead of the built-in one. This way

CREATE FUNCTION new_uuid() RETURNS VARCHAR(36) CHARSET utf8 BEGIN   RETURN UUID();  END;

Then you can just use it instead of UUID()

SELECT new_uuid()

And it will correctly return different UUIDs in case you have multiple rows.

This is the only approach I got working to solve my issue. No idea why :)

answered Jun 7, 2021 at 8:22

bimbo1989

8421 gold badge10 silver badges22 bronze badges

Collectives™ on Stack Overflow

MySql generating the same UUID when INSERT INTO... SELECT

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related