RBR replication HA_ERR_END_OF_FILE dropping a partition during another transaction
HHello,
I have a problem with RBR replication dropping a partition while another client is using it.
To reproduce is very simple.
One partitioned table (by date in the example)
CREATE TABLE `partitioned` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fecha` datetime NOT NULL,
`notas` varchar(50) NOT NULL DEFAULT 'ORIGINAL',
KEY `id__IDX` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE ( TO_DAYS(fecha))
(PARTITION p20120311 VALUES LESS THAN (734939) ENGINE = InnoDB,
PARTITION p20120312 VALUES LESS THAN (734940) ENGINE = InnoDB)
Two clients connected.
Client1:
insert into partitioned (fecha,notas) values ('2012-03-12 00:00:00', 'BEFORE');
insert into partitioned (fecha,notas) values ('2012-03-12 00:01:00', 'BEFORE');
insert into partitioned (fecha,notas) values ('2012-03-12 00:02:00', 'BEFORE');
insert into partitioned (fecha,notas) values ('2012-03-11 00:02:00', 'BEFORE');
insert into partitioned (fecha,notas) values ('2012-03-11 00:03:00', 'BEFORE');
insert into partitioned (fecha,notas) values ('2012-03-11 00:04:00', 'BEFORE');
select * from partitioned;
+----+---------------------+--------+
| id | fecha | notas |
+----+---------------------+--------+
| 4 | 2012-03-11 00:02:00 | BEFORE |
| 5 | 2012-03-11 00:03:00 | BEFORE |
| 6 | 2012-03-11 00:04:00 | BEFORE |
| 1 | 2012-03-12 00:00:00 | BEFORE |
| 2 | 2012-03-12 00:01:00 | BEFORE |
| 3 | 2012-03-12 00:02:00 | BEFORE |
+----+---------------------+--------+
Client2:
select * from partitioned;
+----+---------------------+--------+
| id | fecha | notas |
+----+---------------------+--------+
| 4 | 2012-03-11 00:02:00 | BEFORE |
| 5 | 2012-03-11 00:03:00 | BEFORE |
| 6 | 2012-03-11 00:04:00 | BEFORE |
| 1 | 2012-03-12 00:00:00 | BEFORE |
| 2 | 2012-03-12 00:01:00 | BEFORE |
| 3 | 2012-03-12 00:02:00 | BEFORE |
+----+---------------------+--------+
Client1:
set autocommit=0;
BEGIN
insert into partitioned (fecha,notas) values ('2012-03-11 00:03:00', 'IN TRANS');
insert into partitioned (fecha,notas) values ('2012-03-12 00:03:00', 'IN TRANS');
update partitioned set notas='IN TRANS' where id=4;
select * from partitioned;
+----+---------------------+----------+
| id | fecha | notas |
+----+---------------------+----------+
| 4 | 2012-03-11 00:02:00 | IN TRANS |
| 5 | 2012-03-11 00:03:00 | BEFORE |
| 6 | 2012-03-11 00:04:00 | BEFORE |
| 7 | 2012-03-11 00:03:00 | IN TRANS |
| 1 | 2012-03-12 00:00:00 | BEFORE |
| 2 | 2012-03-12 00:01:00 | BEFORE |
| 3 | 2012-03-12 00:02:00 | BEFORE |
| 8 | 2012-03-12 00:03:00 | IN TRANS |
+----+---------------------+----------+
Client2:
select * from partitioned;
+----+---------------------+--------+
| id | fecha | notas |
+----+---------------------+--------+
| 4 | 2012-03-11 00:02:00 | BEFORE |
| 5 | 2012-03-11 00:03:00 | BEFORE |
| 6 | 2012-03-11 00:04:00 | BEFORE |
| 1 | 2012-03-12 00:00:00 | BEFORE |
| 2 | 2012-03-12 00:01:00 | BEFORE |
| 3 | 2012-03-12 00:02:00 | BEFORE |
+----+---------------------+--------+
alter table partitioned drop partition p20120311;
Client1:
commit;
select * from partitioned;
+----+---------------------+----------+
| id | fecha | notas |
+----+---------------------+----------+
| 1 | 2012-03-12 00:00:00 | BEFORE |
| 2 | 2012-03-12 00:01:00 | BEFORE |
| 3 | 2012-03-12 00:02:00 | BEFORE |
| 8 | 2012-03-12 00:03:00 | IN TRANS |
+----+---------------------+----------+
Client2:
select * from partitioned;
+----+---------------------+----------+
| id | fecha | notas |
+----+---------------------+----------+
| 1 | 2012-03-12 00:00:00 | BEFORE |
| 2 | 2012-03-12 00:01:00 | BEFORE |
| 3 | 2012-03-12 00:02:00 | BEFORE |
| 8 | 2012-03-12 00:03:00 | IN TRANS |
+----+---------------------+----------+
Now the slave is stopped wit the error:
Could not execute Update_rows event on table Probador.partitioned; Can't find record in 'partitioned', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.XXXXX, end_log_pos XXXXXXX
Why is happening that ? It seems like a bug. This situation is not so strange if you think in date partitioned tables in wich you are deleting old values or moving to another table/server. If you have an app that is inserting with wrong date (old date) and you are droping or moving that partition automatically at night and that drops happens during a transaction of this app you could hit this bug/error.