Unsupported modify charset from utf8mb4 to utf8

FAQ
1

  • Before upgrading, the following operations are executed in v2.1.1 and v2.1.2.

    create table t(a varchar(10)) charset=utf8;

    Query OK, 0 rows affected
Time: 0.109s

    show create table t;

    +-------+-------------------------------------------------------+
| Table | Create Table                                          |
+-------+-------------------------------------------------------+
| t     | CREATE TABLE `t` (                                    |
|       |   `a` varchar(10) DEFAULT NULL                        |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-------------------------------------------------------+

    In the above example, show create table only shows the charset of the table, but the charset of the column is actually UTF8MB4, which can be confirmed by obtaining the schema through the HTTP API. However, when a new table is created, the charset of the column should stay consistent with that of the table. This bug has been fixed in v2.1.3.

  • After upgrading, the following operations are executed in v2.1.3 and the later versions.

    show create table t;

    +-------+--------------------------------------------------------------------+
| Table | Create Table                                                       |
+-------+--------------------------------------------------------------------+
| t     | CREATE TABLE `t` (                                                 |
|       |   `a` varchar(10) CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin              |
+-------+--------------------------------------------------------------------+
1 row in set
Time: 0.007s

    alter table t change column a a varchar(20);

    ERROR 1105 (HY000): unsupported modify charset from utf8mb4 to utf8

Solution:

  • Starting from v2.1.3, TiDB supports modifying the charsets of the column and the table, so it is recommended to modify the table charset into UTF8MB4.

    alter table t convert to character set utf8mb4;

  • You can also specify the column charset as done in Issue #1, making it stay consistent with the original column charset (UTF8MB4).

    alter table t change column a a varchar(20) character set utf8mb4;