• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Setting a column as a UUID/GIUD in MySQL – replication safe

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)

Check Out These Related posts:

  1. MySQL utility commands
  2. How to default a column as a UUID/GUID in MySQL
  3. PHP script to check MySQL replication status
  4. Master master replication with MySQL

Filed Under: MySql

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap