|
Given that binlog_format=MIXED is the default, it could be simplest to refuse statement-based replication for the unsafe statements (instead of trying to make them safe by extra locking), and gracefully fall back to row-based replication.
Revert-InnoDB-SBR-changes.patch is a step towards that, removing some InnoDB code. Please read the comments inside, and consider doing something similar for MyRocks. Also, the innodb_binlog.combinations changes in its parent commit (MDEV-17073) should probably be reverted and the test innodb.auto_increment_dup adjusted.
|
|
The scope of the current patch is to "save" STATEMENT binlog format
replication, while the being associated 17614 's it is to make
MIXED "policy" aware of multi-key IODKU unsafety.
|
|
Fixing this could end up fixing MDEV-18141 as well, if we declare the REPLACE and INSERT…ON DUPLICATE KEY UPDATE unsafe for statement-based replication and force a switch from binlog_mode=MIXED to binlog_mode=ROW.
|
|
It turns out that upstream reverted the problematic fix and implemented a better fix in MySQL 5.7.26. I plan to essentially revert MDEV-17073 and apply that fix, to close this bug.
|
|
I reverted MDEV-17073 and the upstream change and implemented and tested a simplified version of what appeared in MySQL 5.7.26.
Neither our test innodb.auto_increment_dup,log-bin nor the upstream test innodb.iodku would pass with those changes. (OK, I missed follow-up fixes to their test case.)
What happens in innodb.auto_increment_dup,log-bin is that the newly converted explicit lock for the record will be released when that record is deleted by the rollback moments later.
The idea of converting implicit locks to explicit on rollback is somewhat expensive. One motivation of MDEV-16232 is to reduce the amount of explicit locks by holding page latches across some handler API calls.
Ideally, this problem would be fixed by changing something in replication, perhaps simply by refusing statement-based replication for these operations.
|
|
The priority is lowered to focus on MDEV-17614 first which tackles the issue when @@binlog_format is MIXED. (The scope of the current ticket as noted covers STATEMENT format).
|
|
Test Case:-
--source include/master-slave.inc
|
--source include/have_binlog_format_statement.inc
|
--source include/have_innodb.inc
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
|
INSERT INTO t1 VALUES (1, 1, 1);
|
BEGIN;
|
INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
|
--connection master1
|
INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
|
--connection master
|
COMMIT;
|
SELECT * FROM t1;
|
--sync_slave_with_master
|
SELECT * FROM t1;
|
|
--connection master
|
drop table t1;
|
--sync_slave_with_master
|
|
--source include/rpl_end.inc
|
|
|
|
Now that MDEV-17614 has fixed the correctness issue, I believe that this becomes a mere performance enhancement. I believe that such enhancements that involve file format changes should be avoided in GA versions.
|
|
After discussion with Elkin and monty , It is decided that this will no longer be fixed, User are advised to user mixed binlog_format , Since it is most optimized and takes care of these issues
|
Revert-InnoDB-SBR-changes.patch