Warning about UUID in MySQL master-slave Replication

Date: January 1, May 23, 2014

Blog: Tie

Recently, many warning messages are displayed when you view the error log of the MariaDB Master-Slave replication server Master. They indicate that the UUID () function is insecure, and the Slave value may be different from that of the Master, the warning information is roughly 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 may return a different value on the slave. Statement: insert into t_user(userId,userName) values(uuid(),'FunYoung')

The general translation is as follows:

140522 15:11:10 [Warning] The statements written to binary logs may be insecure because the format is BINLOG_FORMAT = STATEMENT. the statement is insecure because a system function is used, and execution on the slave server may generate inconsistent values. the statement is as follows: insert into t_user (userId, userName) values (uuid (), 'funyoung ')

It seems that the log format BINLOG_FORMAT = STATEMENT is insecure because the value produced by the slave UUID () function may be different from that of the Master.

I found some information on the Internet and found that version 5.0 is definitely a problem. What should I do? Either modify the implementation, for example, generate a UUID at the application layer, or use a line-based, instead of a statement-based binary log format.

It is said that 5.0 and later are not necessarily true. I read some official documents and it seems that version 5.6 has fixed this issue. Reference link: 22 Changes in MySQL 5.6.0

There is also a 09-year document, said there was this problem: https://drupal.org/node/502622

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

We use MariaDB5.5, which should be compatible with MySQL5.5. After troubleshooting this problem, the system has solved it 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 upgrade the database system, upgrade to the latest version.

If you cannot upgrade DMBS, you need to use the row-based replication method at the application layer.