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
-
-
- Closed
-
Activity
You can see from the description above that auto-creation does not work on slave – it can't work on slave because there is no DML.
Discrepancy in names and in the number of partitions is not acceptable for replication, because it will always break replication at the end. Either auto-creation works with replication, and then the table structure consistency must be maintained, or it doesn't work with replication.
And yes, incompatibility with replication is a very big limitation.
We should resurrect MDEV-25347 fix as it logs ALTER TABLE ADD PARTITION on auto-create. serg what do you think?
This seems to be rather trivial to fix? On auto-create you can set "non transactional tables were modified" flag, and then the transaction will be written into binlog all right, with ROLLBACK at the end. Or you set a flag OPTION_KEEP_LOG. See trans_cannot_safely_rollback() — you need to trick it to return true.
MDEV-17554 was moved to 10.9, so I am changing fixVersion for this bug to 10.9. I am also removing the 10.8V1 support case ID label
This doesn't matter as auto-creation also works on slave. Discrepancy in names of history partitions is acceptable but this should be documented somewhere. I don't believe this is a big limitation as neither current data nor history get lost or made inconsistent across the nodes.