Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21429

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

    XMLWordPrintable

Details

    Description

      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.

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.