How to use LAST_INSERT_ID() when using uuid as a default value?

I have a table where I auto-generate a binary(16) Id by having this in my defaultExpression:

(uuid_to_bin(uuid(), true))

I created a store procedure and insert values to that table and figured I could retrive the latest value by using LAST_INSERT_ID() but Im getting the value 0 instead.

Example:

DECLARE userId BINARY(16);

INSERT INTO Users (Email, Password, FirstName, LastName, Age, Jobtitle, ImageId, PrefLang, ChangedPassword)
VALUES ('email', 'Hashpassword', 'firstName', 'lastName', 'age', 'jobTitle', 'imageId', 'prefLanguagId', 0);

SET userId = LAST_INSERT_ID(); // not getting the correct value here

Trying to use the value above in a junction table:

  INSERT INTO UserRoles (UserId, RoleId)
    VALUES (userId, roleId);

Error Code: 1452 Cannot add or update a child row: a foreign key constraint fails (`dbName`.`userroles`, CONSTRAINT UserIdRef FOREIGN KEY (`UserId`) REFERENCES `users` (`Id`))

How can I solve this?

Answer

LAST_INSERT_ID() only gets an ID that was assigned using AUTO_INCREMENT. It won’t return an ID that was assigned using a DEFAULT expression.

You’ll need to calculate the ID in your procedure rather than using the default.

SET userId = uuid_to_bin(uuid(), true);
INSERT INTO Users (userId, Email, Password, FirstName, LastName, Age, Jobtitle, ImageId, PrefLang, ChangedPassword)
    VALUES (userId, 'email', 'Hashpassword', 'firstName', 'lastName', 'age', 'jobTitle', 'imageId', 'prefLanguagId', 0);
INSERT INTO UserRoles (UserId, RoleId)
    VALUES (userId, roleId);