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

Refuse ALGORITHM=INSTANT when the row size is too large

    XMLWordPrintable

    Details

      Description

      With instant DROP COLUMN, clustered index records will contain traces of dropped columns, as follows:

      1. In ROW_FORMAT=REDUNDANT, dropped columns will be stored as 0 bytes, but they will consume 1 or 2 bytes per column in the record header.
      2. In ROW_FORMAT=COMPACT or ROW_FORMAT=DYNAMIC, dropped columns will be stored as NULL if allowed. This will consume 1 bit per nullable column.
      3. In ROW_FORMAT=COMPACT or ROW_FORMAT=DYNAMIC, dropped NOT NULL columns will be stored as 0 bytes if allowed. This will consume 1 byte per NOT NULL variable-length column.
      4. In ROW_FORMAT=COMPACT or ROW_FORMAT=DYNAMIC, dropped NOT NULL fixed-length columns will be stored using the fixed number of bytes.

      The metadata record will be 20 bytes larger than user records, because it will contain a metadata BLOB pointer.

      We should refuse ALGORITHM=INSTANT (and require a table rebuild) if:

      1. The metadata record would grow too big to fit in the index, even after moving the default values for long variable-length fields off-page.
      2. The maximum length of user records would exceed the maximum size of an index page.

      The limit on the user records would kick in when the default values for any instantly added columns in the metadata record are NULL or short.

      Here is a test case that exceeds the metadata record size:

      diff --git a/mysql-test/suite/innodb/t/instant_alter_limit.test b/mysql-test/suite/innodb/t/instant_alter_limit.test
      index ded14eee89b..5ccb989edf1 100644
      --- a/mysql-test/suite/innodb/t/instant_alter_limit.test
      +++ b/mysql-test/suite/innodb/t/instant_alter_limit.test
      @@ -37,6 +37,19 @@ SELECT variable_value-@old_instant instants
       FROM information_schema.global_status
       WHERE variable_name = 'innodb_instant_alter_column';
       
      +SELECT * FROM t;
      +ALTER TABLE t ADD COLUMN (c CHAR(255) NOT NULL, d BIGINT NOT NULL),
      +ALGORITHM=INSTANT;
      +
      +let $n=253;
      +while ($n) {
      +dec $n;
      +ALTER TABLE t DROP b, DROP c, DROP d,
      +ADD COLUMN (b INT NOT NULL, c CHAR(255) NOT NULL, d BIGINT NOT NULL),
      +ALGORITHM=INSTANT;
      +}
      +
      +UPDATE t SET b=b+1,d=d+1,c='foo';
       SELECT * FROM t;
       
       DROP TABLE t;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              marko Marko Mäkelä
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: