uuid support

[mbnoimi]

Hi,

One of the limitations of PCA is Primary keys should either be auto-increment (from 1 to 2^53) or UUID

Does this structure is supported by PCA?

--Table: tbl_name

--DROP TABLE IF EXISTS tbl_name;

CREATE TABLE tbl_name (
  id_name  char(36) NOT NULL,
  title    varchar(20) NOT NULL,
  /* Keys */
  PRIMARY KEY (id_name)
) ENGINE = InnoDB;

DELIMITER |

CREATE DEFINER = 'root'@'localhost' TRIGGER trigger01
  BEFORE INSERT
  ON tbl_name
  FOR EACH ROW
BEGIN
  /* Trigger text */

  IF new.id_name IS NULL THEN
    SET new.id_name = uuid();
  END IF;

END|

DELIMITER ;

[mevdschee]

Unfortunately not. This structure will not use last insert id, thus it wont work (as in.. the insert operation will not return the uuid, otherwise everything works fine).

See: https://www.php.net/manual/en/mysqli.insert-id.php

What you might do (as a workaround) is generating the uuid client side (isn't that the best practice anyway?).

Read also: https://stackoverflow.com/questions/4687196/mysql-insert-id-or-something-like-that-to-return-last-mysql-uuid

[mbnoimi]

What about using sequences instead?

CREATE SEQUENCE users_seq;

CREATE TABLE users (
  id          bigint UNSIGNED NOT NULL DEFAULT nextval(`MyDB`.`users_seq`),
  first_name  varchar(100) NOT NULL,
  last_name   varchar(100) NOT NULL,
  email       varchar(254) NOT NULL,
  /* Keys */
  PRIMARY KEY (id)
) ENGINE = InnoDB;

[mevdschee]

I was reading this:

https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

and found:

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID().

So maybe you can use this in the trigger:

SET new.id_name = uuid();
LAST_INSERT_ID(new.id_name);

I haven't tested it though..

[mevdschee]

What about using sequences instead?

I guess the same limitations apply.

[mbnoimi]

Thanks a lot @mevdschee,

I'm really frustrated of this limitation because I want an effective way for using uuids or Sequences. I don't want to generate the values by client side or depending on using LAST_INSERT_ID() function because as you know uuids suppose to be a random so using LAST_INSERT_ID() will shorten the range a lot.

[mevdschee]

First of all, why don't you want to generate the values client side? I'm not asking because I want you to do this, only to learn from.

as you know uuids suppose to be a random so using LAST_INSERT_ID() will shorten the range a lot.

I did not propose to rely on the auto_increment value generated, but setting your own using LAST_INSERT_ID(expr) instead of LAST_INSERT_ID().

I'm really frustrated of this limitation

I noticed and I wonder, did you even try what I proposed?

[mevdschee]

Let's work out a solution and document it in the README. Or did you give up already?

[mbnoimi]

why don't you want to generate the values client side?

This is the last resort.

but setting your own using LAST_INSERT_ID(expr) instead of LAST_INSERT_ID().

I mentioned LAST_INSERT_ID() as a shortcut for LAST_INSERT_ID(expr)

I noticed and I wonder, did you even try what I proposed?

Actually I tested the same logic before your suggestion because I digged in Google before posting this issue.

There is no way to use LAST_INSERT_ID(expr) because I don't need it at all.
I can simply use sequences as following:

CREATE SEQUENCE users_seq;
CREATE TABLE users (
  id          bigint UNSIGNED AUTO_INCREMENT NOT NULL,
  first_name  varchar(100) NOT NULL,
  last_name   varchar(100) NOT NULL,
  email       varchar(254) NOT NULL,
  /* Keys */
  PRIMARY KEY (id)
) ENGINE = InnoDB;

Then call users_seq whenever I want to use INSERT as following

SET @nval := (SELECT NEXTVAL(users_seq));

INSERT INTO users
    (id, first_name, last_name, email)
VALUES
    (@nval, "some first name", "some last name", "some email")

But I don't want to use this approach too (although it's much better than using LAST_INSERT_ID(expr)) because it makes gaps in the sequences just like old AUTO_INCREMENT and never fill them (ex. in case of failed INSERT) so once again it will decrease the range of bigint

More about sequences

[mevdschee]

it makes gaps in the sequences

I'm afraid that even happens with sequences.. unique is promised, gapless is not (in case of concurrency and failing queries)..

There is no way to use LAST_INSERT_ID(expr) because I don't need it at all.

You don't, but it is what is returned by the library on an insert request.

it will decrease the range of bigint

Is this really an issue in your usecase? Even with 20% loss a bigint has many many numbers.

[mbnoimi]

Is this really an issue in your usecase? Even with 20% loss a bigint has many many numbers.

Yes; because I want to use a single sequence for whole database which contains on many tables (about 150) with a 10 huge tables (the smallest one is at least 100 millions row)

[mevdschee]

64bit = 9,223,372,036,854,775,807 even with 50% loss and 1,000,000 x 100 million rows you have 99.9% of your numbers unused..

[mbnoimi]

64bit = 9,223,372,036,854,775,807 even with 50% loss and 1,000,000 x 100 million rows you have 99.9% of your numbers unused..

You consider one table while I've many! still afraid of the collision.

[mbnoimi]

Any way, I spent a lot of time on this issue.
I raised the white flag it seems PCA doesn't fit my requirement for this project (the limitations are really strict for middle size projects).
Thanks a lot @mevdschee I wasted your time for nothing.

[mevdschee]

You are welcome. Thank you for checking out PCA. Good luck with your project!