[#MDEV-25477] Auto-create breaks replication when triggering event was not replicated

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.

[MDEV-25477] Auto-create breaks replication when triggering event was not replicated Created: 2021-04-21 Updated: 2022-05-06 Resolved: 2022-04-23
Status:	Closed
Project:	MariaDB Server
Component/s:	Documentation, Partitioning, Replication, Versioned Tables
Affects Version/s:	None
Fix Version/s:	N/A

[MDEV-25477] Auto-create breaks replication when triggering event was not replicated Created: 2021-04-21 Updated: 2022-05-06 Resolved: 2022-04-23