Description:
When using InnoDB on master and Blackhole on the slave, multi-threaded replication, running a DML stops the replication with the error:
Worker 1 failed executing transaction '00046008-1111-1111-1111-111111111111:639579' at master log mysql-bin.000001, end_log_pos 536609686; Could not execute Delete_rows event on table test.bug_report; Found a row in wrong partition 0. Correct is 1 id:1614839878108305 field1:quis quisquam blanditiis omnis voluptas est est voluptatem quibusdam corporis! field2:fugiat autem voluptatem magni et. field3:expedita dicta corrupti. field4:dolores sapiente sit a suscipit. field5:2021-08-22 03:03:35, Error_code: 1863; handler error No Error!; the event's master log mysql-bin.000001, end_log_pos 536609686 | 2021-12-29 10:51:08
How to repeat:
On the source server:
CREATE TABLE `bug_report` (
`id` bigint(20) NOT NULL,
`field1` varchar(255) COLLATE utf8_bin NOT NULL,
`field2` varchar(255) COLLATE utf8_bin NOT NULL,
`field3` varchar(255) COLLATE utf8_bin NOT NULL,
`field4` varchar(255) COLLATE utf8_bin NOT NULL,
`field5` datetime NOT NULL,
PRIMARY KEY (`id`,`field1`,`field2`,`field3`,`field5`),
UNIQUE KEY `UK_blacklisted_action_type` (`field1`,`field2`,`field3`,`field5`)
) ENGINE=InnoDB AUTO_INCREMENT=29645937 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (TO_DAYS(field5))
(PARTITION p20210701 VALUES LESS THAN (738368) ENGINE = InnoDB,
PARTITION p20210801 VALUES LESS THAN (738399) ENGINE = InnoDB,
PARTITION p20210901 VALUES LESS THAN (738429) ENGINE = InnoDB,
PARTITION p20211001 VALUES LESS THAN (738460) ENGINE = InnoDB,
PARTITION p20211101 VALUES LESS THAN (738490) ENGINE = InnoDB,
PARTITION p20211201 VALUES LESS THAN (738521) ENGINE = InnoDB,
PARTITION p20220101 VALUES LESS THAN (738552) ENGINE = InnoDB,
PARTITION p20220201 VALUES LESS THAN (738580) ENGINE = InnoDB,
PARTITION p20220301 VALUES LESS THAN (738611) ENGINE = InnoDB) */;
On the replica server:
CREATE TABLE `bug_report` (
`id` bigint(20) NOT NULL,
`field1` varchar(255) COLLATE utf8_bin NOT NULL,
`field2` varchar(255) COLLATE utf8_bin NOT NULL,
`field3` varchar(255) COLLATE utf8_bin NOT NULL,
`field4` varchar(255) COLLATE utf8_bin NOT NULL,
`field5` datetime NOT NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (TO_DAYS(field5))
(PARTITION p20210701 VALUES LESS THAN (738368) ENGINE = BLACKHOLE,
PARTITION p20210801 VALUES LESS THAN (738399) ENGINE = BLACKHOLE,
PARTITION p20210901 VALUES LESS THAN (738429) ENGINE = BLACKHOLE,
PARTITION p20211001 VALUES LESS THAN (738460) ENGINE = BLACKHOLE,
PARTITION p20211101 VALUES LESS THAN (738490) ENGINE = BLACKHOLE,
PARTITION p20211201 VALUES LESS THAN (738521) ENGINE = BLACKHOLE,
PARTITION p20220101 VALUES LESS THAN (738552) ENGINE = BLACKHOLE,
PARTITION p20220201 VALUES LESS THAN (738580) ENGINE = BLACKHOLE,
PARTITION p20220301 VALUES LESS THAN (738611) ENGINE = BLACKHOLE) */;
# Loading the table with dummy data:
wget https://github.com/Percona-Lab/mysql_random_data_load/releases/download/v0.1.12/mysql_rand...
tar -xvf mysql_random_data_load_0.1.12_Linux_x86_64.tar.gz
chmod +x mysql_random_data_load
#Example
./mysql_random_data_load test bug_report 1000000 -uroot -pmsandbox -P 46008 --bulk-size=2 --max-threads=10
# run in another session in the master
delete from test.bug_report where field5 < '2022-12-25 11:00:00';
Replication will stop.
Suggested fix:
It seems that changing the slave_exec_mode to idempotent is a good workaround. The suggested fix is to automatically do this for blackhole engines.
