TRUNCATE and OPTIMIZE are being refused due to "row size too large"

By default (innodb_strict_mode=ON), InnoDB attempts to ensure at DDL time that any INSERT to the table has a chance of succeeding. MDEV-19292 recently revised the "row size too large" check in InnoDB. The check still is somewhat inaccurate; that should be addressed in MDEV-20194.

Note: If a table contains multiple long string columns so that each column is part of a column prefix index, then an UPDATE that attempts to modify all those columns at once may fail, because the undo log record might not fit in a single undo log page (of innodb_page_size). In the worst case, the undo log record would grow by about 3KiB of for each updated column.

The DDL-time check (which was introduced in the InnoDB Plugin for MySQL 5.1) is ‘optional’ in the sense that when the maximum B-tree record size or undo log record size would be exceeded, the DML operation will fail and the transaction will be properly rolled back.

The following change to a test case demonstrates the problem:

diff --git a/mysql-test/suite/innodb/t/row_size_error_log_warnings_3.test b/mysql-test/suite/innodb/t/row_size_error_log_warnings_3.test

index 35b86cc4c46..af6c15e0e6b 100644

--- a/mysql-test/suite/innodb/t/row_size_error_log_warnings_3.test

+++ b/mysql-test/suite/innodb/t/row_size_error_log_warnings_3.test

@@ -18,7 +18,15 @@ CREATE TABLE t1 (

   ,col_10 TEXT

   ,col_11 TEXT

 ) ENGINE=INNODB ROW_FORMAT=COMPACT;

+--enable_warnings

+TRUNCATE TABLE t1;

+OPTIMIZE TABLE t1;

+ALTER TABLE t1 FORCE;

+SET innodb_strict_mode = ON;

+TRUNCATE TABLE t1;

+OPTIMIZE TABLE t1;

+ALTER TABLE t1 FORCE;

 DROP TABLE t1;

+--disable_warnings

 SET @@global.log_warnings = 2;

-SET innodb_strict_mode = 1;

We want TRUNCATE and OPTIMIZE and a trivial ALTER TABLE…FORCE to succeed, because the table definition is not actually changing. But, we do want warnings to be issued for all these operations, no matter if innodb_strict_mode is set.