Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32637

Implement native UUID7 function

Details

    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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33710 Implement Timestamp Extraction for UUIDv1 and UUIDv7

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33827 UUID() returns a NULL-able result

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-23748 support not-MAC-address based UUID versions

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32583 UUID() should be treated as stochastic for the purposes of forcing query materialization

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-33711 Increase Tests for UUIDv7

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-11339 Support UUID v4 generation

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34981 Functions missing from INFORMATION_SCHEMA.SQL_FUNCTIONS

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik added a comment -

            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.

            serg Sergei Golubchik added a comment - 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.
            danblack Daniel Black added a comment - - 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'

            danblack Daniel Black added a comment - - 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'
            juanparati Juan Lago added a comment -

            @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.

            juanparati Juan Lago added a comment - @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.
            danblack Daniel Black added a comment - - 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.

            danblack Daniel Black added a comment - - 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.
            juanparati Juan Lago added a comment -

            I fixed the code.

            I also implemented a procedure that test collisions.

            juanparati Juan Lago added a comment - I fixed the code. I also implemented a procedure that test collisions.
            jdeepd Jaydeep Das added a comment - - edited

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

            jdeepd Jaydeep Das added a comment - - edited juanparati , danblack , So, is the issue fixed or still open to work on?
            juanparati Juan Lago added a comment -

            @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

            juanparati Juan Lago added a comment - @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
            bar Alexander Barkov added a comment -

            Hello serg Can you please review the pull request from StefanoPetrilli:

            https://github.com/MariaDB/server/pull/3093
            ?

            The low level UUDIv7 generation code is especially interesting.
            Couldn't we reuse some of UUIDv1 code?

            Thanks.

            bar Alexander Barkov added a comment - Hello serg Can you please review the pull request from StefanoPetrilli: https://github.com/MariaDB/server/pull/3093 ? The low level UUDIv7 generation code is especially interesting. Couldn't we reuse some of UUIDv1 code? Thanks.
            danblack Daniel Black added a comment -

            note - ensure MDEV-32583 fix is applied here too.

            danblack Daniel Black added a comment - note - ensure MDEV-32583 fix is applied here too.
            serg Sergei Golubchik added a comment - - edited

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

            serg Sergei Golubchik added a comment - - edited bb-11.7- MDEV-33710 -uuid is ready for testing. It has both UUIDv4 and UUIDv7
            serg Sergei Golubchik added a comment -

            let's change the name to match what percona has

            serg Sergei Golubchik added a comment - let's change the name to match what percona has

            People

              serg Sergei Golubchik
              juanparati Juan Lago
              Votes:
              1 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.