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)