MySQL Bugs: #110380: The description of "Specifying a character set" is incorrect in Online DDL

Bug #110380	The description of "Specifying a character set" is incorrect in Online DDL
Submitted:	15 Mar 2023 12:54	Modified:	27 Mar 2023 9:16
Reporter:	Jinghua Lin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-table...

In this part, "Specifying a character set" is described as not allowed to permits concurrent DML. But actually, this operation can use the INPLACE algorithm and specify LOCK = NONE. And the below example is written like this.

So "Specifying a character set" should be "yes" in "Permits Concurrent DML" field.

How to repeat:
1.create a table:

create table a(id int primary key,name varchar(10)) character set utf8mb4;

2. insert some data:

insert into a values(1,'a'),(2,'b'),(3,'c');

3. execute ddl:

alter table a character set utf8,algorithm=inplace,lock=none;

Hi,

Have you tried running concurrent DML operations on that table, while that DDL is still in progress ???

How did it go ?????

Hi,

I'm sorry, but this ddl statement is executing so fast that I can't simulate concurrent DML

However, as I understand it, the fact that DDL can specify lock=none means that DML can be executed concurrently, doesn't it?

Hi Mr. Lin,

Yes, we know that this is a documentation report.

However, in order to verify, we need to repeat it.

Since that DDL is so fast, that you can not run a single DML, how can you know that what you are reporting is true ?????

Hi,

OK, let's discuss in another way.

1. DDL can specify lock=none meaning that DML can be executed concurrently.

2. The documentation says that this DDL does not allow concurrent DML execution.

3. "alter table a character set utf8,algorithm=inplace,lock=none;" can be executed successfully.

I don't know if the above description can express my doubts. I think there is something wrong with at least one of the program or documentation.

Or there can be a problem with a test case.

This is just a simple DDL case, and the statements are the same as the example in the documentation. And I asked other DBAs and they all think this example is fine.

If a DDL does not support specifying lock=none, an error will be returned.

mysql> alter table a CONVERT TO CHARACTER SET utf8, LOCK=NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED.

Please point out the problem of my case or give me a case that matches the content of the documentation.

Hi Mr. Lin,

There is a very good reason why this is not a bug.

If you have written your test case correctly, you would have got the following warning for the ALTER TABLE:

Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.

Hence, our documentation is correct, since character set is NOT actually changed AT ALL.

Not a bug.

Hi,

I am glad to receive your reply.

This warning does cause a bit of misunderstanding here, but in fact, as it says, we are converting utf8mb4 to utf8mb3 (utf8), which can be seen in the modified table structure. It doesn't mean that character set is not actually changed at all.

I can show you other test cases:

mysql> alter table a CHARACTER SET = big5,ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table a CHARACTER SET = gbk,ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table a CHARACTER SET = latin1,ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

The above DDL can also be executed successfully in the same table. So I still stick to my point of view.

You seem to have been using an empty table .......

No, I actually created another large table for validation to keep things strict.

mysql> show create table a;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name1` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `name2` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `name3` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=393198 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from a ;
+----------+
| count(*) |
+----------+
|   196610 |
+----------+
1 row in set (1.85 sec)

The DDL executes on this table with the same result, successfully and quickly.

Actually, you are correct.

However, our Reference Manual for 8.0.32  has been already altered:

Specifying a character set

ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;
Rebuilds the table if the new character encoding is different.

The above is a recommended syntax for specifying a new character set.

You didn't get my point.

"ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE,
LOCK=NONE;"

This statement can be executed successfully, especially the lock=none part, indicating that it permits concurrent DML

But the table in the documentation clearly states that this DDL cannot execute DML concurrently

Operation					Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Specifying a character set	No		Yes			Yes*			No						No

They are contradictory to each other. This is the problem

Actually, it is not a problem.

Simply, what I quoted to you is copy / paste from the Reference Manual ......

I know what you quoted to me is copy / paste from the Reference Manual.

ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE,
LOCK=NONE;

This statement is correct and could be exeucted.

The problem is what's in the table above (Table 15.21 Online DDL Support for Table Operations):

Operation                     Permits Concurrent DML
Specifying a character set    no

It indicates that this statement cannot execute DML concurrently.

If this description is correct, then the above statement will not execute properly.

These two descriptions contradict each other.

Hi,

That 'No' was changed to 'Yes" last week ....... 

New version of the Red. Manual will come out with one of the next releases of 8.0.

Not need to go through the entire process of  verification, triaging, assignment and 8 (eight) other steps for such a small error in copy / paste.