Operations that modify column types block DML operations on data nodes and affect your business. This topic describes how to use Online Modify Column (OMC) in PolarDB-X to modify column types online.

Precautions

Limits

OMC is subject to the following limits:

  • OMC is supported only in PolarDB-X V5.4.15 and later.
  • Only one column can be modified for each ALTER TABLE statement.
  • The column that you want to modify cannot be an auto-increment column.
  • The column that you want to modify cannot be a primary key column.
  • The column that you want to modify cannot be a shard key column of a primary table or GSI.
  • The column that you want to modify cannot be a column of a GSI. (To modify it, run /*+TDDL: cmd_extra(OMC_ALTER_TABLE_WITH_GSI=true)*/ first.)
  • The column that you want to modify cannot be a column of a table that has a full-text index.
Types
The following four categories of column types are supported:
  • Exact value: tinyint, tinyint unsigned, smallint, smallint unsigned, mediumint, mediumint unsigned, int, int unsigned, bigint, bigint unsigned, and decimal.
  • Approximate value: float and double.
  • String: char, varchar, binary, and varbinary.
  • Date and time: date, datetime, time, and year.
Take note of the following limits when you modify the column type:
  • The column types before and after the modification must be within the same category.
  • The types before and after the modification cannot be the same. (The varchar or varbinary values with different lengths are considered different types.)

Use OMC

If no ALGORITHM clause is specified or you set ALGORITHM to other values, such as INPLACE or COPY, in the ALTER TABLE statement, the statement blocks DML operations. OMC takes effect only when you set ALGORITHM to OMC.

Examples
  • Execute the following statement to create a test table named t1:
    CREATE TABLE t1(a int primary key, b tinyint) dbpartition by hash(a);
  • Execute the following statement to modify the column type in the t1 table:
    ALTER TABLE t1 MODIFY COLUMN b int, ALGORITHM=OMC;
  • Execute the following statement to modify the name and type of column b in the t1 table:
    ALTER TABLE t1 CHANGE COLUMN b c bigint, ALGORITHM=OMC;
Note If a local index is created on the column, you may see a temporary local index created with the DDL statement when you execute SHOW CREATE TABLE. If a local index of the column is used in the FORCE INDEX hint of an SQL statement, the SQL statement may fail when you execute the DDL statement.