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

Instant failure-free data type conversions

    XMLWordPrintable

Details

    Description

      The original InnoDB record format (retroactively named ROW_FORMAT=REDUNDANT in MySQL 5.0.3) basically treats every column as variable-length and possibly NULL. That is why we can freely change those columns, by changing the metadata only.

      MariaDB 10.2 supports VARCHAR column extension, but only if the maximum length does not change from less than 256 to at least 256 bytes. For ROW_FORMAT=REDUNDANT we should support this unconditionally.

      Likewise, for ROW_FORMAT=REDUNDANT we can support the extension of any CHAR column, or converting VARCHAR to a CHAR that is at least as wide.

      We can also support converting INT to BIGINT and similar. Maybe also conversion from an unsigned integer to a signed wider integer (such as INT UNSIGNED to BIGINT), but this would require a change to the 'metadata row' format to indicate that the column originally was in unsigned format.

      To support instant conversion of CHAR to VARCHAR, we should store a flag in the MDEV-15562 metadata record to indicate that trailing space should be trimmed, just like it would if the table was rebuilt. In this case, any secondary indexes on the column would have to be rebuilt and this is, obviously, not and INSTANT operation:

      CREATE TABLE t(a CHAR(2)) ENGINE=InnoDB;
      INSERT INTO t VALUES('a ');
      SELECT HEX(a) FROM t;
      ALTER TABLE t CHANGE a a VARCHAR(2);
      SELECT HEX(a) FROM t;
      DROP TABLE t;
      

      The above example would read 'a' from the column and not 'a '.
      Similarly, for changing INT UNSIGNED to BIGINT we should add a flag that the shorter column was unsigned.

      TO BE DETERMINED: On secondary indexes of full columns (not column prefixes), do we have to rebuild the index when the length of a fixed-length column (CHAR, BINARY) is changed?

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              1 Vote for this issue
              Watchers:
              6 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.