ApsaraDB RDS:Instant column type modification

Features

Background: Starting from version 8.0, community MySQL supports INSTANT DDL. Adding or dropping columns requires only metadata changes and does not involve data copies, allowing the DDL operations to complete in seconds. However, for column type modification, community MySQL 8.0 can only use the COPY DDL method, which is time-consuming. AliSQL extends community MySQL to support instant column type modification by using the INSTANT DDL algorithm.

Introduction: The instant column type modification feature in AliSQL avoids copying data when changing a column type. It uses the community MySQL INSTANT DDL execution framework. The operation changes only metadata. The process is identical to other INSTANT DDL operations and completes in seconds.

Prerequisites

To use this feature, your RDS instance must meet one of the following version requirements. If your version does not meet the requirements, you can upgrade the major engine version or upgrade the minor engine version:

• MySQL 8.4

• MySQL 8.0 with a minor engine version of 20251031 or later

This feature has the following limitations:

• Storage engine: This feature supports only the InnoDB storage engine.

• Index columns: You cannot modify an index column.

• Column types: You can perform only the following column type modifications and length extensions:

  • Widening integer types (column type modification): TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT can be changed only from a smaller type to a larger one. For example, changing INT to BIGINT is supported, but changing BIGINT to INT is not.

  • Extending string types (length extension): You can increase the length of CHAR and VARCHAR types. For example, to modify a column from CHAR(M) to CHAR(N), N must be greater than M.

• Other limitations: You cannot modify a column that is part of a partition key.

Parameter management

Usage

To enable instant column type modification, set the following three parameters to ON in the console:

• loose_rds_upgrade_datatype_online_enable

• loose_rds_upgrade_datatype_instant_enable

• loose_innodb_instant_ddl_enabled

The following examples use a table named t1 with the following structure:

CREATE TABLE `t1` (
  `id` int NOT NULL,
  `c1` char(10) DEFAULT NULL,
  `c2` varchar(10) DEFAULT NULL,
  `c3` char(100) DEFAULT NULL,
  `c4` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Modify a column type with the INSTANT algorithm

You can specify the INSTANT algorithm for both column type modifications and length extensions:

• Column type modification: Specify the INSTANT algorithm to change the type of column c1 to bigint.

• Length extension: Specify the INSTANT algorithm to extend the length of columns c2 and c3.

-- Column type modification: Specify the INSTANT algorithm to modify the column type of c1.
ALTER TABLE `t1` MODIFY `c1` bigint DEFAULT NULL, ALGORITHM=INSTANT;

-- View the modification result.
SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `c1` bigint DEFAULT NULL,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

-- Length extension: Specify the INSTANT algorithm to extend the length of the c2 and c3 columns.
ALTER TABLE `t1` MODIFY `c2` char(20) DEFAULT NULL, ALGORITHM=INSTANT;
ALTER TABLE `t1` MODIFY `c3` varchar(100) DEFAULT NULL, ALGORITHM=INSTANT;

-- View the modification result.
SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `c1` bigint DEFAULT NULL,
  `c2` char(20) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

Modify a column type without an algorithm

If you do not specify an algorithm, the system automatically selects the most appropriate one to modify the column type.

ALTER TABLE `t1` MODIFY `c1` bigint DEFAULT NULL;

Viewing instantly modified columns

You can check the PARENT_PHYSICAL_POSITION field in the INFORMATION_SCHEMA.INNODB_COLUMNS table to identify which columns were modified instantly. A value of -1 indicates a standard column, while any other value indicates a column that was modified instantly.

-- Query the INNODB_TABLES table to find the TABLE_ID of the example table.
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = 'demo/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+-----------------------+-----------------------+---------------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | INITIAL_COLUMN_COUNTS | CURRENT_COLUMN_COUNTS | TOTAL_COLUMN_COUNTS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+-----------------------+-----------------------+---------------------+
|     2197 | demo/t1 |   33 |      7 |  1129 | Dynamic    |             0 | Single     |            0 |                  3 |                     4 |                     4 |                   7 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+-----------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

-- Use the TABLE_ID to retrieve column information from the INNODB_COLUMNS table.
-- In the query result, you can find the instantly modified columns in the PARENT_PHYSICAL_POSITION field (where the value is not -1).
SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID = 2197;
+----------+------+-----+-------+---------+-----+-------------+---------------+---------------+-----------------+--------------+------------------+--------------------------+
| TABLE_ID | NAME | POS | MTYPE | PRTYPE  | LEN | HAS_DEFAULT | DEFAULT_VALUE | VERSION_ADDED | VERSION_DROPPED | PHYSICAL_POS | VERSION_MODIFIED | PARENT_PHYSICAL_POSITION |
+----------+------+-----+-------+---------+-----+-------------+---------------+---------------+-----------------+--------------+------------------+--------------------------+
|     2197 | id   |   0 |     6 |    1283 |   4 |           0 | NULL          |             0 |               0 |            0 |                0 |                       -1 |
|     2197 | c1   |   1 |     6 |    1032 |   8 |           1 | NULL          |             1 |               0 |            6 |                0 |                        3 |
|     2197 | c2   |   2 |    13 | 2162942 |  60 |           1 | NULL          |             2 |               0 |            7 |                0 |                        4 |
|     2197 | c3   |   3 |    12 | 2166799 | 300 |           1 | NULL          |             3 |               0 |            8 |                0 |                        5 |
+----------+------+-----+-------+---------+-----+-------------+---------------+---------------+-----------------+--------------+------------------+--------------------------+
4 rows in set (0.02 sec)