Setting a column as a UUID/GIUD in MySQL - replication safe

I recently looked at how to default a column as a UUID/GUID in MySQL using a trigger, but it's not replication safe when using statement based replication. This post looks at an alternative to using triggers by using a variable instead.

Example table

Let's use the same example table as in the previous post. This is a very basic example table; replace 'tablename' with your actual tablename and add appropriate fields, primary key, change the uuid fieldname, etc.

CREATE TABLE `tablename` (
    `uuid` char(36) NULL,
    `reference` varchar(100) NOT NULL
);

Using a trigger is not statement based replication safe

As noted in the previous post, if you are using statement based replication between a master & slave (or master & master), the UUID will not be replicated across to the slave server(s) and different UUID(s) will be created on the slave(s).

Why not just use the UUID() function directly in the insert/update query?

You could run this query, for example:

INSERT INTO tablename (uuid, name) VALUES (uuid(), 'example');

but again, it's not statement based replication safe and the end result will most likely be different UUID values on the master and slave(s).

Set a variable instead

If you set a variable and use that in the insert or update query, then it does appear to be statement based replication safe, which I have tested myself using master-master replication with MySQL 5.5.

So, do this:

SET @uuid = UUID();
INSERT INTO tablename (uuid, name) VALUES (@uuid, 'example');

Now both the master and slave server(s) will have the same uuid value.

Testing and warnings

These are all the tests I ran:

mysql> insert into tablename (name) values ('automatically set by trigger');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into tablename (uuid, name) values (uuid(), 'set using the uuid() function');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> set @uuid=UUID();
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tablename (uuid, name) values (@uuid, 'set using set @uuid variable');
Query OK, 1 row affected, 1 warning (0.01 sec)

Using statement based replication, all warnings were the same: "Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave."

However, the last example worked fine, despite this warning:

On the master server:

mysql> select * from tablename;
+--------------------------------------+-------------------------------+
| uuid                                 | name                          |
+--------------------------------------+-------------------------------+
| 0a118942-97b2-11e5-b6ee-f23c916e6be0 | automatically set by trigger  |
| 46b800a4-97b2-11e5-b6ee-f23c916e6be0 | set using the uuid() function |
| 711d2c09-97b2-11e5-b6ee-f23c916e6be0 | set using set @uuid variable  |
+--------------------------------------+-------------------------------+
3 rows in set (0.00 sec)

On the slave server:

mysql> select * from tablename;
+--------------------------------------+-------------------------------+
| uuid                                 | name                          |
+--------------------------------------+-------------------------------+
| 0a151316-97b2-11e5-b867-f23c916e6b8f | automatically set by trigger  |
| 46bba1aa-97b2-11e5-b867-f23c916e6b8f | set using the uuid() function |
| 711d2c09-97b2-11e5-b6ee-f23c916e6be0 | set using set @uuid variable  |
+--------------------------------------+-------------------------------+
3 rows in set (0.00 sec)

Related posts:

• How to default a column as a UUID/GUID in MySQL
• Temporarily changing the auto increment increment in MySQL
• PHP script to check MySQL replication status
• Master master replication with MySQL

How to default a column as a UUID/GUID in MySQL

MySQL does not yet support setting a column's default value using a function (at least not yet as of version 5.6) but you can use a trigger instead. This post shows how to set a column to a UUID/GUID by default in MySQL using a trigger.

A warning about replication before we start

If you are using statement based replication between a master & slave (or master & master), the examples on this page will not replicate the UUID across to the slave server(s) and different UUID(s) will be created on the slave(s). I cover another method which is statement based replication safe here.

Example table

This is a very basic example table; replace 'tablename' with your actual tablename and add appropriate fields, primary key, change the uuid fieldname, etc.

CREATE TABLE `tablename` (
    `uuid` char(36) NULL,
    `reference` varchar(100) NOT NULL
);

The trigger

The following trigger will set the uuid field to a UUID if it hasn't already been set by your insert query whenever a new record is inserted:

DELIMITER ;;
CREATE TRIGGER before_insert_tablename
BEFORE INSERT ON tablename
FOR EACH ROW
BEGIN
  IF new.uuid IS NULL THEN
    SET new.uuid = uuid();
  END IF;
END
;;

Note that the SET and END IF lines need to be terminated with ; otherwise you'll get a SQL error. However, these would define the end of the query, hence the DELIMITER ;; statement at the very start of the trigger creation and then the ;; at the end to finish it.

Remember to reset you delimiter back to ; if you are running this from the MySQL CLI:

DELIMITER ;

Example insert and resulting data

We'll insert a value into the reference column only, and the trigger will automatically set the uuid field to a UUID:

INSERT INTO tablename (reference) VALUES ('example');

Now we'll select the data to see the automatically populated UUID:

SELECT * FROM tablename;

And the resulting data:

+--------------------------------------+-----------+
| uuid                                 | reference |
+--------------------------------------+-----------+
| 79c9e1ac-96f1-11e5-85a6-000c29f1f6c4 | example   |
+--------------------------------------+-----------+

This method can be used for any functions you want to automatically populate a column with in MySQL, by using triggers. Note that as mentioned at the start, this won't work as expected when using statement based replication, because the UUID generated will be different on each server.

Check the Advantages and Disadvantages of Statement-Based and Row-Based Replication section of the MySQL manual for more information about the functions which won't be replicated correctly, and I cover another method which is statement based replication safe here.

Related posts:

• Temporarily changing the auto increment increment in MySQL
• PHP script to check MySQL replication status
• Master master replication with MySQL