UUID as default for MySQL id column

Question

I am trying to add a column to an existing table in MySql 8.0.17. The column needs to contain a UUID and I am trying to set it as a default value.

This is the statement I am executing

ALTER TABLE myTable ADD COLUMN UUID varchar(36) NOT NULL DEFAULT (UUID());

However I am getting the following error

Error Code: 1674. Statement is unsafe because it uses a system function that may return a different value on the slave.

I have read from other posts that it is possible to create a Trigger on the table however i would like to find out whether it is possible to set it directly as the default value on the column.

Also, what would be the advantage of using a binary conversion of the UUID over just a simple UUID ?

Eg.

ALTER TABLE myTable ADD COLUMN UUID binary(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), true));

Thanks for your help.

Intuitivism

Answer 1

Assigning UUID() as DEFAULT value won't work, because it does not guarantee that the same value will be generated on your replica. That is why using TRIGGER is good option for new records (insertions).

If your intention is to update current records as well, you can write an update statement

UPDATE myTable
SET UUID = UUID();

---

Your column is of type binary(16) which means UUID data is implicitly converted to binary. Using UUID_TO_BIN is not needed.

---

EDIT

CHAR / VARCHAR is the human-readable format, whereas binary is the compact format. That means compressing the 32 characters (36 or more with separators) to the 16-bit format or back to the human-readable format.

If you don't mind about reading UUID, best is to use binary format.

Floozy

Hi Dervis, thanks for your response. So do you suggest i set the column as varchar(36) or binary(16) ? And what is the advantage of one over the other one? Thanks. – Intuitivism

You can set your column as CHAR(36) as it would always same length. – Malnutrition

CHAR/VARCHAR is the human-readable format. whereas, binary is the compact format. If you dont mind about reading UUID, best is to use binary format – Pruter

Answer 2

Change VARCHAR to CHAR, this will let you use 16bit.

Old Method

ALTER TABLE myTable ADD COLUMN UUID varchar(36) NOT NULL DEFAULT (UUID());

New Method

ALTER TABLE myTable ADD COLUMN UUID BINARY(36) NOT NULL DEFAULT (UUID_TO_BIN(UUID()));

Odense

Why BINARY(36) for the "new method"? IAFAIK a BINARY(16) should be enough for an UUID. Besides the different size this is exactly what is included in the question so I don't see how this will answer the question. Also 16bit can not be correct, may be you mean 16 bytes? – Wulfenite

I'm getting the error with both methods – Loaiasis

Answer 3

If the ALTER command is sent directly to the replica server for execution (rather than creating the column on the main server with data and then replicating that information), the error you encountered occurs because the UUID() function may generate different unique identifiers on the main server (where the query is executed) and the replica servers in your configuration. This inconsistency can potentially compromise data integrity.

While it works fine for CREATE TABLE, it doesn't behave as expected for ALTER TABLE.

The Solution

The provided solution addresses this concern by breaking down the process into distinct ALTER TABLE statements:

1. Adding the Column:

   ALTER TABLE foo ADD guid binary(16);
   

2. Populating with UUIDs:

   UPDATE foo SET guid = uuid();
   

3. Setting NOT NULL and Default:

   ALTER TABLE foo MODIFY guid binary(16) NOT NULL DEFAULT (uuid());
   

This approach ensures the following:

• The column is added without a default value initially.
• All existing rows are populated with UUIDs using the UPDATE statement.
• Finally, the NOT NULL constraint and default value are applied, guaranteeing consistency across the main and replica servers.

Additional Considerations

• Emphasize using binary(16) over varbinary(16) to ensure a fixed size of 16 bytes for the binary UUID representation.
• Be aware that this approach might generate more data flow between the main and replica servers due to the UPDATE statement.

Optional Reordering Steps

While the provided order works well, you can potentially swap steps 2 and 3:

2. Adding the column with a default:

   ALTER TABLE foo MODIFY guid binary(16) DEFAULT (uuid());
   

3. Updating null values (if any):

   UPDATE foo SET guid = uuid() WHERE guid IS NULL;
   

4. Setting NOT NULL:

   ALTER TABLE foo MODIFY guid binary(16) NOT NULL;
   

Batch Updates (For Large Datasets)

If you're dealing with a massive dataset, consider updating existing columns in smaller batches to minimize impact.

Goggin