Warning about UUID in MySQL master-slave replication

Date: May 23, 2014

Blog: Anchor

Most recently, when viewing the error log of the MARIADB master-slave replication server master, I see a number of warning messages, all of which indicate that the UUID () function is unsafe and may Slave the resulting values and master inconsistencies, the warning message is as follows:

140522 15:11:10 [Warning] Unsafe statement written to the binary logusing statement format since Binlog_format = statement . Statement is unsafe because it uses a system function, that could return a different value on the slave. Statement:insert into T_user (userid,username) VALUES (UUID (), ' Funyoung ')

The approximate translation to Chinese is as follows:

140522 15:11:10 [WARNING] because the format used is Binlog_format = STATEMENT, statements written to the binary log may not be secure. The statement is unsafe because a system function is used, Slave execution from the server may generate inconsistent values. The statement is as follows: INSERT into T_user (userid,username) VALUES (UUID (), ' Funyoung ')

It seems to be that because the slave UUID () function produces a value that may be inconsistent with the master, useBinlog_format = STATEMENTThis log format is not secure.

Find some information on the Internet, show 5.0 version is absolutely problematic, how to do? Either modify the implementation, such as generating a UUID at the application layer, or using a row-based, rather than a statement-based, binary log format.

It is said that after 5.0 is not necessarily, looked through some official documents, as if the 5.6 version fixed the problem. Reference link: changes in MySQL 5.6.0

There was a 09 document that said there was a problem at the time : https://drupal.org/node/502622

There is also a more detailed article: Beware of MySQL 5.6 server UUID when cloning slaves

We are using MariaDB5.5, should be compatible with MySQL5.5 bar, after the troubleshooting of the system has been solved by itself.

MariaDB [(None)]> select version (); +--------------------+| Version ()          |+--------------------+| 5.5.34-mariadb-log |+--------------------+1 row in Set (0.00 sec)

If you encounter this problem, if you can also upgrade the database system, then upgrade to the latest version that's fine.

If the dmbs cannot be upgraded, then it needs to be in the application tier, or with row-based replication.