Bug #117539 Different behaviors when altering table and its columns charset from utf8mb3 to utf8mb4

• Submitted: 21 Feb 7:15
• Modified: 21 Feb 8:29
• Reporter: Hai Nguyen Xuan
• Status: Analyzing
• Category: MySQL Server
• Severity: S5 (Performance)
• Version: 8.0.37
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[21 Feb 7:15] Hai Nguyen Xuan

Description:
Hi team,

When altering the table default charset and its columns' charsets from utf8mb3 to utf8mb4 in one statement, as described in this document https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html, then it takes a while to complete for a table with ~2 million rows:

mysql> ALTER TABLE `t1`
    -> DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci,
    -> MODIFY COLUMN `c1` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
    -> MODIFY COLUMN `c2` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
    -> MODIFY COLUMN `c3` varchar(255) COLLATE utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (1 min 2.69 sec)
Records: 0  Duplicates: 0  Warnings: 0

However, when separating it into two commands like this, these two commands complete instantly:

mysql> ALTER TABLE `t1`
    -> MODIFY COLUMN `c1` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
    -> MODIFY COLUMN `c2` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
    -> MODIFY COLUMN `c3` varchar(255) COLLATE utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `t1`
    -> DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Is this the intended behavior? I haven't found any document that explains this yet.

Thank you!

How to repeat:
-- create test table and then insert some dummy data
CREATE TABLE t1 (
c1 text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
c2 text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
c3 varchar(255)
) CHARSET=utf8mb3 COLLATE utf8mb3_unicode_ci;

-- this command takes a while to complete
ALTER TABLE `t1`
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci,
MODIFY COLUMN `c1` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
MODIFY COLUMN `c2` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
MODIFY COLUMN `c3` varchar(255) COLLATE utf8mb4_0900_ai_ci;

-- these two commands complete instantly
ALTER TABLE `t1`
MODIFY COLUMN `c1` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
MODIFY COLUMN `c2` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
MODIFY COLUMN `c3` varchar(255) COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE `t1`
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;