PolarDB:Convert a DRDS mode database to an AUTO mode database

Choose a statement

Prerequisites

Before you begin, ensure that you have:

• A PolarDB-X instance running version 5.4.16-16717637 or later. To check your version, see View the version of an instance

• Enough remaining storage capacity. If storage is insufficient, the conversion fails

Usage notes

• During conversion, PolarDB-X applies a read lock to the source DRDS database. The database becomes read-only — no DML or DDL statements can run against it until conversion completes. Schedule conversions during off-peak hours or in a test environment to avoid business disruption.

• Do not run DML or DDL statements on either the source DRDS database or the destination AUTO database during conversion. Doing so causes the conversion to fail.

• The source DRDS database is preserved after conversion. A new destination AUTO database is created alongside it, so you can verify results and roll back if needed before decommissioning the source.

Syntax

CREATE DATABASE [IF NOT EXISTS] auto_database_name
{ LIKE | AS } drds_database_name
[convert_option_list]

convert_option_list:
    convert_option [convert_option...] [{ include_list | exclude_list }]

convert_option:
      mode=auto
    | dry_run={ true | false }
    | lock={ true | false }
    | create_tables={ true | false }

include_list:
    include=table_name [,table_name...]

exclude_list:
    exclude=table_name [,table_name...]

Parameters

Convert a database

The following examples cover the most common scenarios. All examples use db_drds as the source DRDS database and db_auto as the destination AUTO database.

Convert schemas only (no data)

Use CREATE DATABASE LIKE when you want to validate how table schemas will look in AUTO mode before running a full migration.

CREATE DATABASE db_auto LIKE db_drds mode=auto;

Expected output:

+-------------+
| RESULT      |
+-------------+
| ALL SUCCESS |
+-------------+
1 row in set (10 min 32.17 sec)

Convert schemas and copy data

Use CREATE DATABASE AS to run a full migration.

CREATE DATABASE db_auto AS db_drds mode=auto;

Expected output:

+-------------+
| RESULT      |
+-------------+
| ALL SUCCESS |
+-------------+
1 row in set (10 min 37.30 sec)

Migrate a specific table to an existing destination database

If the destination database db_auto_exist already exists, use IF NOT EXISTS to add table tb1 without recreating the database.

CREATE DATABASE IF NOT EXISTS db_auto_exist AS db_drds include=tb1;

Expected output:

+-------------+
| RESULT      |
+-------------+
| ALL SUCCESS |
+-------------+
1 row in set (8 min 12.05 sec)

Preview schema conversion without making changes

Set dry_run=true to preview how DRDS schemas map to AUTO schemas. No tables are created and no data is copied. The source database is not locked.

CREATE DATABASE db_auto LIKE db_drds dry_run=true include=tb1,tb2;

Expected output:

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE_TABLE_DRDS                                                                                                                                                                                                                                   | CREATE_TABLE_AUTO                                                                                                                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb1   | CREATE TABLE `tb1` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`id`) | CREATE TABLE `tb1` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY (`id`) PARTITIONS 32 |
| tb2   | CREATE TABLE `tb2` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`id`) | CREATE TABLE `tb2` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY (`id`) PARTITIONS 32 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.04 sec)

Copy data to a pre-created destination database

Set create_tables=false when you want full control over partition schemes in the destination. Pre-create db_auto and all tables (same table names and column definitions as the source), then run:

CREATE DATABASE db_auto AS db_drds create_tables=false;

PolarDB-X copies the data but does not create the database or tables.

Expected output:

+-------------+
| RESULT      |
+-------------+
| ALL SUCCESS |
+-------------+
1 row in set (5 min 47.75 sec)

Monitor conversion progress (for large databases)

For large databases where conversion takes a significant amount of time, use the INFORMATION_SCHEMA.CREATE_DATABASE view to track progress while the task runs.

SELECT * FROM INFORMATION_SCHEMA.CREATE_DATABASE WHERE TARGET_SCHEMA = 'db_auto'\G

Example output:

*************************** 1. row ***************************
             DDL_JOB_ID: 1547426040408715264
          SOURCE_SCHEMA: db_drds
          TARGET_SCHEMA: db_auto
              TABLE/SEQ: tb1
                  STAGE: BACKFILL
                 STATUS: RUNNING
                 DETAIL: NULL
                SQL_SRC: CREATE TABLE `tb1` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`id`)
                SQL_DST: CREATE TABLE IF NOT EXISTS `tb1` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY (`id`) PARTITIONS 32
    BACKFILL_START_TIME: 2023-01-01 19:13:01
CURRENT_SPEED(ROWS/SEC): 37632
AVERAGE_SPEED(ROWS/SEC): 216064
          FINISHED_ROWS: 216064
 APPROXIMATE_TOTAL_ROWS: 1
      BACKFILL_PROGRESS: 100%
*************************** 2. row ***************************
             DDL_JOB_ID: 1547426040408715264
          SOURCE_SCHEMA: db_drds
          TARGET_SCHEMA: db_auto
              TABLE/SEQ: tb2
                  STAGE: BACKFILL
                 STATUS: RUNNING
                 DETAIL: NULL
                SQL_SRC: CREATE TABLE `tb2` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`id`)
                SQL_DST: CREATE TABLE IF NOT EXISTS `tb2` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY (`id`) PARTITIONS 32
    BACKFILL_START_TIME: 2023-01-01 19:13:01
CURRENT_SPEED(ROWS/SEC): 36608
AVERAGE_SPEED(ROWS/SEC): 211968
          FINISHED_ROWS: 211968
 APPROXIMATE_TOTAL_ROWS: 1
      BACKFILL_PROGRESS: 100%
2 rows in set (0.01 sec)

To get a quick summary of the DDL job status, run:

SHOW FULL DDL;

If the connection is closed before the task completes, the conversion continues running. Run SHOW DDL RESULT after reconnecting to see the final outcome.

Performance reference: On an instance with 8 cores, 64 GB memory, 4 compute nodes, and 4 storage nodes, converting a database with 4 tables and 50 million rows each (~44 GB total) takes approximately 10 minutes 37 seconds. Actual duration depends on instance type and database size. To get a realistic estimate, run a test conversion during off-peak hours with lock=false.

Schema conversion rules (reference)

Review these rules to verify that the auto-converted schemas meet your requirements before running a full migration.

-- DRDS mode
CREATE TABLE tb1 (
  id INT,
  name VARCHAR(20)
) dbpartition BY uni_hash(id);

-- AUTO mode (converted)
CREATE TABLE tb1 (
  id INT,
  name VARCHAR(20)
) PARTITION BY KEY(id);

-- DRDS mode
CREATE TABLE tb3 (
  id INT,
  dt DATE
) tbpartition BY week(dt) tbpartitions 4;

-- AUTO mode (converted)
CREATE TABLE tb3 (
  id INT,
  dt DATE
) PARTITION BY RANGE (dayofweek(`dt`)) (
  PARTITION p2 VALUES LESS THAN (2),
  PARTITION p3 VALUES LESS THAN (3),
  PARTITION p4 VALUES LESS THAN (4),
  PARTITION p5 VALUES LESS THAN (5),
  PARTITION p6 VALUES LESS THAN (6),
  PARTITION pd VALUES LESS THAN MAXVALUE
);

-- DRDS mode
CREATE TABLE tb2 (
  buyer_id VARCHAR(20),
  order_id VARCHAR(20)
) dbpartition BY range_hash(buyer_id, order_id, 10)
  tbpartition BY range_hash(buyer_id, order_id, 10) tbpartitions 4;

-- AUTO mode (converted)
CREATE TABLE tb2 (
  buyer_id VARCHAR(20),
  order_id VARCHAR(20)
) PARTITION BY co_hash(right(buyer_id, 10), right(order_id, 10)) PARTITIONS 64;

-- DRDS mode
CREATE TABLE tb5 (
  buyer_id VARCHAR(20),
  order_id VARCHAR(20)
) dbpartition BY hash(buyer_id)
  tbpartition BY hash(order_id) tbpartitions 4;

-- AUTO mode (converted)
CREATE TABLE tb5 (
  buyer_id VARCHAR(20),
  order_id VARCHAR(20)
) PARTITION BY KEY(buyer_id) PARTITIONS 16
  SUBPARTITION BY KEY(order_id) SUBPARTITIONS 4;

What's next

• Databases in AUTO mode and DRDS mode

• Sequence