Schema migration mechanisms

When you migrate data between two databases of OceanBase Community Edition, OceanBase Migration Service (OMS) Community Edition provides the corresponding mechanisms to support the schema migration based on the actual conditions of the migration project.

Background

When you migrate data from one database of OceanBase Community Edition to another and advance to the forward switchover step, in order to migrate tables without unique keys, OMS will add hidden columns to these tables in the destination database, and create unique indexes based on the hidden columns. These columns and indexes will be automatically dropped during the forward switchover step.

If you do not advance to the forward switchover step in a data migration project, you must manually drop the hidden columns and unique indexes based on the guidance in this topic.

Prerequisites

• {partition_col_0} or {partition_col_0} indicates a table partitioning key column. Multiple such fields may exist. Separate the fields with commas (,), for example: "{partition_col_0}","{partition_col_1}","{partition_col_2}" or `{partition_col_0}`,`{partition_col_1}`,`{partition_col_2}`.

• {schema}, {table}, {partition_col_0}, and {partition_col_1} are respectively placeholders for the database name, table name, and partitioning key column names.

Procedure

When you migrate data from a database of OceanBase Community Edition to another, OMS Community Edition will add columns and create unique indexes for tables without unique keys.

You can run the following command to check whether a table has unique keys. Replace {schema} and {table} with the actual database and table names respectively. If no result is returned, the table does not have unique keys.

SELECT 1 FROM information_schema.statistics WHERE TABLE_SCHEMA = {schema} AND TABLE_NAME = {table} GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME HAVING count(1) = count(IF(upper(nullable) != 'YES' and NON_UNIQUE = 0, 1, NULL));

• Add columns.

  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_PK_INCRMT` bigint;
  

• Add unique indexes.

  // Partitioning key columns are available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("{partition_col_0}", "{partition_col_1}", "OMS_PK_INCRMT") local;
  // No partitioning key column is available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("OMS_PK_INCRMT");
  

• Drop a unique index.

  DROP INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}`
  

• Drop a column.

  ALTER TABLE `{schema}`.`{table}` DROP COLUMN `OMS_PK_INCRMT`;