Flite Careers

How to Safely Replicate UUID Values in MySQL

With MySQL replication, when using the UUID() function to insert or update values in your database you need to be careful to assure the UUIDs are replicated properly. This is primarily a problem in statement-based replication.

Here’s a simple example with row-based replication to illustrate UUIDs replicating successfully:

Master

1
2
3
4
5
6
7
8
9
10
11
12
13
master > set binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)

master > insert into uuid_test (binlog_format,uuid_string) values(@@binlog_format,uuid());
Query OK, 1 row affected (0.00 sec)

master > select id,binlog_format,uuid_string,md5(uuid_string) from uuid_test;
+----+---------------+--------------------------------------+----------------------------------+
| id | binlog_format | uuid_string                          | md5(uuid_string)                 |
+----+---------------+--------------------------------------+----------------------------------+
|  1 | ROW           | f54448fa-7c20-11e4-8f6a-03e05a24a3c7 | a9f18c42b9d561f126ca007b1d273473 |
+----+---------------+--------------------------------------+----------------------------------+
1 row in set (0.00 sec)

Replica

1
2
3
4
5
6
7
replica1 > select id,binlog_format,uuid_string,md5(uuid_string) from uuid_test;
+----+---------------+--------------------------------------+----------------------------------+
| id | binlog_format | uuid_string                          | md5(uuid_string)                 |
+----+---------------+--------------------------------------+----------------------------------+
|  1 | ROW           | f54448fa-7c20-11e4-8f6a-03e05a24a3c7 | a9f18c42b9d561f126ca007b1d273473 |
+----+---------------+--------------------------------------+----------------------------------+
1 row in set (0.00 sec)

(Note that in addition to the UUID itself I also check the md5 hash of the UUID to make it easier to visually spot differences. This is relevant for UUIDs in particular since two non-matching UUIDs from the same machine will still have some matching substrings and thus may look like they match at first glance.)

In this case I can see the UUID value is the same.

Row-based replication seems fine, but what happens when I run the same test with statement-based replication?

Master

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
master > set binlog_format = 'STATEMENT';
Query OK, 0 rows affected (0.00 sec)

master > \W
Show warnings enabled.
master > insert into uuid_test (binlog_format,uuid_string) values(@@binlog_format,uuid());
Query OK, 1 row affected, 2 warnings (0.01 sec)

Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
Note (Code 1592): 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.
master > select id,binlog_format,uuid_string,md5(uuid_string) from uuid_test;
+----+---------------+--------------------------------------+----------------------------------+
| id | binlog_format | uuid_string                          | md5(uuid_string)                 |
+----+---------------+--------------------------------------+----------------------------------+
|  1 | STATEMENT     | d0095606-7c21-11e4-8f6a-03e05a24a3c7 | 6aa9e8cd140c8b19db4e36cc23a496e5 |
+----+---------------+--------------------------------------+----------------------------------+
1 row in set (0.00 sec)

Given the “Unsafe statement” warnings I expect the UUID on the replica to be different:

Replica

1
2
3
4
5
6
7
replica1 > select id,binlog_format,uuid_string,md5(uuid_string) from uuid_test;
+----+---------------+--------------------------------------+----------------------------------+
| id | binlog_format | uuid_string                          | md5(uuid_string)                 |
+----+---------------+--------------------------------------+----------------------------------+
|  1 | STATEMENT     | d009acb4-7c21-11e4-bca0-ab31f023deee | a5cd3de1cb802ede41c6ccc2204a1bda |
+----+---------------+--------------------------------------+----------------------------------+
1 row in set (0.00 sec)

The UUID did not replicate properly, and my INSERT generated a different value on the replica than on the master. The reference manual warns about this behavior:

Note

UUID() does not work with statement-based replication.

That’s a strong statement, but it doesn’t provide much detail about exactly why it doesn’t work.

Challenge accepted!

I will now make UUID() work with statement-based replication. The method I use is to set the UUID value to a user-defined variable first, and then use the user-defined variable in my INSERT statement.

Here’s an example:

Master

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
master > set binlog_format = 'STATEMENT';
Query OK, 0 rows affected (0.00 sec)

master > \W
Show warnings enabled.
master > set @uuid = uuid();
Query OK, 0 rows affected (0.00 sec)

master > insert into uuid_test (binlog_format,uuid_string) values(@@binlog_format,@uuid);
Query OK, 1 row affected, 1 warning (0.00 sec)

Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
master > select id,binlog_format,uuid_string,md5(uuid_string) from uuid_test;
+----+---------------+--------------------------------------+----------------------------------+
| id | binlog_format | uuid_string                          | md5(uuid_string)                 |
+----+---------------+--------------------------------------+----------------------------------+
|  1 | STATEMENT     | 8231373c-7c26-11e4-8f6a-03e05a24a3c7 | ac78c5527ecdc46fab2c51a4585b075b |
+----+---------------+--------------------------------------+----------------------------------+
1 row in set (0.00 sec)

I still got a warning, in this case only about the system variable and not about the system function. The warning doesn’t matter if the values are the same, so let me check the replica.

Replica

1
2
3
4
5
6
7
replica1 > select id,binlog_format,uuid_string,md5(uuid_string) from uuid_test;
+----+---------------+--------------------------------------+----------------------------------+
| id | binlog_format | uuid_string                          | md5(uuid_string)                 |
+----+---------------+--------------------------------------+----------------------------------+
|  1 | STATEMENT     | 8231373c-7c26-11e4-8f6a-03e05a24a3c7 | ac78c5527ecdc46fab2c51a4585b075b |
+----+---------------+--------------------------------------+----------------------------------+
1 row in set (0.00 sec)

The UUID matches! I have successfully used UUID() with statement-based replication.

In case you are wondering, the user-defined variable method works for UPDATE statements too:

Master

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
master > set binlog_format = 'STATEMENT';
Query OK, 0 rows affected (0.00 sec)

master > \W
Show warnings enabled.
master > set @uuid = uuid();
Query OK, 0 rows affected (0.00 sec)

master > update uuid_test set binlog_format = @@binlog_format, uuid_string = @uuid where id = 1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
master > select id,binlog_format,uuid_string,md5(uuid_string) from uuid_test;
+----+---------------+--------------------------------------+----------------------------------+
| id | binlog_format | uuid_string                          | md5(uuid_string)                 |
+----+---------------+--------------------------------------+----------------------------------+
|  1 | STATEMENT     | 015ee69e-7c27-11e4-8f6a-03e05a24a3c7 | 06126d10ff95e7dbc19f9fb338a89874 |
+----+---------------+--------------------------------------+----------------------------------+
1 row in set (0.00 sec)

Replica

1
2
3
4
5
6
7
replica1 > select id,binlog_format,uuid_string,md5(uuid_string) from uuid_test;
+----+---------------+--------------------------------------+----------------------------------+
| id | binlog_format | uuid_string                          | md5(uuid_string)                 |
+----+---------------+--------------------------------------+----------------------------------+
|  1 | STATEMENT     | 015ee69e-7c27-11e4-8f6a-03e05a24a3c7 | 06126d10ff95e7dbc19f9fb338a89874 |
+----+---------------+--------------------------------------+----------------------------------+
1 row in set (0.00 sec)
Comments

Comments