MySQL’s UUID() and RAND() functions both provide with (pseudo) indeterministic result. UUID()‘s result is moreover bound to the host on which it executes. For this reason, both are unsafe to replicate with STATEMENT binlog format. As an example, consider:
master> create table test.uuid_test (id int, u varchar(64)); master> insert into test.uuid_test values (1, UUID()); Query OK, 1 row affected, 1 warning (0.03 sec) master> select * from test.uuid_test; +------+--------------------------------------+ | id | u | +------+--------------------------------------+ | 1 | 7e3596d8-56ac-11e4-b284-3c970ea31ea8 | +------+--------------------------------------+
The warning we got on the insert directly relates to the following inconsistency on a slave:
slave1> select * from test.uuid_test; +------+--------------------------------------+ | id | u | +------+--------------------------------------+ | 1 | 7e379d63-56ac-11e4-8477-3c970ea31ea8 | +------+--------------------------------------+
The data on the slave is clearly inconsistent with the master’s. The slave, replicating via STATEMENT binlog format, re-executes the INSERT command and gets a different UUID value.
External
One solution to the above is to generate the UUID value from your application. By the time MySQL gets the INSERT statement, the UUID value is a constant string, as far as MySQL is concerned.
Internal
However there’s a way to do it from within MySQL, by decoupling the UUID() function from the INSERT statement. It takes a session variable. Consider: Continue reading » “Making UUID() and RAND() replication safe”