Bug #106231 'alter table convert' command operation suggestion

• Submitted: 21 Jan 2022 3:48
• Modified: 21 Jan 2022 13:59
• Reporter: chaizhigang chai
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.27
• OS: Any
• CPU Architecture: Any

[21 Jan 2022 3:48] chaizhigang chai

Description:
When all varchar and text fields in the table are not indexed,
Using 【 alter table t1 modify xxx collate xxx 】 is very fast,
When using 【 alter table t1 CONVERT TO CHARACTER SET xxxx COLLATE xxxx; 】 will rebuild the table and execute very fast.

How to repeat:
mysql> create table t1(id int primary key auto_increment,name varchar(2000)) character set utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 select null,repeat('a',2000);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select null,repeat('a',2000) from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

..... Repeat the above operation

mysql> insert into t1 select null,repeat('a',2000) from t1;
Query OK, 65536 rows affected (2.24 sec)
Records: 65536  Duplicates: 0  Warnings: 0

#Modifying the collation of a field is very fast because there is no index

mysql> alter table t1 modify name varchar(2000) collate utf8mb4_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 modify name varchar(2000) collate utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#Modifying the table-level collation will rebuild the table and execute slowly

mysql> alter table t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected (3.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

Suggested fix:
I want to make more judgments internally when modifying the table-level collation. If the varchar, text and other columns in the table do not have indexes, I should just modify the metadata to speed up the execution of DDL commands.

[21 Jan 2022 13:59] MySQL Verification Team

Hi Mr. chai,

Thank you for your bug report.

However, this is not a bug.

This is intended behaviour and is fully explained in the sub-sub-chapter 13.1.9 of our Reference Manual, under the header of "Changing the character set".

In short, that particular DDL has to check each of the rows in order to make sure that there is sufficient room for all tuples to contain a provided character set. Read more about it in the Manual .....

Not a bug .....

[21 Jan 2022 13:59] MySQL Verification Team

Hi Mr. chai,

Thank you for your bug report.

However, this is not a bug.

This is intended behaviour and is fully explained in the sub-sub-chapter 13.1.9 of our Reference Manual, under the header of "Changing the character set".

In short, that particular DDL has to check each of the rows in order to make sure that there is sufficient room for all tuples to contain a provided character set. Read more about it in the Manual .....

Not a bug .....