MySQL Bugs: #113477: Support ALGORITHM=INSTANT for change data type to larger type

Bug #113477	Support ALGORITHM=INSTANT for change data type to larger type
Submitted:	20 Dec 2023 17:13	Modified:	27 Dec 2023 17:45
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This is a feature request.

ALGORITHM=INSTANT for add/drop column has been very useful for us in MySQL 8.0, and covers a large portion of our schema changes.

Our next most common change is adding indexes (where we would like to use ALGORITHM=INPLACE, but are blocked by bug #113476).

The third most common change is to modify the data type of a column, which currently requires the table to be rebuilt. I understand why this is required in some cases as data could be truncated, however most of the time our changes will be made to larger types. i.e.

INT -> BIGINT
VARCHAR(10) -> VARCHAR(60)

If this smaller subset could be supported by ALGORITHM=INSTANT, we will be able to satisfy the vast majority of our schema changes without table rebuilds.

How to repeat:
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;
ALTER TABLE t1 CHANGE b b VARCHAR(100), ALGORITHM=INSTANT;

.. 

mysql> ALTER TABLE t1 CHANGE b b VARCHAR(100), ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

Suggested fix:
See description

Hello Morgan,

Thank you for the feature request!

regards,
Umesh

Quick edit:
Part of the description is kind of misleading, since VARCHAR(10) -> VARCHAR(60) is "INPLACE" but really should be INSTANT (reported in bug #113355)

But to follow is correct: varchar(60) -> varchar(100) changes the pointer size internally which currently requires a full table copy. This feature request is for it use versioning internally so it does not require that.