TiDB - DM replicating index values instead of the ENUM value from MySQL

[2b1sh]

Is your feature request related to a problem?

Step 1 : Created the table in MySQL

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `gender` enum('male','female') DEFAULT NULL,
  `user_type` enum('AQ','AL') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Step 2 : Inserted values into the same table

mysql> INSERT INTO user (name, age, gender, user_type) VALUES     ('John', 25, 'male', 'AQ'),     ('Alice', 30, 'female', 'AL');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

Step 3 : Migrated this table to TiDB using TiDB DM and checked it

Following is the output in TiDB

mysql> select * from user;
+----+-------+------+--------+-----------+
| id | name  | age  | gender | user_type |
+----+-------+------+--------+-----------+
|  1 | John  |   25 | male   | AQ        |
|  2 | Alice |   30 | female | AL        |
+----+-------+------+--------+-----------+
2 rows in set (0.00 sec)

Step 4 : Then I changed the datatype from enum to varchar(2) in TiDB
Because the ENUM is a experimental feature for TiFLash

mysql> alter table user modify column user_type Varchar(2);
Query OK, 0 rows affected (0.36 sec)
mysql> alter table user modify column gender Varchar(2);
Query OK, 0 rows affected (0.36 sec)

Step 5 : Again inserted data in MySQL

mysql> INSERT INTO user (name, age, gender, user_type) VALUES ('Emma', 28, 'female', 'AL'),     ('Mike', 40, 'male', 'AQ');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Step 6 : Again checked it in TiDB

This is what I get as the output

mysql> select * from user;
+----+-------+------+--------+-----------+
| id | name  | age  | gender | user_type |
+----+-------+------+--------+-----------+
|  1 | John  |   25 | male   | AQ        |
|  2 | Alice |   30 | female | AL        |
|  3 | Emma  |   28 | 2      | 2         |
|  4 | Mike  |   40 | 1      | 1         |
+----+-------+------+--------+-----------+
4 rows in set (0.00 sec)

I expect the same data as in the MySQL

mysql> select * from user;
+----+-------+------+--------+-----------+
| id | name  | age  | gender | user_type |
+----+-------+------+--------+-----------+
|  1 | John  |   25 | male   | AQ        |
|  2 | Alice |   30 | female | AL        |
|  3 | Emma  |   28 | female | AL        |
|  4 | Mike  |   40 | male   | AQ        |
+----+-------+------+--------+-----------+
4 rows in set (0.00 sec)

Instead I get the data i have changed to Varchar as 1 and 2.

mysql> select * from user;
+----+-------+------+--------+-----------+
| id | name  | age  | gender | user_type |
+----+-------+------+--------+-----------+
|  1 | John  |   25 | male   | AQ        |
|  2 | Alice |   30 | female | AL        |
|  3 | Emma  |   28 | 2      | 2         |
|  4 | Mike  |   40 | 1      | 1         |
+----+-------+------+--------+-----------+
4 rows in set (0.00 sec)

Describe the feature you'd like

I would like TiDB-DM to Replicate MySQL's ENUM data as it is, after I changed the MySQL ENUM data type to VARCHAR data type in TiDB.

Describe alternatives you've considered

TiDB not supporting ENUM datatypes in TiFLASH. So it will be better to update the feature for the TiFLASH ENUM issue.

Teachability, Documentation, Adoption, Migration Strategy

I used this because the TiFLASH ENUM is an experimental feature. So, I thought that converting the data type from ENUM to VARCHAR can make the TiFLASH replica perform without any interruption.

[dveeden]

/area dm