Description:
Innodb has supported 'row version' since 8.0.29, which is used to implement instant ddl. Now only instant add column and instant drop column are supported, we can extend this feature to support instant modify column.
On behalf of our team(Tencent MySQL Team), I will contribute an implementation of instant modify column to MySQL. The code was originally written by my colleague Yuan Zhang(https://bugs.mysql.com/search.php?cmd=display&status=All&severity=all&reporter=9498938) on mysql-5.7.18, later Zheng Lai (https://bugs.mysql.com/search.php?cmd=display&status=All&severity=all&reporter=14107507) ported it to mysql-8.0.22, now I re-implement it on mysql-8.0.33 and we decide to contribute it to mysql community.
In our implementation, these kinds of columns can be modified instantly:
char -> char
char -> varchar
varchar -> char
varchar -> varchar
binary -> binary
binary -> varbinary
varbinary -> binary
varbinary -> varbinary
tinyint/smallint/mediumint/int -> bigint
tinyint/smallint/mediumint -> int
tinyint/smallint -> mediumint
tinyint -> smallint
Limitations:
1. each column can only be modified once
2. the length should not shrink
3. nullable to non-nullable(or non-nullable to nullable) is not supported()
4. signed to unsigned(or unsigned to signed) is not supported
5. import and export are not supported
6. compressed table and encrypted table are not supported
7. index column is not supported
8. upgraded instant(instant add column in V1) is not supported
9. instantly modified columns can not be dropped instantly(can be extended in the future)
Examples:
create table t1(id int,c1 int, c2 char(10));
alter table t1 modify column id bigint, algorithm=instant
alter table t1 modify column c2 varchar(20), algorithm=instant;
How does it work?
When a column is modified instantly, we store it's original mtype, prtype, length and version modified in the DD se_private_data. We use row version stored in record header to determine whether a field should be converted or not. If row version in record header is less than the column's modified version, it means that the field of this column is stored in old format in this record, we should use the old mtype, prtype and length to get the data and convert it to new format. All newly inserted records are in the newest format.
How to convert a field from old format to new format?
-----------------------------------------------------
| old type | new type | conversion |
-----------------------------------------------------
| char | char | pad spaces |
-----------------------------------------------------
| char | varchar | remove trailing spaces |
-----------------------------------------------------
| varchar | char | pad spaces |
-----------------------------------------------------
| varchar | varchar | --- |
-----------------------------------------------------
| binary | binary | pad zero bytes |
-----------------------------------------------------
| binary | varbinary | --- |
-----------------------------------------------------
| varbinary | binary | pad zero bytes |
-----------------------------------------------------
| varbinary | varbinary | --- |
-----------------------------------------------------
Conversions between integers are a little more complicated.
1. Convert unsigned int to unsigned bigint, we only need to pad 0x00 before the first byte, for example:
1(unsigned int): 0x00 0x00 0x00 0x01 -> 1(unsigned bigint): 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x01
2. Convert int to bigint, we need to pad 0x00 or 0xff before the first byte and reset the signed bit, for example:
1(int): 0x80 0x00 0x00 0x01 -> 1(bigint): 0x80 0x00 0x00 0x00 0x00 0x00 0x00 0x01
-1(int): 0x7f, 0xff, 0xff, 0xff -> -1(bigint): 0x7f, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff
3. Other conversions are similar.
How to repeat:
N/A
Suggested fix:
I'll attach a patch later
