Supported DDL operations for incremental migration and limits

This topic describes the supported DDL operations in different types of incremental migration projects and their limits.

Supported DDL operations in incremental migration from a MySQL database to a MySQL tenant of OceanBase Database and limits

Overview

• Supported DDL operations

  • CREATE TABLE

  • ALTER TABLE

  • TRUNCATE TABLE

  • RENAME TABLE

  • DROP TABLE

  • CREATE INDEX

  • DROP INDEX

• Unsupported DDL operations

  • CREATE/DROP/ALTER DATABASE

  • CREATE/DROP/ALTER FUNCTION

  • CREATE/DROP/ALTER PROCEDURE

  • CREATE/DROP/ALTER EVENT

  • CREATE/DROP/ALTER VIEW

  • CREATE/DROP/ALTER ROLE

  • CREATE/DROP/ALTER TRIGGER

  • CREATE/DROP/ALTER USER

  • CREATE/DROP/ALTER SERVER

  • CREATE/DROP/ALTER TABLESPACE

CREATE TABLE

• When you use the CREATE TABLE statement to create a table, you can specify the IF NOT EXISTS keyword and the LIKE option. Example:

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
  { LIKE old_tbl_name | (LIKE old_tbl_name) }
  

• You cannot use this statement to create a temporary table. The TEMPORARY keyword will be ignored.

  Note

  You can create temporary tables in MySQL tenants of OceanBase Database V3.2.3.

• You cannot use the CREATE TABLE AS SELECT statement to create a table based on the output of a SELECT statement. This is because an empty string will be output in this scenario.

Syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)   
    [table_options]
    [partition_options]

Notice

MySQL tenants of OceanBase Database support only certain character sets.

Do not use unsupported character sets, such as Latin-1. If you forcibly specify an unsupported character set for a MySQL tenant of OceanBase Database, this character set will not be converted during DDL synchronization to the destination database and an error will be returned.

create_definition allows you to create columns, indexes, or constraints.

• Create columns

  When you create columns, you can:

  • Specify the NULL or NOT NULL attribute.

  • Specify the DEFAULT attribute, which can be a constant or a function.

  • Specify the VISIBLE or INVISIBLE attribute.

  • Specify AUTO_INCREMENT.

  • Specify COMMENT.

  • Specify COLLATE.

  • Specify the VIRTUAL | STORED attribute.

  • Specify the UNIQUE KEY, PRIMARY KEY, or KEY attribute. For more information, see Create indexes or constraints following this section.

  • Specify CHECK constraints (supported only in MySQL tenants of OceanBase Database V3.2.3). For more information, see Create indexes or constraints following this section.

  • Specify foreign keys. For more information, see Create indexes or constraints following this section.

  • The following table lists the supported data types.

    Classification		MySQL database	MySQL tenant of OceanBase Database
    Data types	Integer data types	INT	INT
    		TINYINT	TINYINT
    		SMALLINT	SMALLINT
    		MEDIUMINT	MEDIUMINT
    		BIGINT	BIGINT
    		BOOL/BOOLEAN	BOOLEAN
    	Fixed-point data types	DECIMAL	DECIMAL
    		NUMERIC	NUMERIC
    	Floating-point data types	REAL	FLOAT
    		DOUBLE	DOUBLE
    		FLOAT	FLOAT
    	BIT data type	BIT	BIT
    Character data types		CHAR	CHAR
    		NCHAR	CHAR
    		VARCHAR	VARCHAR
    		BINARY	BINARY
    		VARBINARY	VARBINARY
    		LONG VARBINARY	BLOB
    Date and time data types		YEAR	YEAR
    		DATE	DATE
    		TIME	TIME
    		TIMESTAMP	TIMESTAMP
    		DATETIME	DATETIME
    BLOB and TEXT data types		TINYBLOB	TINYBLOB
    		MEDIUMBLOB	MEDIUMBLOB
    		BLOB	BLOB
    		LONGBLOB	LONGBLOB
    		TINYTEXT	TINYTEXT
    		MEDIUMTEXT	MEDIUMTEXT
    		TEXT	TEXT
    		LONGTEXT	LONGTEXT
    Enumeration and set data types		ENUM	ENUM
    		SET	SET
    JSON data type		JSON	TEXT (for MySQL tenants of OceanBase Database of a version earlier than V3.2.3) or JSON (for MySQL tenants of OceanBase Database V3.2.3 and later)

    SERIAL and GEOMETRY data types cannot be converted, such as GEOMETRY, GEOMETRYCOLLECTION, POINT, MULTIPOINT, LINESTRING, MULTILINESTRING, POLYGON, and MULTIPOLYGON.

    Notice:

    If an unsupported data type is used, empty DDL statements will be output.

  The following attributes will not be parsed or converted but only ignored when they are specified in incremental DDL statements:

  • COLUMN_FORMAT

  • ENGINE_ATTRIBUTE

  • SECONDARY_ENGINE_ATTRIBUTE

  • STORAGE

    Due to the limits of MySQL tenants of OceanBase Database, an error may be returned when the converted incremental DDL statements are synchronized to a MySQL tenant of OceanBase Database in the following scenarios:

  • The MySQL tenant of OceanBase Database does not support specifying certain functions or expressions in the DEFAULT attribute.

  • The MySQL tenant of OceanBase Database does not support specifying certain functions or expressions in generated columns.

  • The MySQL tenant of OceanBase Database does not support specifying the UNIQUE KEY, PRIMARY KEY, or KEY attribute for certain field types.

  • The MySQL tenant of OceanBase Database does not support certain collations.

• Create indexes or constraints

  When you create indexes or constraints, you can:

  • Create primary keys.

  • Create unique keys.

  • Create indexes or keys.

  • Create foreign keys on fields, functions, or expressions.

    Specify the ON [DELETE, UPDATE] | RESTRICT | CASCADE | NO ACTION | SET | DEFAULT attribute.

  • Create full-text indexes.

  • Create CHECK constraints in MySQL tenants of OceanBase Database V3.2.3 and later.

  • Create prefix indexes.

  • Specify the ASC or DESC attribute.

    When you create indexes or constraints, you cannot:

  • Create primary keys, unique keys, indexes, keys, or full-text indexes on functions. You can create indexes only on fields.

    Notice

    If incremental DDL statements contain the following unsupported definitions, the whole table creation statement will fail and an empty string will be output.

    Primary key, unique key, index, key, or full-text index definitions that contain functions or expressions. Examples:

      CREATE TABLE functional_index_t1 (col1 INT, PRIMARY KEY (col1, (ABS(col1))));
      CREATE TABLE functional_index_t1(x VARCHAR(30), INDEX idx ((CAST(x->>'$.name' AS CHAR(30)))));
    

  • Create spatial indexes.

  • Specify the ON [DELETE | UPDATE] SET NULL option for foreign keys.

    The following attributes will not be parsed or converted but only ignored when they are specified in incremental DDL statements:

  • USING BTREE or USING HASH

  • KEY_BLOCK_SIZE, index_type, WITH PARSER, COMMENT, VISIBLE | INVISIBLE, ENGINE_ATTRIBUTE, or SECONDARY_ENGINE_ATTRIBUTE in index_option

  • [NOT] ENFORCED for CHECK constraints

  • MATCH FULL | MATCH PARTIAL | MATCH SIMPLE option for foreign keys

    Due to the limits of MySQL tenants of OceanBase Database, an error may be returned when the converted incremental DDL statements are synchronized to a MySQL tenant of OceanBase Database in the following scenarios:

  • The MySQL tenant of OceanBase Database does not support creating primary keys, unique keys, indexes, keys, full-text indexes, or foreign keys on specific types of fields.

  • The MySQL tenant of OceanBase Database does not support specifying certain functions or expressions in CHECK constraints.

  • The MySQL tenant of OceanBase Database does not support specifying certain functions or expressions in FOREIGN KEY constraints.

  • The MySQL tenant of OceanBase Database does not support certain collations.

• Create partitions

  When you create partitions, you can:

  • Create RANGE, LIST, HASH, or KEY partitions.

  • Create HASH or KEY subpartitions.

  • For RANGE, LIST, and HASH partitions, you can specify a function partitioning key (an expression or a function) or column partitioning key (a field). For KEY partitions, you can specify only a column partitioning key.

  • Specify PARTITIONS number and SUBPARTITIONS number.

    You cannot create LINEAR HASH or LINEAR KEY partitions.

    Notice

    If the DDL statement for creating a partition contains unsupported definitions, the output partition definition is empty, but the schemas are retained (with the partition definition discarded).

  The following attributes will not be parsed or converted but only ignored when they are specified in incremental DDL statements:

  • ENGINE

  • COMMENT

  • DATA DIRECTORY

  • INDEX DIRECTORY

  • MAX_ROWS

  • MIN_ROWS

  • TABLESPACE

  • ALGORITHM (for KEY partitions)

    PARTITION BY KEY ALGORITHM={1 | 2} (column_list) -> PARTITION BY KEY (column_list)

  Due to the limits of MySQL tenants of OceanBase Database, an error may be returned when the converted incremental DDL statements are synchronized to a MySQL tenant of OceanBase Database in the following scenarios:

  • The MySQL tenant of OceanBase Database does not support using fields of certain types as the partitioning key.

  • The MySQL tenant of OceanBase Database does not support using certain functions or expressions as the partitioning key.

ALTER TABLE

When you modify a table, you can operate its columns, constraints, indexes, and partitions, as well as modify its attributes.

• Operate columns

  When you operate columns, you can add, drop, and modify columns. To be specific, you can:

  • Use ADD COLUMN to add one or more columns, with the FIRST | AFTER keyword being specified.

  • Use ALTER COLUMN SET DEFAULT to modify the default value of a column.

  • Use ALTER COLUMN DROP DEFAULT to drop the default value of a column.

  • Use CHANGE COLUMN or MODIFY COLUMN to change or modify a column.

  • Use DROP COLUMN to drop a column.

  If incremental DDL statements contain the following unsupported definitions, the whole table creation statement will fail and an empty string will be output.

  • ALTER COLUMN SET VISIBLE | INVISIBLE

  • ORDER BY col_name

  • RENAME COLUMN

    ALTER TABLE t RENAME COLUMN d TO g;
    

  If incremental DDL statements contain the MODIFY COLUMN or CHANGE COLUMN clause, the FIRST | AFTER keyword will be ignored.

  Due to the limits of MySQL tenants of OceanBase Database, an error may be returned when the converted incremental DDL statements are synchronized to a MySQL tenant of OceanBase Database in the following scenarios:

  • Using the MODIFY COLUMN or CHANGE COLUMN clause to modify the field type may fail. This is because certain field types are not supported in MySQL tenants of OceanBase Database.

  • Using the MODIFY COLUMN or CHANGE COLUMN statement to modify the field length may fail.

  • Using the ALTER COLUMN SET DEFAULT statement to set a default value may fail. This is because certain functions or expressions are not supported in MySQL tenants of OceanBase Database.

  • You cannot drop columns that are used as primary keys, unique keys, or normal indexes, or columns with FOREIGN KEY constraints.

• Operate constraints and indexes

  When you operate constraints or indexes, you can add and drop constraints or indexes. To be specific, you can:

  • Use ADD INDEX | KEY to create a normal index.

  • Use DROP INDEX to drop an index.

  • Use ADD FULLTEXT INDEX | KEY to create a full-text index.

  • Use ADD UNIQUE INDEX | KEY to create a unique index.

  • Use ADD FOREIGN KEY to create a foreign key.

    When you operate constraints or indexes, you cannot:

    Notice

    If incremental DDL statements contain the following unsupported definitions, the whole table creation statement will fail and an empty string will be output.

  • Create unique keys, indexes, keys, and full-text indexes on functions. You can create indexes only on fields.

  • Use ADD SPATIAL INDEX.

  • Use ADD PRIMARY KEY.

  • Use DROP PRIMARY KEY.

  • Use ALTER TABLE DISABLE | ENABLE KEYS.

  • Specify the ON [DELETE | UPDATE] SET NULL attribute for foreign keys.``

  • In MySQL tenants of OceanBase Database of a version earlier than V3.2.3, you cannot:

    • Use ADD CHECK.

    • Use DROP CHECK.

    • Use ALTER CHECK [NOT] ENFORCED.

    • Use ALTER INDEX VISIBLE | INVISIBLE.

    • Use RENAME INDEX | KEY.

      alter table t rename key k to kk;
      ALTER TABLE T RENAME INDEX b TO w;
      

  • Use ALTER TABLE DISABLE | ENABLE KEYS.

  • Specify the ON [DELETE | UPDATE] SET NULL attribute for foreign keys.``

    The following attributes will not be parsed or converted but only ignored when they are specified in incremental DDL statements:

  • USING BTREE or USING HASH

  • KEY_BLOCK_SIZE, index_type, WITH PARSER, COMMENT, VISIBLE | INVISIBLE, ENGINE_ATTRIBUTE, or SECONDARY_ENGINE_ATTRIBUTE in index_option

  • [NOT] ENFORCED for CHECK constraints

  • MATCH FULL | MATCH PARTIAL | MATCH SIMPLE for foreign keys`

    Due to the limits of MySQL tenants of OceanBase Database, an error may be returned when the converted incremental DDL statements are synchronized to a MySQL tenant of OceanBase Database in the following scenarios:

  • The MySQL tenant of OceanBase Database does not support creating primary keys, unique keys, indexes, keys, full-text indexes, or foreign keys on specific types of fields.

  • The MySQL tenant of OceanBase Database does not support specifying certain functions or expressions in CHECK constraints.

  • The MySQL tenant of OceanBase Database does not support specifying certain functions or expressions in FOREIGN KEY constraints.

• Operate partitions

  When you operate partitions, you can:

  • Use ADD PARTITION to add a RANGE partition.

  • Use ADD PARTITION to add a LIST partition.

  • Use DROP PARTITION to drop a partition.

    When you operate partitions, you cannot:

    Notice

    If incremental DDL statements contain the following unsupported definitions, the whole table creation statement will fail and an empty string will be output.

  • Use ADD PARTITION to add a HASH partition.

  • Use TRUNCATE PARTITION.

  • Use DISCARD PARTITION.

  • Use IMPORT PARTITION.

  • Use COALESCE PARTITION.

  • Use REORGANIZE PARTITION.

  • Use EXCHANGE PARTITION.

  • Use ANALYZE PARTITION.

  • Use CHECK PARTITION.

  • Use OPTIMIZE PARTITION.

  • Use REBUILD PARTITION.

  • Use REPAIR PARTITION.

  • Use REMOVE PARTITIONING.

• Modify table attributes

  When you modify table attributes, you can:

  • Rename tables.

    alter table tablename rename to new_tablename
    

  • Modify table comments.

    ALTER TABLE t comment = 'table comment'
    

  When you modify table attributes, you cannot:

  • Modify the ALGORITHM attribute.

    ALTER TABLE t ALGORITHM = COPY
    

  • Convert to a specified character set.

    ALTER TABLE t CONVERT TO CHARACTER SET utf16;
    

  • Modify the character set or collation.

    ALTER TABLE T DEFAULT CHARACTER SET utf8
    

  • Specify the DISCARD | IMPORT TABLESPACE attribute.

  • Use ALTER TABLE FORCE.

  • Modify the LOCK attribute: LOCK = DEFAULT | NONE | SHARED | EXCLUSIVE

  • Use ALTER TABLE WITHOUT | WITH VALIDATION.

  • Modify attributes except COMMENT in table_option.

TRUNCATE TABLE

  TRUNCATE [TABLE] tbl_name

RENAME TABLE

When you migrate data from a MySQL database to a MySQL tenant of OceanBase Database, you can execute the RENAME TABLE statement to rename one or more tables.

DROP TABLE

When you migrate data from a MySQL database to a MySQL tenant of OceanBase Database, you can execute the DROP TABLE statement to drop one or more tables.

The following attributes will not be parsed or converted but only ignored when they are specified in incremental DDL statements:

• TEMPORARY in MySQL tenants of OceanBase Database of a version earlier than V3.2.3

• IF EXISTS

• RESTRICT | CASCADE

CREATE INDEX

When you create indexes, you can:

• Create normal indexes.

• Create unique indexes.

• Create prefix indexes.

  create index i on t(c1(2))
  

• Specify only COMMENT in index_option.

When you create indexes, you cannot:

Notice

If incremental DDL statements contain the following unsupported definitions, the whole table creation statement will fail and an empty string will be output.

• Create spatial indexes.

• Create function indexes.

The following attributes will not be parsed or converted but only ignored when they are specified in incremental DDL statements:

• ASC | DESC

• KEY_BLOCK_SIZE

• WITH PARSER

• VISIBLE | INVISIBLE

• ENGINE_ATTRIBUTE

• SECONDARY_ENGINE_ATTRIBUTE

• ALGORITHM = DEFAULT | INPLACE | COPY

• LOCK = DEFAULT | NONE | SHARED | EXCLUSIVE

Due to the limits of MySQL tenants of OceanBase Database, you cannot create indexes on specific types of fields. Otherwise, an error may be returned when the converted incremental DDL statements are synchronized to a MySQL tenant of OceanBase Database.

DROP INDEX

When you migrate data from a MySQL database to a MySQL tenant of OceanBase Database, you can drop indexes.

The following attributes will not be parsed or converted but only ignored when they are specified in incremental DDL statements:

• ALGORITHM = DEFAULT | INPLACE | COPY

• LOCK = DEFAULT | NONE | SHARED | EXCLUSIVE

Limits of DDL operations in incremental migration

• If a table to be synchronized involves DDL operations that are not supported, the migration project may fail and unrecoverable data exceptions may be caused.

• Frequent DDL operations on a table are not supported. After the store finishes a DDL operation, which can be determined based on the timestamp, it proceeds to the next DDL operation. Otherwise, the store may exit unexpectedly or cause unrecoverable data exceptions.

• Make sure that no DDL operations are performed before you create a store and when a store is being started. If log pulling is involved, make sure that no DDL operations are performed during the period from the start time when logs are pulled to the current time. Otherwise, the store may exit unexpectedly or cause unrecoverable data exceptions.

• The table names before and after the RENAME TABLE statement must be both included or both not included in the list of tables to be synchronized.

• If you enable DDL operations in incremental migration, the DROP INDEX statement is executed on all indexes, which may cause index loss in the destination database.

• In a DDL operation for incremental migration, if the primary key of a table is data of the FLOAT or DOUBLE type, data inconsistency may occur.

• If you use gh-ost to synchronize DDL operations in incremental migration from a MySQL database to a MySQL tenant of OceanBase Database:

  • When you select the Specify Objects mode, do not select the table named *_ghc.

  • When you select the Match Rules mode, set Object Exclusion Rules to {database_name}.*_ghc.

Supported DDL operations in incremental migration from a MySQL tenant of OceanBase Database to a MySQL database and limits

OMS Community Edition supports MySQL 5.5, 5.6, 5.7, and 8.0.

• Supported DDL operations in incremental migration

  • CREATE TABLE

    Note

    CREATE TABLE AS SELECT is not supported.

  • DROP TABLE

  • TRUNCATE TABLE

  • ALTER TABLE CHANGE COLUMN

    Notice

    You can only extend the column length. You cannot modify the column type.

  • ALTER TABLE ADD COLUMN

  • ALTER TABLE ALTER COLUMN SET DEFAULT and ALTER TABLE ALTER COLUMN DROP DEFAULT

  • ALTER TABLE DROP COLUMN

  • CREATE INDEX and ALTER TABLE ADD INDEX

  • DROP INDEX and ALTER TABLE DROP INDEX

• Limits of DDL operations in incremental migration

  • If a table to be synchronized involves DDL operations that are not supported, the migration link may be interrupted and unrecoverable data exceptions may be caused.

  • Frequent DDL operations on a table are not supported. After the store finishes a DDL operation, which can be determined based on the timestamp, it proceeds to the next DDL operation. Otherwise, the store may exit unexpectedly or cause unrecoverable data exceptions.

  • Make sure that no DDL operations are performed before you create a store and when a store is being started. If log pulling is involved, make sure that no DDL operations are performed during the period from the start time when logs are pulled to the current time. Otherwise, the store may exit unexpectedly or cause unrecoverable data exceptions.

  • The table names before and after the RENAME TABLE statement must be both included or both not included in the list of tables to be synchronized.

  • If you enable DDL operations in incremental migration, the DROP INDEX statement is executed on all indexes, which may cause index loss in the destination database.

Supported DDL operations and limits for migrate data between OceanBase database Community Edition

You can use DDL operations to migrate incremental data between OceanBase database Community Edition.

Notice

If you have created an index in the source tenant, it also exists in the destination tenant. OMS Community Edition automatically ignores the index without interrupting the migration process.

• Supported DDL operations in incremental migration

  • ALTER TABLE ADD COLUMN

  • ALTER TABLE MODIFY COLUMN

    Notice

    You can only extend the column length. You cannot modify the column type.

  • ALTER TABLE ALTER COLUMN SET DEFAULT and ALTER TABLE ALTER COLUMN DROP DEFAULT

  • ALTER TABLE DROP COLUMN

  • CREATE TABLE

  • DROP TABLE

  • TRUNCATE TABLE

  • CREATE INDEX and ALTER TABLE ADD INDEX

  • DROP INDEX and ALTER TABLE DROP INDEX

  • RENAME TABLE and ALTER TABLE RENAME

• Limits of DDL operations in incremental migration

  • If a table to be synchronized involves DDL operations that are not supported, the migration link may be interrupted and unrecoverable data exceptions may be caused.

  • Frequent DDL operations on a table are not supported. After the store finishes a DDL operation, which can be determined based on the timestamp, it proceeds to the next DDL operation. Otherwise, the store may exit unexpectedly or cause unrecoverable data exceptions.

  • Make sure that no DDL operations are performed before you create a store and when a store is being started. If log pulling is involved, make sure that no DDL operations are performed during the period from the start time when logs are pulled to the current time. Otherwise, the store may exit unexpectedly or cause unrecoverable data exceptions.

  • The table names before and after the RENAME TABLE statement must be both included or both not included in the list of tables to be synchronized.

  • If you enable DDL operations in incremental migration, the DROP INDEX command is executed on all indexes, which may cause index loss in the destination database.

FAQ

Q: What can I do if a data migration project fails because I have performed an unsupported DDL operation in the source database?

A: Configure the DDL operation in SkipDDL and restart the data migration project. Procedure:

1. Go to the details page of the target project.

   1. Log on to the OMS Community Edition console.

   2. In the left-side navigation pane, click Data Migration.

   3. In the Migration Projects list, click the name of the target project to go to its details page.

2. Click View Component Monitoring in the upper-right corner.

3. On the View Component Monitoring page, click ... for the JDBCWriter component and choose View Logs from the short-cut menu.

4. Find the error DDL statement in the error.log file.

5. In the ddl_msg.log file, copy the content in receive ddl:.

6. Update component parameters.

   1. Return to the View Component Monitoring page and click Update for the JDBCWriter component.

   2. On the Update Configurations page, move the pointer over the JDBCWriter.coordinatorFile.skipDdl parameter. Click the Edit icon, change the value to the copied content, and click the Confirm icon. Multiple DDL statements must be separated with ampersands (&). You can also change the delimiter.

   3. Move the pointer over the extraConfig parameter. Click the Add icon, add the JDBCWriter.coordinatorFile.skipDDLSeparator = "$" parameter, and click the Confirm icon. The default value of the new parameter is &.

   4. Click Update.

7. Return to the details page of the data migration project, and click Restore in the upper-right corner to run the project again.