WL#6554: InnoDB: enlarge varchar size online
Affects: Server-5.7 — Status: Complete — Priority: Medium
We want to allow online alter column modify when increasing varchar from less than 255 bytes to up to 255 bytes. (We will also allow increasing the size from more than 255 bytes.) WL#6554 is only about the InnoDB implementation.
Requirement: R001 It should be possible to extend varchar size using algorithm=inplace (extending from less than 256 bytes to 256 or more is not supported) As usual, if something is not supported with ALGORITHM=INPLACE, we will fall back to ALGORITHM=COPY (copying the entire table, or refusing the operation if ALGORITHM=INPLACE was explicitly specified). For example, shrinking a column or crossing the 255-byte maximum length boundary will require copying of the table. An excerpt from innodb-alter-wl6554.result: # We cannot shrink the size or cross the 256-byte boundary. ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(6); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(256); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. We can extend from less-than-256 to 256 without specifying an explicit ALGORITHM (letting it to use ALGORITHM=COPY): ALTER TABLE t2 CHANGE COLUMN fk fk VARCHAR(256); Then, we can extend further, until we hit a limit that causes MySQL to treat VARCHAR as BLOB: ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(300); ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(99999); ERROR 42000: BLOB/TEXT column 'fk' used in key specification without a key length
The ALTER TABLE API introduces the flag ALTER_COLUMN_EQUAL_PACK_LENGTH for extending a VARCHAR column so that the character set and collation remains the same. This flag is NOT set when the column is being extended from up to than 255 bytes to at least 256 bytes. This flag is NOT set when the column size is being shrunk. This is perfect for InnoDB, because when the maximum length is less than 256 bytes, the current length will be stored in one byte. When the 256-byte boundary is not being crossed, we can extend the VARCHAR in-place, by just updating the data dictionary. Add the functions innobase_enlarge_column_try(), innobase_enlarge_columns_try() similar to innobase_rename_column_try() and innobase_rename_columns_try(). Rename the function innobase_rename_columns_cache() to innobase_rename_or_enlarge_columns_cache(). If the table is being rebuilt, there is no need to resize (or rename) because the rebuilt copy of the table will use the altered definition from the beginning. The in-place resizing will only be invoked when the table is not being rebuilt (copied).
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.