[MDEV-31601] Some ALTER TABLE .. fail when they worked before, and with a wrong error message Created: 2023-07-02  Updated: 2023-08-16  Resolved: 2023-08-16

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: N/A
Fix Version/s: 11.2.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Nikita Malyavin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-16329 Engine-independent online ALTER TABLE Closed
Relates
relates to MDEV-30906 Variable alter_algorithm=COPY takes p... Stalled

 Description    

CREATE TABLE t (a INT);
 
INSERT INTO t VALUES (1),(2);
 
ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
 
 
 
DROP TABLE t;

ALTER here can have actual operations before ALGORITHM, I just over-simplified it to allow ALTER be executed multiple times without intermediate changes.

In the above example, before MDEV-16329 ALTER works with ALGORITHM=NOCOPY and ALGORITHM=INSTANT (and of course with COPY and DEFAULT), while fails with ALGORITHM=INPLACE:

11.2 2867894ac6ca23fc2f82cfad2dd510351f597325

 
MariaDB [test]> ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
 
Query OK, 2 rows affected (0.044 sec)              
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> ALTER TABLE t ALGORITHM=INPLACE, ORDER BY a;
 
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
MariaDB [test]> 
MariaDB [test]> ALTER TABLE t ALGORITHM=INSTANT, ORDER BY a;
 
Query OK, 2 rows affected (0.056 sec)              
Records: 2  Duplicates: 0  Warnings: 0

This is in itself weird, and may be a bug.

After MDEV-16329 the behavior changed. If the above was indeed a bug, then functionally the change may be correct, but it leaves us with a wrong error message:

bb-11.2-oalter adcf5dfa8d646b9fbd2bb1a7a4f5a3bb8a7e659c

 
MariaDB [test]> ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
 
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
MariaDB [test]> 
MariaDB [test]> ALTER TABLE t ALGORITHM=INPLACE, ORDER BY a;
 
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
MariaDB [test]> 
MariaDB [test]> ALTER TABLE t ALGORITHM=INSTANT, ORDER BY a;
 
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY

Regardless which algorithm is specified, it complains about INPLACE.

Or, if the old behavior was valid and on whatever reason INPLACE was indeed not applicable, but NOCOPY and INSTANT were, then the new behavior is functionally a regression.

 Comments   
Comment by Elena Stepanova [ 2023-07-03 ]

Same happens upon ENGINE change.

CREATE TABLE t (a INT) ENGINE=Aria;
 
ALTER TABLE t ENGINE=MyISAM, ALGORITHM=NOCOPY;
 
SHOW CREATE TABLE t;
 
DROP TABLE t;

bb-11.2-oalter adcf5dfa8d6

 
mysqltest: At line 2: query 'ALTER TABLE t ENGINE=MyISAM, ALGORITHM=NOCOPY' failed: ER_ALTER_OPERATION_NOT_SUPPORTED (1845): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY

and upon ALTER on a temporary table

CREATE TEMPORARY TABLE t (f INT);
 
ALTER TABLE t FORCE, ALGORITHM=NOCOPY;


mysqltest: At line 2: query 'ALTER TABLE t FORCE, ALGORITHM=NOCOPY' failed: ER_ALTER_OPERATION_NOT_SUPPORTED (1845): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY

Comment by Sergei Golubchik [ 2023-07-07 ]

6e6e950c8ab is ok to push

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