Failed to Convert Character Set

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 转换字符集失败

| username: 孤君888

【TiDB Usage Environment】Production
【TiDB Version】v6.1.0
【Encountered Problem】

【Reproduction Path】What operations were performed to encounter the problem
【Problem Phenomenon and Impact】

The problem is as follows: the default TiDB character set rule is utf8mb4_bin, and now it needs to be changed to utf8mb4_general_ci. The error is as follows:

mysql> show index from xxxxx
+----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
| Table          | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | Clustered |
+----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
| xxx |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       | YES       |
| xxx |          0 | unq_e_code |            1 | e_code      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       | NO        |
+----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
2 rows in set (0.01 sec)

mysql>
mysql> alter table xxxx convert to character set utf8mb4 collate utf8mb4_general_ci;
ERROR 8200 (HY000): Unsupported converting collation of column 'e_code' from 'utf8mb4_bin' to 'utf8mb4_general_ci' when index is defined on it.
2
| username: wuxiangdong | Original post link

How about deleting the index?

3
| username: 孤君888 | Original post link

Deleting the index is possible, but the cost is too high; both the primary key index and secondary indexes need to be deleted.

4
| username: Billmay表妹 | Original post link

Refer to this~

5
| username: 孤君888 | Original post link

I looked into it, but it still didn’t solve my problem.

6
| username: tracy0984 | Original post link

I tested on version 6.1.1 and found that simply deleting the index on the e_code column solves the issue. During testing, the data type of the id column was set to int.

7
| username: 孤君888 | Original post link

You can try with a table that has multiple unique index keys.

8
| username: 特雷西-迈克-格雷迪 | Original post link

Character sets and collations only apply to character types such as VARCHAR. If the type is INT or DATE, these attributes do not exist.

9
| username: kkpeter | Original post link

I also encountered this problem, is there a solution?

10
| username: Jellybean | Original post link

In this case, it seems that you can only create a new table, set the character set encoding to what you need, and then re-import the data, which would be faster.