[#MDEV-28961] Partition management with non-default alter_algorihm / old_alter

In 10.3 a new system variable alter_algorithm was introduced, and previously boolean variable old_alter_table was turned into an alias of alter_algorithm.

It made partition management with a non-default value of old_alter_table or alter_algorithm impossible. Any value other than the default alter_algorithm=DEFAULT old_alter_table=0 makes ALTER fail:

--source include/have_partition.inc

create table t (a int) partition by hash(a) partitions 2;

set old_alter_table= 1;

alter table t add partition partitions 2;

set alter_algorithm= COPY;

alter table t add partition partitions 2;

# Cleanup

drop table t;

10.3 be99d0dd
set old_alter_table= 1;
alter table t add partition partitions 2;
mysqltest: At line 5: query 'alter table t add partition partitions 2' failed: 1846: ALGORITHM=COPY/INPLACE is not supported. Reason: Partition specific operations do not yet support LOCK/ALGORITHM. Try ALGORITHM=DEFAULT
set alter_algorithm= COPY;
alter table t add partition partitions 2;
mysqltest: At line 7: query 'alter table t add partition partitions 2' failed: 1846: ALGORITHM=COPY/INPLACE is not supported. Reason: Partition specific operations do not yet support LOCK/ALGORITHM. Try ALGORITHM=DEFAULT

The confusing message does not help, as it says that COPY/INPLACE is not supported, but is thrown on any value of alter_algorithm:

set alter_algorithm= NOCOPY;

alter table t add partition partitions 2;

mysqltest: At line 9: query 'alter table t add partition partitions 2' failed: 1846: ALGORITHM=COPY/INPLACE is not supported. Reason: Partition specific operations do not yet support LOCK/ALGORITHM. Try ALGORITHM=DEFAULT

set alter_algorithm= INSTANT;

alter table t add partition partitions 2;

mysqltest: At line 13: query 'alter table t add partition partitions 2' failed: 1846: ALGORITHM=COPY/INPLACE is not supported. Reason: Partition specific operations do not yet support LOCK/ALGORITHM. Try ALGORITHM=DEFAULT

And when explicit locking is involved, it also looks strange:

alter online table t add partition partitions 2;

mysqltest: At line 4: query 'alter online table t add partition partitions 2' failed: 1846: LOCK=NONE/SHARED/EXCLUSIVE is not supported. Reason: Partition specific operations do not yet support LOCK/ALGORITHM. Try LOCK=DEFAULT

If there are cases when none of allowed values for locking modes and alter algorithms is supported and "default" means something other than of of the enumerated values, at least it needs to be documented (maybe it already is, I couldn't find it).

[MDEV-28961] Partition management with non-default alter_algorihm / old_alter_table is not possible Created: 2022-06-27 Updated: 2023-11-28
Status:	Open
Project:	MariaDB Server
Component/s:	Data Definition - Alter Table, Partitioning
Affects Version/s:	10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s:	10.11

[MDEV-28961] Partition management with non-default alter_algorihm / old_alter_table is not possible Created: 2022-06-27 Updated: 2023-11-28