MySQL Bugs: #113355: DROP/RENAME index, increase VARCHAR should really be ALGORITHM=INSTANT

Bug #113355	DROP/RENAME index, increase VARCHAR should really be ALGORITHM=INSTANT
Submitted:	6 Dec 2023 17:45	Modified:	18 Apr 2024 15:27
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The following DDL statements fail as INSTANT:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
 id INT NOT NULL PRIMARY KEY auto_increment,
 b VARCHAR(60) NOT NULL,
 c VARCHAR(60) NOT NULL,
 INDEX (b)
);
INSERT INTO t1 SELECT NULL, 'aaa', 'bbb' FROM dual;
INSERT INTO t1 SELECT NULL, 'aaa', 'bbb'  FROM t1 a JOIN t1 b JOIN t1 c;
INSERT INTO t1 SELECT NULL, 'aaa', 'bbb'  FROM t1 a JOIN t1 b JOIN t1 c;
INSERT INTO t1 SELECT NULL, 'aaa', 'bbb'  FROM t1 a JOIN t1 b JOIN t1 c;
INSERT INTO t1 SELECT NULL, 'aaa', 'bbb'  FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000000;

ALTER TABLE t1 DROP INDEX b, ALGORITHM=INSTANT;
ALTER TABLE t1 RENAME INDEX b TO idx_b, ALGORITHM=INSTANT;

This is despite that they are very much different in complexity from other "INSTANT" operations, such as adding an index. i.e. compare the runtime of the following:

ALTER TABLE t1 RENAME INDEX b TO idx_b;
ALTER TABLE t1 DROP INDEX idx_b;
ALTER TABLE t1 ADD INDEX (b);

How to repeat:
mysql> ALTER TABLE t1 DROP INDEX b, ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
mysql> ALTER TABLE t1 RENAME INDEX b TO idx_b, ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

mysql> ALTER TABLE t1 RENAME INDEX b TO idx_b;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 DROP INDEX idx_b;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX (b);
Query OK, 0 rows affected (9.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Suggested fix:
My understanding is that drop index might also have to remove entries from the AHI (and that removing from the buffer pool is async). If this is a concern, it is reasonable that DROP INDEX is only INSTANT when AHI is disabled.

But you can see from the example that the time complexity is very different between these operations, making it difficult to allow 'INPLACE' operations because some are fast enough to be safe on production systems, and some are not.

This makes it difficult for tooling (such as Spirit) which has to implement a parser to detect 'safe' inplace statements. i.e. https://github.com/cashapp/spirit/pull/251

The tooling detection is error prone, since if a statement contains both an INSTANT and an INPLACE operation, it must use a full-copy algorithm. This is because INSTANT is sometimes not-instant (i.e. >64 instant add column operations have been run).

Sorry s/This is despite that they are very much different in complexity from other "INSTANT" operations, such as adding an index/This is despite that they are very much different in complexity from other "INPLACE" operations, such as adding an index/ in description :-)

Hello Morgan,

Thank you for the report and feedback.

regards,
Umesh

I have updated the description since there is another scenario which should be INSTANT. Extending a VARCHAR (same pointer size):

mysql> ALTER TABLE t1 MODIFY b VARCHAR(61) NOT NULL, ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
mysql> ALTER TABLE t1 MODIFY b VARCHAR(61) NOT NULL, ALGORITHM=INPLACE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

For different pointer size the behavior is correct because it does actually copy the table.

Here is another use-case that makes no sense as INPLACE:

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1 (
    ->     id INT NOT NULL PRIMARY KEY auto_increment,
    ->     b INT NOT NULL,
    ->     INDEX (b)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE t1 ALTER INDEX b INVISIBLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ALTER INDEX b VISIBLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ALTER INDEX b INVISIBLE, ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
mysql> ALTER TABLE t1 ALTER INDEX b INVISIBLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ALTER INDEX b VISIBLE, ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.