All Products
Search

This Product

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

    Document Center

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

    Last Updated:Aug 20, 2026

    This topic describes how to convert a DRDS mode database to an AUTO mode database.

    Background information

    PolarDB-X allows you to convert a DRDS mode database to an AUTO mode database by using CREATE DATABASE LIKE or CREATE DATABASE AS statements. For more information about AUTO mode databases and DRDS mode databases, see AUTO mode databases and DRDS mode databases. For clarity, this topic refers to the original DRDS mode database as the source DRDS database and the new AUTO mode database as the destination AUTO database.

    • The create database like statement converts only the table schema in the source DRDS database and does not copy data.

    • The create database as statement converts the table schema and copies the data from the corresponding tables.

    Prerequisites

    The instance version must be 5.4.16-16717637 or later. For more information about how to check the instance version, see View the instance version.

    Precautions

    • The conversion process places a read lock on the source DRDS database, making it read-only and blocking all DML and DDL statements until the conversion is complete. Therefore, carefully assess the potential impact on your business.

    • To prevent conversion failures, do not execute any DML or DDL statements on the source DRDS database or the destination AUTO database during the conversion.

    • The conversion process does not delete or overwrite the source DRDS database. Instead, it creates a new destination AUTO database from the source DRDS database. You can still access the source DRDS database after the conversion.

    • To prevent failures due to insufficient storage space, ensure that enough space is available before you begin.

    Syntax

    CREATE DATABASE [IF NOT EXISTS] auto_database_name
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

    Parameter

    Description

    auto_database_name

    The name of the new destination AUTO database.

    drds_database_name

    The name of the source DRDS database. This database must already exist and be a DRDS mode database.

    mode

    The mode of the new database. This parameter must be set to auto.

    dry_run

    The dry_run parameter previews how PolarDB-X converts the table schema from a source DRDS database to an AUTO mode database. You can also see Table schema conversion rules to understand how PolarDB-X performs the conversion.

    Valid values:

    • true: Enables the dry_run parameter.

    • false: Disables the dry_run parameter. This is the default value.

    Note

    • The dry_run parameter only previews the table schema conversion from DRDS mode to AUTO mode. It does not create any table schemas or copy any data.

    • Enabling the dry_run parameter does not affect the source DRDS database. For example, it will not become read-only.

    lock

    The lock parameter controls whether to place a read lock on the source DRDS database during the conversion. A read lock makes the source DRDS database read-only and blocks all DML and DDL operations.

    Valid values:

    • true: Places a read lock on the source DRDS database. This is the default value.

    • false: Disables the lock.

      Important

      If you disable this parameter, data may become inconsistent between the source DRDS database and the destination AUTO database.

    create_tables

    The create_tables parameter controls whether to create the corresponding table schemas in the destination AUTO database during the conversion.

    Valid values:

    • true: When create_tables is enabled, PolarDB-X automatically converts the table schema and creates the data tables in the destination AUTO database. This is the default value.

    • false: When create_tables is disabled, you must create the destination AUTO database in advance and create all tables that correspond to the tables in the source DRDS database.

      Note

      • If the tables automatically converted by PolarDB-X do not suit your application scenario, you can disable the create_tables parameter and manually create the destination AUTO database and tables. The tables in the destination AUTO database must have the exact same table names and column definitions as the tables in the source DRDS database. You can customize the partition mode.

      • When the create_tables option is disabled, the create database as statement only copies data from the source DRDS database to the destination AUTO database. It does not create the destination AUTO database or its tables.

    include

    The include parameter specifies the tables to convert from the source DRDS database. If you specify include, only the tables that you specify in the include parameter are converted.

    exclude

    The exclude parameter specifies the tables to exclude from the conversion. If you specify exclude, the tables that you specify in the exclude parameter are not converted.

    Examples

    • Convert the db_drds DRDS mode database to the db_auto AUTO mode database. This operation converts only the table schema and does not copy data.

      CREATE DATABASE db_auto like db_drds mode=auto;

      The following output is returned:

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

    • Convert the db_drds DRDS mode database to the db_auto AUTO mode database. This operation converts the table schema and copies the data.

      CREATE DATABASE db_auto as db_drds mode=auto;

      The following output is returned:

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

    • Convert and copy only the tb1 table from the db_drds DRDS mode database to the db_auto_exist AUTO mode database.

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

      The following output is returned:

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

    • Preview the table schema of the tb1 and tb2 tables in the db_drds source DRDS database and the resulting AUTO mode schema, without performing an actual conversion.

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

      The following output is returned:

      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 only the data from the source DRDS database to the destination AUTO database without converting the table schema. You must create the destination AUTO database and its tables in advance. The table schemas in the destination database must be identical to those in the source database.

      CREATE DATABASE db_auto as db_drds create_tables=false;

      The following output is returned:

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

    View the conversion progress and results

    PolarDB-X provides the INFORMATION_SCHEMA.CREATE_DATABASE view, which you can use to check the progress and results of the conversion task. For example, you can run the following SQL statement to check the progress of a task where the destination AUTO database is db_auto.

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

    The following output is returned:

    *************************** 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)

    You can also use the show full ddl command to check the progress.

    For some time-consuming conversion tasks, if the connection is lost and you cannot see the result (the conversion task continues to run normally after the connection is lost), you can use show ddl result to view the conversion result. You can also view more detailed results from INFORMATION_SCHEMA.CREATE_DATABASE.

    Conversion time

    The conversion time primarily depends on the data copy process between the source DRDS database and the destination AUTO database. In a test environment with a PolarDB-X dedicated instance (8-core CPU, 64 GB memory, 4 compute nodes, and 4 storage nodes), a DRDS database was created based on a Sysbench test. The database contained 4 tables, each with 50 million rows, totaling approximately 44 GB of data. The conversion took 10 minutes and 37 seconds.

    Note

    The actual conversion time depends on factors such as your instance specifications and database size. To get a more accurate estimate, we recommend performing a drill during off-peak hours or in a test environment. You can set the lock parameter to false for the drill.

    Table schema conversion rules

    By understanding the rules for how PolarDB-X automatically converts DRDS mode databases to AUTO mode databases, you can evaluate whether the databases and tables converted by PolarDB-X meet your business requirements and make adjustments accordingly.

    Single tables and broadcast tables

    Single tables and broadcast tables in DRDS mode are converted to single tables and broadcast tables in AUTO mode, respectively.

    Tables with database and table sharding

    • Function mapping table.

      DRDS function type

      DRDS sharding function

      AUTO partitioning function

      Hash

      hash(a)

      key(a)

      str_hash(a, startIdx, endIdx)

      key(a)

      uni_hash(a)

      key(a)

      right_shift(a)

      key(a)

      range_hash(a, b, 10)

      co_hash(right(a,n), right(b,n))

      Date

      YYYYMM(a)

      hash(to_months(a))

      YYYYWEEK(a)

      hash(to_weeks(a))

      YYYYDD(a)

      hash(to_days(a))

      MM(a)

      range(month(a))

      DD(a)

      range(dayofmonth(a))

      WEEK(a)

      range(dayofweek(a))

      MMDD(a)

      range(dayofyear(a))

    • Database sharding without table sharding.

      # Table in DRDS mode
create table tb1 (
	id int,
  name varchar(20)
) dbpartition by uni_hash(id);

# Mapped table in AUTO mode
create table tb1 (
  id int,
  name varchar(20)
) partition by key(id);
      Note

      As shown in the function mapping table:

      • The database sharding function is converted to a partitioning function.

      • The table schema is converted to a level-1 partitioned table in AUTO mode.

    • Table sharding without database sharding.

      # Table in DRDS mode
create table tb3 (
  id int,
  dt date
) tbpartition by week(dt) tbpartitions 4;

# Mapped table in AUTO mode
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
);
      Note

      As shown in the function mapping table:

      • The table sharding function is converted to its corresponding partitioning function.

      • The table schema is converted to a level-1 partitioned table in AUTO mode.

    • Database and table sharding with identical rules.

      # Table in 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; 

# Mapped table in AUTO mode
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;
      Note

      As shown in the function mapping table:

      • The database sharding function is converted to its corresponding partitioning function.

      • The number of partitions equals the product of the number of database shards and the number of table shards.

      • The table schema is converted to a level-1 partitioned table in AUTO mode.

    • Database and table sharding with different rules.

      # Table in 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; 

# Mapped table in AUTO mode
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;
      Note

      As shown in the function mapping table:

      • The database sharding function is converted to the corresponding level-1 partitioning function, and the number of level-1 partitions equals the number of database shards.

      • The table sharding function is converted to the corresponding level-2 partitioning function, and the number of level-2 partitions equals the number of table shards.

      • The table schema is converted to a level-2 partitioned table in AUTO mode.

    Sequence conversion rules

    In DRDS mode, group sequence, time-based sequence, and simple sequence are all converted to the New Sequence type in AUTO mode, which offers better overall performance. For more information, see Sequence.

    Precautions

    • The destination AUTO database must have the same CHARSET and COLLATE attributes as the source DRDS database. Therefore, you cannot manually specify CHARSET or COLLATE in the create database like/as statement.

    • The destination AUTO database and its tables do not inherit the locality attribute from the source DRDS database and its tables. For more information about locality, see Specify storage locations by using LOCALITY.