I'd second this. UUID4 make it simpler to shard queries across instances. Since it's random I can say where uuid_column REGEXP ^[ab] and I know that I'm getting statistically 1/8th of the rows in my table. This becomes very handy when doing pagination stuff.

I would love to see this too. We often generated UUIDs in application logic (e.g. Java UUID.randomUUID()) and when doing database migrations we need to generate compatible UUIDs in MariaDB.

Agreed with this, it would pair nicely with the new UUID datatype. V5 could occasionally be handy too, but with a less high priority.

I don't think there's an ISO-SQL way of doing it, postgres implements it with a plugin uuid_generate_v1(), uuid_generate_v4(), uuid_generate_v5(). https://www.postgresql.org/docs/9.4/uuid-ossp.html

@chalbersma you could do something similar with v1. Not sure about where mariadb makes the cutoff, but most v1 implementations use random for parts of the third and/or fourth groups (https://www.uuidtools.com/img/version_1_diagram.png). Regex gets a bit more complex but it's possible to extract the random bits.

Copying my comment from MDEV-23748 which might get closed as a duplicate:

Keeping UUID() to be a UUIDv1 generator based on mac address seems necessary for MySQL compatibility.

As a random user, I'd vouch for implementing UUID generation functions the way that the postgres plugin does it https://www.postgresql.org/docs/13/uuid-ossp.html

- uuid_generate_v1() : UUIDv1 generator using real HW mac address (same as UUID() is currently for My/Maria)

- uuid_generate_v1mc(): UUIDv1 but use a randomly generated mac. It makes sense to me that this random mac should be saved as a global variable, for consistency if the DB is restarted or moved (unsure how PG handles this).

- uuid_generate_v3(namespace, name): UUIDv3, MD5 namespace

- uuid_generate_v4(): UUIDv4, full random UUID

- uuid_generate_v5(namespace, name): UUIDv5, SHA-1 namespace

In my opinion, uuid_generate_v4 and uuid_generate_v1mc would be the most useful to have. The mc version with the mac saved to a variable would nicely address circumstances where generating node needs to be tracked (so v4 isn't usable), but tracked to the database instance rather than the server it runs on.

This issue was created more than 6 years ago. Is there any update?

As a solid workaround until something official comes out, I created a UDF that adds functions matching the postgres uuid-osp signatures. It can generate v1 and v4 UUIDs, v1 with a randomized MAC address, validate UUIDs, and generate the namespace UUIDs (v3 & v5 are a work in progress).

Repo: https://github.com/pluots/udf-suite you can compile it yourself, or download the `.so`/`.dll` files from the releases page

@Trevor Gross Thanks for that, this is helpful and precisely what is required. I can't use it though, since I'm connecting to a server that hosts many different databases and where I'm not root or have any control of the server or the client for that matter.

It would be even better if you could branch off and work these functions into the mariadb codebase itself. Then a PR and hope someone will accept it.

Minor rant not aimed at you: When a Jira issue gets this old it should be closed with "Won't fix" because for all practical purposes, that is the defacto status. Six years without being accepted or rejected is frankly a disgrace.

I would like to work on it since it's unassigned.

Sure, please, do.

A question for users with practical use cases (I believe those two requested it in comments above could help with an answer): what would better fit your use case, multiple functions like UUIDv1(), UUIDv4(), UUIDv7(), etc or one function with an argument, like UUID(1), UUID(4), UUID(7)?

I personally suspect that the query like SELECT UUID(tbl1.ver) FROM tbl1 is rather artificial, but perhaps someone needs that?

> UUIDv1(), UUIDv4(), UUIDv7(), etc or one function with an argument, like UUID(1), UUID(4), UUID(7)

Honestly either one could work. The second seems to fit the patterns of other built in functions nominally better, but I think the primary use case for UUID generation is upon insertion of data, but I imagine the 3/5 types might be used on select logic.

elenst, can you please assign a tester for this feature? Thanks.

The patch is here:
https://github.com/MariaDB/server/pull/3143

It's in the contributor's repository for now. Should we create a stage branch in MariaDB repository, e.g. to get it tested by buildbots?

This task wasn't in 11.5 preview, so it missed the deadline for 11.5 release.

For 11.6 – yes, there should be a branch in MariaDB/server repo which should be tested by buildbots, and it should pass the buildbots (except for broken builders and old pre-existing unstable test failures) before it goes to testing.

Hi elenst,

please find the stage branch with UUIDv4 here:

https://github.com/MariaDB/server/tree/bb-11.5-bar-MDEV-11339

Buildbots
Thanks.

Note - will need MDEV-32583 like fix.

Applied MDEV-32583 fix to bb-11.5-bar-MDEV-11339

bb-11.7-MDEV-33710-uuid is ready for testing. It has both UUIDv4 and UUIDv7

Testing is done (bb-11.7-MDEV-33710-uuid e3dcdddea5fda111a3929de5e752f4944a1e6a9e), ok to push