Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
-
None
Description
Partitioning auto-creation cannot be enabled in a replication topology, unless only non-transactional engines are used (which is hardly realistic these days).
The reason is that when a partition is auto-created within a transaction, but the transaction is later rolled back, there will be nothing written in the binary log. Thus, the master will have the new partition, but the slave will not. This discrepancy will almost inevitably cause a replication abort some time later.
If it's intentional, at the very least it has to be documented, as it is a pretty big limitation.
Example:
--source include/have_partition.inc
|
--source include/have_innodb.inc
|
--source include/master-slave.inc
|
|
create table t (a int) engine=InnoDB with system versioning |
partition by system_time interval 1 hour auto; |
|
insert into t values (1),(2); |
set @@timestamp= @@timestamp+3601; |
|
start transaction; |
delete from t; |
rollback; |
|
show create table t; |
|
--sync_slave_with_master
|
|
show create table t; |
|
--connection master
|
alter table t drop partition p0; |
|
--sync_slave_with_master |
After rollback, the master has 2 historical partitions, while the slave has only one:
bb-10.6-midenok-MDEV-17554 edbc73ac4 |
show create table t; |
Table Create Table |
t CREATE TABLE `t` ( |
`a` int(11) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING |
PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2021-04-21 23:00:00' AUTO |
PARTITIONS 3
|
connection slave; |
show create table t; |
Table Create Table |
t CREATE TABLE `t` ( |
`a` int(11) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING |
PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2021-04-21 23:00:00' AUTO |
PARTITIONS 2
|
So, the master is allowed to drop partition p0, but the slave is not:
Last_Errno 4128
|
Last_Error Error 'Wrong partitions for `t`: must have at least one HISTORY and exactly one last CURRENT' on query. Default database: 'test'. Query: 'alter table t drop partition p0'
|
Also test case from MDEV-25478 must be considered.
Attachments
Issue Links
- is caused by
-
MDEV-17554 Auto-create history partitions for system-versioned tables
- Closed
- is duplicated by
-
MDEV-25478 Auto-create: Auto partitioning is unsafe for row-based replication
- Closed