[MDEV-30906] Variable alter_algorithm=COPY takes precedence over explicit ALGORITHM clause Created: 2023-03-22  Updated: 2023-08-15

Status: Stalled
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Variables
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 11.1

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-31601 Some ALTER TABLE .. fail when they wo... Closed
relates to MDEV-30984 Online ALTER table is denied with non... Closed

 Description   

ALTER TABLE with explicit unsupported algorithm is still executed when alter_algorithm variable is set to COPY. Apparently, the explicit algorithm is quietly ignored.

10.4 99ee200b

 
MariaDB [test]> create table t1 (a int);
 
Query OK, 0 rows affected (0.039 sec)
 
 
 
MariaDB [test]> alter table t1 force, algorithm=instant;
 
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
 
MariaDB [test]> set alter_algorithm= copy;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> alter table t1 force, algorithm=instant;
 
Query OK, 0 rows affected (0.123 sec)              
Records: 0  Duplicates: 0  Warnings: 0

It contradicts the variable documentation, as well as common sense.

Noticeably, it's not the universal rule "variable takes precedence over the clause", apparently it is specific to the COPY algorithm. It doesn't happen with alter_algorithm=INPLACE which is also supported for the operation:

MariaDB [test]> set alter_algorithm= inplace;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> alter table t1 force, algorithm=instant;
 
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE

Even if was at some point intentional (maybe some legacy reasons), I think it should be made uniform and consistent. I'm not sure however that it can be done post-GA, so I'm setting fix version to the next RC. Please adjust if needed.

MTR

 
create table t1 (a int);
 
 
 
set alter_algorithm= DEFAULT;
 
 
 
--error ER_ALTER_OPERATION_NOT_SUPPORTED
 
alter table t1 force, algorithm=instant;
 
 
 
set alter_algorithm= INPLACE;
 
 
 
--error ER_ALTER_OPERATION_NOT_SUPPORTED
 
alter table t1 force, algorithm=instant;
 
 
 
set alter_algorithm= COPY;
 
 
 
--error ER_ALTER_OPERATION_NOT_SUPPORTED
 
alter table t1 force, algorithm=instant;
 
 
 
# Cleanup
 
drop table t1;


mysqltest: At line 16: query 'alter table t1 force, algorithm=instant' succeeded - should have failed with errno 1845...



 Comments   
Comment by Nikita Malyavin [ 2023-06-28 ]

Will likely be fixed as part of MDEV-30984:
the new patch causes problems with main.mysql57_virtual:

# We can't do online changes, as the MariaDB storage is incompatible with MySQL
 
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 
alter online table mysql57_virtual comment "I am now a MariaDB table", algorithm=nocopy;

Comment by Elena Stepanova [ 2023-07-01 ]

For a note to interested parties, fixing this might potentially cause a problem with OM => NS replication, as always when something that was allowed before becomes prohibited.

Still, I think it is an obvious error which needs to be fixed, and I don't see how it can be done without potentially breaking replication. Hopefully not so many users have it set to a non-default value and still use an explicit ALGORITHM clause in their ALTERs.

Of course, it concerns having alter-algorithm as a startup option. If the variable is changed dynamically, like in the test case, it will cause replication problems anyway, as many dynamic variables do.

Generated at Thu Feb 08 10:19:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.