In MySQL replication Master-Slave, is there anyway to deal with the functions:
- LOAD_FILE()
- UUID(), UUID_SHORT()
- USER()
- FOUND_ROWS()
- SYSDATE()
- GET_LOCK()
- IS_FREE_LOCK()
- IS_USED_LOCK()
- MASTER_POS_WAIT()
- RAND()
- RELEASE_LOCK()
- SLEEP()
- VERSION()
We have Replication and I am kind of worry for the consistency of the data, I.E in my localhost.
MASTER
mysql> CREATE TABLE `t1` ( `number_id` varchar(30) DEFAULT NULL );
mysql> insert into t1 values (uuid_short());
mysql> select * from t1;
+-------------------+
| number_id |
+-------------------+
| 95769054064672779 |
+-------------------+
1 row in set (0.00 sec)
Slave
mysql> select * from t1;
+--------------------+
| number_id |
+--------------------+
| 167826651055390725 |
+--------------------+
1 row in set (0.00 sec)
Master
mysql> update t1 set number_id=0 where number_id='95769054064672779';
mysql> select * from t1;
+-----------+
| number_id |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
Slave
mysql> select * from t1;
+--------------------+
| number_id |
+--------------------+
| 167826651055390725 |
+--------------------+
1 row in set (0.00 sec)
Is there anyway to deal with it? I am not a programmer, should the programmers knows that it's replication database so he can start using variables with Mysql Functions to keep the consistency?
Best Answer
What you're seeing is completely unacceptable, and developers should not be expected to work around it.
You've suppressed it in your answer, and I don't know if you didn't notice it, or didn't consider it significant, or something is broken in your setup. Here's what you should have seen:
Notice the 1 warning.
So your simple fix is "don't use statement format."
Clean up those discrepant tables, and then...
Then, prepare yourself for what follows... because
MIXED
mode is more correct and deterministic thanSTATEMENT
mode, it's also more likely to detect cases where your servers are already inconsistent, and replication will stop. Don't be confused thatMIXED
mode has broken it...MIXED
mode will just tend to uncover problems you already have.The same thing goes for
ROW
format, which is even more aggressive at maintaining identical data, and is my preferredBINLOG_FORMAT
for that reason, among others.You also need to set this in your
my.cnf
file so that the setting persists if the server is restarted.But once you aren't using
STATEMENT
mode any more, this issue should completely disappear.http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html