Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
Description
Consider the following test case (based on real life issue and simplified by Elkin):
CREATE TABLE `ti_pk` ( |
`a` int(11) NOT NULL, |
`b` int(11) DEFAULT NULL, |
PRIMARY KEY (`a`) |
) ENGINE=InnoDB;
|
|
# for the MIXED binlog format version of the bug (and it exists with ROW format as well) the direct logging needs to be non-default |
set @@session.binlog_direct_non_transactional_updates=1; |
set @@binlog_format=mixed; |
# Bug
|
begin; |
DROP TEMPORARY TABLE IF EXISTS `tmp_0`; |
CREATE TEMPORARY TABLE tmp_0 (a int primary key); |
insert into tmp_0 set a=1; |
INSERT into ti_pk select a,1 from tmp_0; |
INSERT into ti_pk set a=3; |
# --error |
INSERT into ti_pk values (2,1),(3,2) /* hit the dup key by the 2nd row */; |
|
commit; |
show binlog events; /* a=2 ROW event is there */ |
You should get error upon INSERT, but the event that causes the problem is in the binary log. Now check this output on master vs slave:
select count(*) = 0 from ti_pk where a = 2; |
Slave is already out of sync and on every next event it may easily break/hit error that does not happen on master.