Migrating Data from MySQL to openGauss/MogDB Using MTK

Supported Versions

• 5.5
• 5.6
• 5.7
• 8.0

Precautions

Empty string problem

• openGauss A mode is different from MySQL, and data needs to be processed. When the attribute of the column is ``NOT NULL, it is converted to" "`, and when it is allowed to be NULL, it is converted to NULL
• openGauss PG mode is the same as MySQL, NULL != '' No need to deal with
• openGauss B mode is the same as MySQL, NULL != '' No need to deal with

Database compatible types. Value range: A, B, C, and PG

• A indicates that MogDB is compatible with Oracle.
• B indicates that MogDB is compatible with MySQL.
• C indicates that MogDB is compatible with Teradata.
• PG indicates that MogDB is compatible with PostgreSQL.

However, C is not supported currently. Therefore, the values A, B, and PG are commonly used.

Note:

• For the compatibility type of A, the database considers an empty string as NULL and replace the data type DATE with TIMESTAMP(0) WITHOUT TIME ZONE.
• For the compatibility type of B, when a string is converted to an integer type, if an illegal value is entered, it will be converted to 0. However, for other compatibility types, an error will be reported.
• For the compatibility type of PG, CHAR and VARCHAR use character as the string unit. For other compatibility types, byte is used as the string unit. For example, as for UTF-8 character set, CHAR(3) can hold 3 Chinese characters in the compatibility type of PG but only one Chinese character in the other compatibility types.

COLLATE

MySQL's COLLATE is divided into the following levels

• Instance level
• database level
• table level
• column level
• Connection level

MogDB's COLLATE in B mode dolphin is the following level

• Instance level
• database level
• Schema
• table level
• column level

Multiple character set scenarios are not supported in the one database/table. Migration to MogDB can only be one character set. For example, if UTF8 and UTF8MB4 exist in MySQL database, they can only be UTF8 in MogDB. The synchronized table structure will synchronize the COLLATE attribute, but it is limited by database support and does not support it until it becomes the default COLLATE. See the specific support list in pg_catalog.pg_collation Reverse table structure synchronization will lose the COLLATE attribute. This will lead to problems such as column length exceeding 65535. It is recommended to use the source table creation statement to initialize the structure.

database

MTK migrates the MySQL database to MogDB/openGauss schema. Be sure to pay attention to the character set and COLLATE.

MySQL view COLLATE

select schema_name,default_character_set_name,DEFAULT_COLLATION_NAME from information_schema.schemata;

MTK does not migrate the COLLATE attribute for the time being. Manual intervention is required to confirm and create the schema. Whether migration will be considered in later versions is to be determined.

Sequence/Auto_increment

MySQL does not have sequences. Migrate to target-side sequences when column attribute auto_increment is not supported.

• Auto_increment is not supported

  • Compatibility mode is not B mode.
  • Compatibility mode B mode does not install plugin dolphin
  • Compatibility mode B mode and plugin dolphin is installed, database version is less than 3.1.0

  - Create sequence
  create sequence sequence_name;
  - Specify column default value as sequence next
  create table table(col col_type default nextval('sequence_name'));
  - Modify sequence owner
  alter sequence sequence_name owned owner.table_name.column_name
  - Modify sequence last value
  select setval('',last_number);

• Auto_increment is supported

  Compatibility mode B mode and plugin dolphin is installed, database version is greater than 3.1.0

  - Do not create sequence
  - Specify column attribute auto_increment
  create table table(col col_type auto_increment);
  --Modify the last value of the sequence
  alter table owner.table_name auto_increment=last_number

Table

• rewrite the auto-increment column as the default sequence
• CHARSET property remove
• ENGINE property remove

Partition Table

• LINEAR Hash partition convert Hash partition

• LINEAR Key/Key partition convert Hash partition

• range partition

  • days function not support

  • to_days

    MySQL

    CREATE TABLE mtk.range_part_t1 (
        prof_history_id BIGINT(20),
        person_id BIGINT(20) NOT NULL,
        organization_id BIGINT(20) NOT NULL,
        record_date DATETIME NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=UTF8 PARTITION BY RANGE (to_days(record_date))
    (
        PARTITION yr0 VALUES LESS THAN(736695),
        PARTITION yr7 VALUES LESS THAN(737060),
        PARTITION yr8 VALUES LESS THAN(737425),
        PARTITION yr9 VALUES LESS THAN(MAXVALUE)
    )

    openGauss

    CREATE TABLE mtk.range_part_t2 (
        prof_history_id BIGINT,
        person_id BIGINT NOT NULL,
        organization_id BIGINT NOT NULL,
        record_date TIMESTAMP NOT NULL
    ) PARTITION BY RANGE (record_date)
    (
        PARTITION yr0 VALUES LESS THAN('2017-01-01'),
        PARTITION yr7 VALUES LESS THAN('2018-01-01'),
        PARTITION yr8 VALUES LESS THAN('2019-01-01'),
        PARTITION yr9 VALUES LESS THAN(MAXVALUE)
    )

  • year

    MySQL

    CREATE TABLE mtk.range_part_t2 (
        prof_history_id BIGINT(20),
        person_id BIGINT(20) NOT NULL,
        organization_id BIGINT(20) NOT NULL,
        record_date DATETIME NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=UTF8 PARTITION BY RANGE (year(record_date))
    (
        PARTITION yr0 VALUES LESS THAN(2018),
        PARTITION yr7 VALUES LESS THAN(2019),
        PARTITION yr8 VALUES LESS THAN(2020),
        PARTITION yr9 VALUES LESS THAN(MAXVALUE)
    )

    openGauss

    CREATE TABLE mtk.range_part_t1 (
        prof_history_id BIGINT,
        person_id BIGINT NOT NULL,
        organization_id BIGINT NOT NULL,
        record_date TIMESTAMP NOT NULL
    ) PARTITION BY RANGE (record_date)
    (
        PARTITION yr0 VALUES LESS THAN('2018-01-01'),
        PARTITION yr7 VALUES LESS THAN('2019-01-01'),
        PARTITION yr8 VALUES LESS THAN('2020-01-01'),
        PARTITION yr9 VALUES LESS THAN(MAXVALUE)
    )

  • unix_timestamp

    MySQL

    CREATE TABLE mtk1.range_part_t3 (
        report_id INT(11) NOT NULL,
        report_status VARCHAR(20) NOT NULL,
        report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=UTF8 PARTITION BY RANGE (unix_timestamp(report_updated))
    (
        PARTITION p0 VALUES LESS THAN(1293811200),
        PARTITION p1 VALUES LESS THAN(1296489600),
        PARTITION p2 VALUES LESS THAN(1298908800),
        PARTITION p3 VALUES LESS THAN(1301587200),
        PARTITION p4 VALUES LESS THAN(1304179200),
        PARTITION p5 VALUES LESS THAN(1306857600),
        PARTITION p6 VALUES LESS THAN(1309449600),
        PARTITION p7 VALUES LESS THAN(1312128000),
        PARTITION p8 VALUES LESS THAN(1314806400),
        PARTITION p9 VALUES LESS THAN(MAXVALUE)
    )

    openGauss

    CREATE TABLE mtk1.range_part_t3 (
        report_id INTEGER NOT NULL,
        report_status VARCHAR(60) NOT NULL,
        report_updated TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) PARTITION BY RANGE (report_updated)
    (
        -- 1293811200 ---> '2011-01-01 00:00:00'
        PARTITION p0 VALUES LESS THAN('2011-01-01 00:00:00'),
        -- 1296489600 ---> '2011-02-01 00:00:00'
        PARTITION p1 VALUES LESS THAN('2011-02-01 00:00:00'),
        -- 1298908800 ---> '2011-03-01 00:00:00'
        PARTITION p2 VALUES LESS THAN('2011-03-01 00:00:00'),
        -- 1301587200 ---> '2011-04-01 00:00:00'
        PARTITION p3 VALUES LESS THAN('2011-04-01 00:00:00'),
        -- 1304179200 ---> '2011-05-01 00:00:00'
        PARTITION p4 VALUES LESS THAN('2011-05-01 00:00:00'),
        -- 1306857600 ---> '2011-06-01 00:00:00'
        PARTITION p5 VALUES LESS THAN('2011-06-01 00:00:00'),
        -- 1309449600 ---> '2011-07-01 00:00:00'
        PARTITION p6 VALUES LESS THAN('2011-07-01 00:00:00'),
        -- 1312128000 ---> '2011-08-01 00:00:00'
        PARTITION p7 VALUES LESS THAN('2011-08-01 00:00:00'),
        -- 1314806400 ---> '2011-09-01 00:00:00'
        PARTITION p8 VALUES LESS THAN('2011-09-01 00:00:00'),
        PARTITION p9 VALUES LESS THAN(MAXVALUE)
    )

Column

Column Type Map Between MySQL and openGauss/MogDB

• For MySql bigint unsigned auto increment columns migrated to openGauss, pay attention to the size of the sequence.
• The field length will be expanded according to the compatibility mode migrated to openGauss
• Timestamp column Default value 0000-00-00 00:00:00 rewrite 1970-01-01

Spatial Data Type

MTK 2.5.3 Support. Migrate to geomotry if Postgis is installed, varchar otherwise

The following types are supported + Geometry + GeometryCollection + LineString + MultiPoint + MultiPolygon + Point + Polygon + MultilineString

Set

MySQL Set type can insert multiple values, temporarily migrated to openGauss varchar type

Table Data

• The time column data is 0000-00-00 13:14:13 This kind of data is not supported in openGauss and can be ignored by the parameter mySQLSkipErrorDateTimeData or igErrorData

Constraint

• The name is automatically changed in openGauss.

• not supported Foreign keys point to non-unique indexes of the parent table

  CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `uuid` binary(16) NOT NULL COMMENT 'UUID',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `uuid` (`uuid`) USING BTREE
  );
  CREATE TABLE `t2` (
  `c1_uuid` binary(16) NOT NUL,
  `c2_uuid` binary(16) NOT NULL,
  PRIMARY KEY (`c1_uuid`,`c2_uuid`),
  CONSTRAINT `FK_T1` FOREIGN KEY (`c2_uuid`) REFERENCES `t1` (`uuid`) ON DELETE RESTRICT ON UPDATE RESTRICT
  );
  --  FK_T1 constraints are not migrated. The dependency it points to is a normal index

Index

• The name is automatically changed in openGauss.
• Index partitions are not supported currently.

Syntax

• year(col3)
• to_days(t)