pingcap / pingcap/tidb

Wrong value for replicating enum from mysql to Tidb

Open
#51,908 5 comments 0 reactions 0 assignees View on GitHub
compatibility-mysql80 type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

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

### 2. What did you expect to see? (Required)

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

### 3. What did you see instead (Required)

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

### 4. What is your TiDB version? (Required)

```
mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.5.1
Edition: Community
Git Commit Hash: 7d16cc79e81bbf573124df3fd9351c26963f3e70
Git Branch: heads/refs/tags/v7.5.1
UTC Build Time: 2024-02-27 14:28:32
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.