[MDEV-32637] Implement native UUID7 function - Jira

Details

Type: New Feature
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 11.7.1
Component/s: Server
Labels:

Description

MariaDB provides the UUID method that generates UUID v4, however UUID v4 generates non linear UUIDS that will spread data across different linear partitions based on key or hash.

The new UUID7 (https://uuid7.com/) will guarantee that continuous records are time sortable and a good collision resistance.

The main advantage of UUID7 function is that it will allow to MariaDB avoid the limitation of use an unique combined primary key for time series records, where an unique ID and a date is required in order to create key/hash partitions. With UUID7 is possible only use only the key as ID and time reference.

Right now is possible to generate UUID7 using a poor custom UDF however it may be very slow.

Attachments

Issue Links

causes

MDEV-33710 Implement Timestamp Extraction for UUIDv1 and UUIDv7

Open

is blocked by

MDEV-33827 UUID() returns a NULL-able result

Closed

is part of

MDEV-23748 support not-MAC-address based UUID versions

Closed

relates to

MDEV-32583 UUID() should be treated as stochastic for the purposes of forcing query materialization

Closed

MDEV-33711 Increase Tests for UUIDv7

Open

MDEV-11339 Support UUID v4 generation

Closed

MDEV-34981 Functions missing from INFORMATION_SCHEMA.SQL_FUNCTIONS

Closed

(2 relates to)

Activity

Ascending order - Click to sort in descending order

Juan Lago created issue - 2023-10-31 12:55

Sergei Golubchik made changes - 2023-10-31 19:54

Field	Original Value	New Value
Labels	uuid	beginner-friendly uuid

Sergei Golubchik added a comment - 2023-10-31 19:57

Correction: MariaDB UUID() function generates UUIDv1, not UUIDv4. They are time sortable.

Anyway, being able to generate UUID's of other versions is a totally valid feature request.

Sergei Golubchik added a comment - 2023-10-31 19:57 Correction: MariaDB UUID() function generates UUIDv1, not UUIDv4. They are time sortable. Anyway, being able to generate UUID's of other versions is a totally valid feature request.

Sergei Golubchik made changes - 2023-10-31 19:58

Summary

[Feature request] - Implement native UUID7 function

Implement native UUID7 function

Sergei Golubchik made changes - 2023-10-31 19:58

Link

This issue relates to ~~MDEV-11339~~ [ ~~MDEV-11339~~ ]

Sergei Golubchik made changes - 2023-10-31 19:58

Link

This issue is part of ~~MDEV-23748~~ [ ~~MDEV-23748~~ ]

Daniel Black added a comment - 2023-11-01 00:07 - edited

In the mean time, a fast uuidv7:

From https://github.com/pluots/udf-suite/releases

doc: https://github.com/pluots/udf-suite/blob/main/udf-uuid/README.md

Untar from the github release with the .so files put into /usr/lib/mysql/plugin

MariaDB [test]> CREATE FUNCTION uuid_generate_v7 RETURNS string SONAME 'libudf_uuid.so';

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT BENCHMARK(1000000,uuid_generate_v7());

+---------------------------------------+

| BENCHMARK(1000000,uuid_generate_v7()) |

+---------------------------------------+

|                                     0 |

+---------------------------------------+

1 row in set (0.063 sec)

Unlike the SQL version you had which generates invalid uuids:

MariaDB [test]> SELECT BENCHMARK(1000000,uuidv7());

ERROR 1292 (22007): Incorrect uuid value: '06541949-30-7602-4E1D-DD59E218E01A'

MariaDB [test]> SELECT BENCHMARK(1000000,uuidv7());

ERROR 1292 (22007): Incorrect uuid value: '0654194A-90-738F-4364-563AF35DC398'

MariaDB [test]> SELECT BENCHMARK(1000000,uuidv7());

ERROR 1292 (22007): Incorrect uuid value: '0654194A-30-75A3-48A3-89854C46C137'

MariaDB [test]> SELECT BENCHMARK(1000000,uuidv7());

ERROR 1292 (22007): Incorrect uuid value: '0654194A-70-7E23-49E4-E49A5AE9C3FE'

Daniel Black added a comment - 2023-11-01 00:07 - edited In the mean time, a fast uuidv7: From https://github.com/pluots/udf-suite/releases doc: https://github.com/pluots/udf-suite/blob/main/udf-uuid/README.md Untar from the github release with the .so files put into /usr/lib/mysql/plugin MariaDB [test]> CREATE FUNCTION uuid_generate_v7 RETURNS string SONAME 'libudf_uuid.so' ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> SELECT BENCHMARK(1000000,uuid_generate_v7()); + ---------------------------------------+ | BENCHMARK(1000000,uuid_generate_v7()) | + ---------------------------------------+ | 0 | + ---------------------------------------+ 1 row in set (0.063 sec) Unlike the SQL version you had which generates invalid uuids: MariaDB [test]> SELECT BENCHMARK(1000000,uuidv7()); ERROR 1292 (22007): Incorrect uuid value: '06541949-30-7602-4E1D-DD59E218E01A' MariaDB [test]> SELECT BENCHMARK(1000000,uuidv7()); ERROR 1292 (22007): Incorrect uuid value: '0654194A-90-738F-4364-563AF35DC398' MariaDB [test]> SELECT BENCHMARK(1000000,uuidv7()); ERROR 1292 (22007): Incorrect uuid value: '0654194A-30-75A3-48A3-89854C46C137' MariaDB [test]> SELECT BENCHMARK(1000000,uuidv7()); ERROR 1292 (22007): Incorrect uuid value: '0654194A-70-7E23-49E4-E49A5AE9C3FE'

Juan Lago added a comment - 2023-11-01 07:11

@Daniel Black: Thank you for provide me a plugin that can generate UUIDv7. Unfortunately some the server that I am using are hosted in cloud providers like AWS and they don't allow to install custom plugins.

I will fix the UDF so it will generate correctly UUID7.

Juan Lago added a comment - 2023-11-01 07:11 @Daniel Black: Thank you for provide me a plugin that can generate UUIDv7. Unfortunately some the server that I am using are hosted in cloud providers like AWS and they don't allow to install custom plugins. I will fix the UDF so it will generate correctly UUID7.

Daniel Black added a comment - 2023-11-01 08:01 - edited

Sorry to hear that. Welcome a share of the corrected SQL too.

If anyone feels comfortable implementing it, the two item_uuidfunc* files in https://github.com/MariaDB/server/tree/11.3/plugin/type_uuid contain the current UUIDv1 implementation. Adding 1 class and an implementation is all that is required.

And the my_uuid function is a useful reference.

Daniel Black added a comment - 2023-11-01 08:01 - edited Sorry to hear that. Welcome a share of the corrected SQL too. If anyone feels comfortable implementing it, the two item_uuidfunc* files in https://github.com/MariaDB/server/tree/11.3/plugin/type_uuid contain the current UUIDv1 implementation. Adding 1 class and an implementation is all that is required. And the my_uuid function is a useful reference.

Juan Lago added a comment - 2023-11-01 09:10

I fixed the code.

I also implemented a procedure that test collisions.

Juan Lago added a comment - 2023-11-01 09:10 I fixed the code. I also implemented a procedure that test collisions.

Jaydeep Das added a comment - 2023-12-23 07:21 - edited

juanparati, danblack, So, is the issue fixed or still open to work on?

Jaydeep Das added a comment - 2023-12-23 07:21 - edited juanparati , danblack , So, is the issue fixed or still open to work on?

Juan Lago added a comment - 2023-12-25 11:14

@Jaydeep Das: I am not MariaDB developer. I just fixed a poor function that can we use as a workaround: https://gist.github.com/juanparati/0ded9c04d4cd43e5aae8f5a438a8b18b

Juan Lago added a comment - 2023-12-25 11:14 @Jaydeep Das: I am not MariaDB developer. I just fixed a poor function that can we use as a workaround: https://gist.github.com/juanparati/0ded9c04d4cd43e5aae8f5a438a8b18b

Stefano Petrilli made changes - 2024-03-18 15:30

Link

This issue causes MDEV-33710 [ MDEV-33710 ]

Stefano Petrilli made changes - 2024-03-18 15:43

Link

This issue relates to MDEV-33711 [ MDEV-33711 ]

Daniel Black made changes - 2024-03-28 01:24

Issue Type

Task [ 3 ]

New Feature [ 2 ]

Alexander Barkov made changes - 2024-04-02 05:16

Link

This issue relates to ~~MDEV-11339~~ [ ~~MDEV-11339~~ ]

Alexander Barkov made changes - 2024-04-02 05:16

Link

This issue relates to ~~MDEV-11339~~ [ ~~MDEV-11339~~ ]

Alexander Barkov made changes - 2024-04-04 10:15

Link

This issue is blocked by ~~MDEV-33827~~ [ ~~MDEV-33827~~ ]