ApsaraDB RDS:Online column type modification

How it works

Background: Since MySQL 5.6, most Data Definition Language (DDL) statements have supported Online DDL, which allows read and write operations on a table during the DDL process. However, even up to MySQL 8.0, modifying a column's type required the COPY DDL method. AliSQL builds on community MySQL by implementing the Online Modify Column feature, which supports modifying columns by using the INPLACE DDL method.

Introduction: The AliSQL Online Modify Column feature avoids the need to read and insert rows one by one at the server layer, a process performed by the COPY method. Instead, it pushes the column type modification operation down to the InnoDB engine layer and reuses the community INPLACE DDL execution framework. The entire process is identical to other INPLACE DDL operations, allowing normal read and write operations on the table during the modification.

Prerequisites and limitations

To use the Online Modify Column feature, your instance must use one of the following database versions. If its minor engine version is older than required, you can upgrade the minor engine version.

• MySQL 8.4

• MySQL 8.0: minor engine version 20250731 or later.

• MySQL 5.7: minor engine version 20251031 or later.

The Online Modify Column feature is subject to the following limitations:

• Engine limitation: Only the InnoDB engine is supported.

• Column type limitation: Only type modification and length extension are supported for the following data types:

  • Increasing the size of integer types (type modification): This includes changing from a smaller integer type to a larger one, such as TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. For example, modifying a column from INT to BIGINT is supported, but modifying from BIGINT to INT is not.

  • Increasing the length of string types (length extension): This applies to extending 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 the Online Modify Column feature, set the loose_rds_upgrade_datatype_online_enable parameter to ON in the console. The following examples use a table named t1 with the following structure:

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

Specify the INPLACE algorithm

You can specify the INPLACE algorithm for online column modifications, including type modification and length extension:

• Type modification: Change the type of the c1 column to bigint.

• Length extension: Extend the length of the c2 and c3 columns.

-- Type modification: Use the INPLACE algorithm to modify the c1 column type.
ALTER TABLE `t1` MODIFY `c1` bigint DEFAULT NULL, ALGORITHM=INPLACE;

-- View the 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: Use the INPLACE algorithm to extend the length of the c2 and c3 columns.
ALTER TABLE `t1` MODIFY `c2` char(20) DEFAULT NULL, ALGORITHM=INPLACE;
ALTER TABLE `t1` MODIFY `c3` varchar(100) DEFAULT NULL, ALGORITHM=INPLACE;

-- View the 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)

Do not specify an algorithm

If you do not specify an algorithm, the system automatically selects the most appropriate algorithm for the column type modification.

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