The
5.1 manual states:
Therefore, if you are using statement-based replication, you must either avoid INSERT ... ON DUPLICATE KEY UPDATE or use innodb_autoinc_lock_mode = 0
And earlier
in the same page is the description of innodb_autoinc_lock_mode=0
This lock mode is provided only for backward compatibility and performance testing. There is little reason to use this lock mode unless you use “mixed-mode inserts” and care about the important difference in semantics described later.
I don't think these statements agree. I am confused even more because upgrading a master-slave pair of servers to 5.1 with innodb_autoinc_lock_mode=1 and statement-based replication fixed a logical corruption problem that occurs when the wrong value was written to the binlog for SET INSERT_ID=... as part of a transaction that does INSERT ... ON DUPLICATE KEY UPDATE.There are a few details on that at
bug 50413.
Posts
I think you can't normally set the mode to 1 with SBR, because then concurrent inserts will get wrong values on the slave. This is particularly a problem when insert..select is used.
ReplyDeleteBtw, does this problem only happen on InnoDB?
I don't know about MyISAM. Use InnoDB if you want to avoid data drift between master and slave.
ReplyDeleteI am not aware of case in which innodb_autoinc_lock_mode=1 and SBR produces an incorrect result. I am very aware of a case in which innodb_autoinc_lock_mode=0 and SBR does that -- http://bugs.mysql.com/bug.php?id=50413
It seems we are getting to a point where SBR is increasingly incompatible with new features designed to get better performance from the server. But RBR is not implemented in a satisfactory way for my taste at the moment, or at least not documented and transparent enough. When RBR works badly, it's an uphill battle to solve it.
ReplyDelete