Bug #93207 Cannot change the index type with ALGORITHM=INSTANT

• Submitted: 15 Nov 2018 10:22
• Modified: 3 Jan 2019 11:43
• Reporter: Ramesh Sivaraman
• Status: Not a Bug
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 8.0.13
• OS: Any
• CPU Architecture: Any
• Tags: debug

[15 Nov 2018 10:22] Ramesh Sivaraman

Description:
Not able to change the index type with ALGORITHM=INSTANT. As per the document, we can change the index type  with ALGORITHM=INSTANT.

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

Is this document bug?

8.0.13>show create table test3.sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `c1` enum('a','b','c','d') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `cidx` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

8.0.13>
8.0.13>ALTER TABLE test3.sbtest1 DROP INDEX cidx,ADD INDEX cidx(c,pad) USING BTREE, ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
8.0.13>
8.0.13>

How to repeat:
Testcase

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `c1` enum('a','b','c','d') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `cidx` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

ALTER TABLE test3.sbtest1 DROP INDEX cidx,ADD INDEX cidx(c,pad) USING BTREE, ALGORITHM=INSTANT;

[16 Nov 2018 7:54] MySQL Verification Team

Thank you for the bug report.

[23 Nov 2018 20:57] Daniel Price

Posted by developer:
 
The example posted above adds a column to the index, which is not supported by the INSTANT algorithm. Do not add a column to the index and the INSTANT operation is successful:

mysql> CREATE TABLE `sbtest1` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `k` int(11) NOT NULL DEFAULT '0',
    ->   `c` char(120) NOT NULL DEFAULT '',
    ->   `pad` char(60) NOT NULL DEFAULT '',
    ->   `c1` enum('a','b','c','d') DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `k_1` (`k`),
    ->   KEY `cidx` (`c`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4
    -> COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.14 sec)

mysql> ALTER TABLE sbtest1 DROP INDEX cidx,ADD INDEX cidx(c) USING BTREE,ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0