Online modify column type - ApsaraDB RDS - Alibaba Cloud Documentation Center

AliSQL supports the Online Modify Column feature. This feature moves the column type modification operation to the InnoDB engine layer, which lets you make online changes without blocking read and write operations.

Feature description

Background: In community MySQL 5.6 and later, most Data Definition Language (DDL) statements support online execution. During an online DDL execution, you can perform standard read and write operations on the table. However, modifying a column type required the COPY DDL method, even in versions up to MySQL 8.0. AliSQL enhances community MySQL by adding the online column type modification feature. This feature lets you modify columns using the INPLACE DDL method.

Introduction: The AliSQL Online Modify Column feature modifies column types without using the COPY method to read and insert data row-by-row at the server layer. Instead, it moves the operation to the InnoDB engine layer and reuses the community INPLACE DDL execution frame. The entire process is identical to other INPLACE DDL operations. You can perform standard read and write operations on the table while the column type is being modified.

Applicability

To use the online column type modification feature, the database version of your instance must meet the following conditions. If the minor version does not meet the requirements, upgrade the minor engine version.

MySQL 8.0: Minor engine version 20250731 or later.
MySQL 5.7: Minor engine version 20251031 or later.

The online column type modification feature has the following limitations:

Engine limitation: Only the InnoDB engine is supported.
Column type limitation: Only type modification or length extension for the following data types is supported:
- Increasing integer types (Type modification): This includes increasing the size of TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. For example, changing INT to BIGINT is supported, but changing BIGINT to INT is not.
- Extending string types (Length extension): This includes extending the length of CHAR and VARCHAR types. For example, when you change CHAR(M) to CHAR(N), N must be greater than M.
Other limitations: Modifying columns that are part of a partition key is not supported.

Parameter management

Parameter description

The online column type modification feature is controlled by the following parameter:

Parameter name	Description
`loose_rds_upgrade_datatype_online_enable`	Description: Enables or disables the online modify column type feature. Scope: Global parameter. Data type: `Boolean`. Default value: `OFF`. Valid values: `ON` or `OFF`. Restart required: No.

Modify parameters

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left navigation pane, click Parameter Settings.
On the Editable Parameters tab, find the parameter and set its value.
Click OK, and then click Submit Parameters. In the dialog box that appears, select when to apply the changes.

Usage

In the console, you can set the loose_rds_upgrade_datatype_online_enable parameter to ON to enable the online column type modification feature. Assume that you want to modify a table named t1 that has the following schema:

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;

Modify column type by specifying the INPLACE algorithm

You can specify the INPLACE algorithm to modify the column type online. This includes type modification and length extension.

Type modification: Specify the INPLACE algorithm to change the type of the c1 column to bigint.
Length extension: Specify the INPLACE algorithm to extend the length of the c2 and c3 columns.

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

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

-- 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 column type without specifying an algorithm

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

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