Bug #106162 Whether "instant add column" checks "row size limits" ?
Submitted: 14 Jan 2022 5:06 Modified: 17 Jan 2022 16:23
Reporter: zhenzhen li Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:8.0.12, 8.0.20, 8.0.27 OS:CentOS (7.2.1511)
Assigned to: CPU Architecture:x86 (Intel(R) Xeon(R) CPU E5-2670 v3 @ 2.30GHz)
Tags: INSTANT add column, row size limits

[14 Jan 2022 5:06] zhenzhen li
Description:
I needed to use "instant add column", and then I looked at the documentation 
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

So, I see some limitations, and one of them is: 

Row size limits are not evaluated when adding a column. However, row size limits are checked during DML operations that insert and update rows in the table.

Does this represent a limit of 65535 ?
Then I tried it, and the result differ from the documentation. It was an error: 

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

How to repeat:
1. creat a table 

create table longColumn19
(
   name14 VARCHAR(1000),
   name13 VARCHAR(1000),
   name12 VARCHAR(1000),
   name11 VARCHAR(1000),
   name10 VARCHAR(1000),
   name9 VARCHAR(1000),
   name8 VARCHAR(1000),
   name7 VARCHAR(1000),
   name6 VARCHAR(1000),
   name5 VARCHAR(1000),
   name4 VARCHAR(1000),
   name3 VARCHAR(1000),
   name2 VARCHAR(1000),
   name1 VARCHAR(1000)
);

2. add two column, success

alter table longColumn19 add column id2 VARCHAR(2000), ALGORITHM=INSTANT;

alter table longColumn19 add column id3 char(255), ALGORITHM=INSTANT;

3. The next one failed:

alter table longColumn19 add column id4 char(255), ALGORITHM=INSTANT;

  Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Suggested fix:

Is the "row size limits" also checked when "instant add column" ?
[14 Jan 2022 5:10] zhenzhen li
and this blog: 
https://dev.mysql.com/blog-archive/mysql-8-0-innodb-now-supports-instant-add-column/
[14 Jan 2022 12:47] MySQL Verification Team
Hi Mr. li,

Thank you for your bug report.

However, this is not a bug.

Row size can not be larger than how it is specified in the Manual. So, follow the instructions there and change some columns into any of the CLOB types and it will work.

Hence, documentation is correct, since this is a limit that can not be surpassed, not even by ADD COLUMN command, nor any other ........

Not a bug.
[14 Jan 2022 16:40] zhenzhen li
Hi,

I know the row size limit
Where I am confused is the line in the documentation:

Row size limits are not evaluated when adding a column. However, row size limits are checked during DML operations that insert and update rows in the table.
[14 Jan 2022 19:05] zhenzhen li
and,

I mean, the content of the documentation may not be correct

Because the row size limits are checked when adding a column.

But the content of the document is: "Row size limits are not evaluated when adding a column"
[17 Jan 2022 13:43] MySQL Verification Team
Hi Mr. Li,

Documentation on the adding column is very correct.

Simply, when you run that instruction, MySQL will not check the limit, but it will attempt to add a column. It can succeed or fail, depending on prior definition of the entity.

Hence, our documentation is correct.
[17 Jan 2022 15:43] zhenzhen li
Hi,
Maybe we understand it differently ...
Maybe it's because of my poor English ...

I don't understand your answer,

If MySQL doesn't check the operation(add column), why I got an error ...?

And your answer: It can succeed or fail, depending on prior definition of the entity.

Isn't this the result of mysql check ...?
[17 Jan 2022 16:10] MySQL Verification Team
Hi,

The answer is simple. There are checks in several stages, as it is explained throughout our Reference Manual.

When you issue a DDL statement, it is checked on some aspects of the feasibility, while not on all. Other aspects are checked only at runtime, such as "row size limit". 

Hence, we can not totally re-organise and re-write MySQL code to check everything before attempting the operation. 

Not a bug.
[17 Jan 2022 16:23] zhenzhen li
Hi,
thank you for your reply

I see what you mean now. We really think differently ..

Because my first impression of the manual is: if MySQL doesn't check, it should succeed. And I think all steps before success or failure should be called "checks" ...