[MDEV-22603] ALTER TABLE gives false reason for refusing to instantly drop primary key Created: 2020-05-17  Updated: 2023-04-26

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Trivial
Reporter: Elena Stepanova Assignee: Thirunarayanan Balathandayuthapani
Resolution: Unresolved Votes: 0
Labels: None


 Description    

--source include/have_innodb.inc
 
create or replace table t1 (a int, b int, primary key(a)) engine=InnoDB;
 
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 
alter table t1 drop primary key, algorithm=instant;
 
alter table t1 drop primary key, add primary key(b), algorithm=instant;

10.5 69077dea25

 
MariaDB [test]> create or replace table t1 (a int, b int, primary key(a)) engine=InnoDB;
 
Query OK, 0 rows affected (1.101 sec)
 
 
 
MariaDB [test]> alter table t1 drop primary key, algorithm=instant;
 
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY
 
 
 
MariaDB [test]> alter table t1 drop primary key, add primary key(b), algorithm=instant;
 
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE

Apparently in the first case it means to say that the primary key has to be on the same column, but it doesn't say that.

 Comments   
Comment by Thirunarayanan Balathandayuthapani [ 2020-05-17 ]

Inplace operation supports only when DROP PRIMARY KEY is only allowed in combination with ADD PRIMARY KEY. I think it is not a bug.
marko can you confirm the above ?

Comment by Elena Stepanova [ 2020-05-17 ]

My only complaint is about the error message . Sorry, I've realized it might be unclear, I said it in the bug summary but not in the description.

Comment by Marko Mäkelä [ 2020-05-18 ]

elenst, in MDEV-13134 we introduced ALGORITHM=INSTANT and ALGORITHM=NOCOPY as strict subsets of ALGORITHM=INPLACE (which would better have been called ‘engine’ or ‘native’ instead of the misleading ‘inplace’).

The native ALTER TABLE algorithm in InnoDB does support redefining or adding a primary key (even while the table is concurrently being modified), but it does not support dropping a primary key without a replacement.

What would you like the error message to say?

Comment by Elena Stepanova [ 2020-05-18 ]

Repeating what was explained on Slack, my confusion and the reason why I made this note was that the first error message attempts to be specific, but incorrectly so. It says "Reason: Dropping a primary key is not allowed without also adding a new primary key". It can mean only one thing, that if the operation also adds a new primary key, it is supported. So, I add the new primary key in the second ALTER, but then it claims it isn't supported anyway.
Specificity is good, but only when it is accurate. As much as I usually dislike the standard message "ALGORITHM=X is not supported for this operation. Try ALGORITHM=Y", I think it would be more appropriate for the first ALTER than giving the false "Reason".

I don't insist on fixing it though. It was just a side note from something else that I was checking at the time.

Comment by Marko Mäkelä [ 2020-05-18 ]

I am afraid that this cannot be fixed without changing the handler API. ha_innobase::check_if_supported_inplace_alter() does not know what ALGORITHM or LOCK has been requested, and hence it cannot specify a less specific error message.

Also, redundant ADD and DROP pairs (of indexes or columns, possibly also constraints) are being optimized away on the SQL layer before the function in the storage engine is called.

Comment by Jivan Pal [ 2023-04-26 ]

I have also just run into this misleading error message on MariaDB 10.4, understanding it to mean that adding a new primary key would permit the use of ALGORITHM=INSTANT. Given that the handler API cannot be changed, is it possible to change the entire error message ...

Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY

... to something else? If so, I'd suggest this:

Try ALGORITHM=COPY, or add a new primary key and try ALGORITHM=INPLACE.

Generated at Thu Feb 08 09:16:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.