MySQL Bugs: #120595: Contribution by Tencent: Relax max row size restriction for INSTANT DDL

Bug #120595	Contribution by Tencent: Relax max row size restriction for INSTANT DDL
Submitted:	2 Jun 4:45	Modified:	16 Oct 7:22
Reporter:	Yin Peng (OCA)	Email Updates:
Status:	Need Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	9.7.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Since MySQL 8.0.29, InnoDB uses a "row versioning" mechanism to support
INSTANT ADD/DROP COLUMN. As part of this implementation, InnoDB performs a
max row size check during INSTANT DDL: it calculates the theoretical maximum
row size (assuming every column is filled to its maximum length) and rejects
the INSTANT DDL if this size exceeds the page record limit.

However, this check is overly conservative and causes practical problems:

1. **Upgrade compatibility regression**: In MySQL early 8.0 (before
   8.0.29), INSTANT ADD COLUMN (the old implementation) did not have this
   max row size restriction. After upgrading to 8.0.29+, tables that
   previously allowed INSTANT ADD COLUMN may now be rejected, forcing users
   to fall back to the much slower COPY/INPLACE algorithm. This is a
   significant regression for users who relied on INSTANT DDL for large
   tables in production.

2. **Theoretical vs. practical row size**: The check uses the theoretical
   maximum row size, but in practice, rows rarely reach their maximum size.
   For example, a table with many VARCHAR(255) or CHAR(200) columns will
   almost never have all columns filled to their full length simultaneously.
   The actual row size at DML time (INSERT/UPDATE) is already enforced by
   InnoDB's "Record too big" check, which is sufficient to prevent
   oversized records from being written to pages.

This contribution relaxes the restriction by introducing a new system
variable `innodb_instant_ddl_strict_row_size` (default ON for backward
compatibility):

- **ON** (default): Preserve the existing strict check. INSTANT DDL is
  rejected if the theoretical max row size exceeds the page limit.
- **OFF**: Issue a warning but allow INSTANT DDL to proceed. The actual
  row size enforcement is deferred to DML time — as long as the real
  data fits within the page limit, the operation succeeds normally.

When the strict check is relaxed, a correctness issue arises during
rollback: after INSTANT ADD COLUMN, a record originally inserted at an
older row version (with fewer columns) may be updated and then rolled
back. If rollback rebuilds the record using current_row_version, the
restored record will include all INSTANT-added columns (filled with
default values), making it physically larger than the original. When the
theoretical max row size exceeds the page limit, this inflated record
may not fit in the page, causing the rollback to fail.

To solve this, this patch records the original row_version of each
record in the undo log during UPDATE/DELETE operations, and uses that
original version (instead of current_row_version) when rebuilding the
record during rollback and MVCC version construction. This ensures the
restored record contains only the columns visible at its original
version, maintaining the original compact physical format.

For records inserted before any INSTANT DDL (which have no version
flag in the record header), version 0 is written to the undo log,
representing the original table schema. During rollback, such records
are restored with the versioned bit set and version=0, so they are
interpreted using only the columns from the original table definition.

How to repeat:
N

Relax max row size restriction for INSTANT DDL (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: patch.txt (text/plain), 99.50 KiB.

See also: https://bugs.mysql.com/bug.php?id=119280

The maximum-row-size check is a correctness safeguard for UPDATE/ROLLBACK, not just a restriction on the size of newly inserted rows. If not there, it can allow an UPDATE to succeed even though that UPDATE cannot subsequently be rolled back.

For simplicity, assume an 8,000-byte maximum in-page record size, ignore record overhead, and assume off-page storage cannot make the oversized record fit.
1. An existing row occupies 7,500 bytes.
2. We perform an instant ADD COLUMN whose default value occupies 1,000 bytes. If we allow this operation, the existing physical row still occupies 7,500 bytes because the default is supplied from metadata rather than stored in the row.
3. In a subsequent transaction, an UPDATE changes an existing column’s value from 3,000 bytes to 1,000 bytes. When the row is rewritten, the instant added column’s default is also materialized. The resulting size is:
7500 − 2000 + 1000 = 6500 bytes
This fits, so the UPDATE succeeds.
4. Then we rollback the transaction. Rollback restores the original column value, adding back 2,000 bytes. However, the instantly added default remains materialized:
6500 + 2000 = 8500 bytes
The restored record now exceeds the 8,000-byte limit.
5. Rollback fails, which causes the server to crash.
6. Checking the actual row size during INSERT/UPDATE does not prevent this: the updated row fits; the record needed to undo the update does not.
7. Moreover, recovery can encounter the same failure when retrying rollback and it may cause a repeated crash recovery loop.

The maximum-row-size check prevents this situation without scanning existing rows, preserving the metadata-only nature of instant ADD. This rationale is also documented in `materialize_instant_default()`.

I see this has also been identified by you and there is a proposal to solve it:

```
To solve this, this patch records the original row_version of each
record in the undo log during UPDATE/DELETE operations, and uses that
original version (instead of current_row_version) when rebuilding the
record during rollback and MVCC version construction. This ensures the
restored record contains only the columns visible at its original
version, maintaining the original compact physical format.
```

Thanks for the idea and efforts!

However, these are very low level changes and to be evaluated/tested for correctness and regression. I wonder if you could please provide some insight on the testing performed for this patch for evaluation. 

For example : 
- Does it take care of all row formats (say Redundant?)
- Does it take care of legacy row format (instant add done pre 8.0.29 and rows inserted after that and then there is upgrade). Because upgrade doesn't change anything on rows, so all the legacy rows shall be handled.
- We also support drop column with INSTANT. And it doesn't change the rows on disk. So once a column is dropped, an existing row might have dropped col value, but when we do UPDATE/ROLLBACK, how do we restore the row to original version with 
the value of dropped column.

These are the few questions which come to my mind.