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

Instant failure-free data type conversions

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

            marko Marko Mäkelä added a comment - - edited

            I ported all generic changes from bb-10.4-MDEV-17520 to a new branch bb-10.4-MDEV-15563.
            It implements an instantaneous removal of NOT NULL constraints for ROW_FORMAT=REDUNDANT tables, and it includes some minor refactoring.

            marko Marko Mäkelä added a comment - - edited I ported all generic changes from bb-10.4- MDEV-17520 to a new branch bb-10.4- MDEV-15563 . It implements an instantaneous removal of NOT NULL constraints for ROW_FORMAT=REDUNDANT tables, and it includes some minor refactoring.

            Results of RQG testing (several hours with rqg_batch.pl on box with 8 CPU's) on
            bb-10.4-MDEV-15563  commit b710e66b4912c0a0806609330c78679d809ecb36
            - three new bugs found but all were not related to bb-10.4-MDEV-15563
              - https://jira.mariadb.org/browse/MDEV-17989    InnoDB problem which exists already in 10.2
              - to https://jira.mariadb.org/browse/MDEV-15850  added as activity    Two RQG problems+now fixed.
            - the RQG grammar 'table_stress' was extended to include
               - certain InnoDB Row formats (especially redundant)
               - ALTERING columns of different type between NULL (allowed) and NOT NULL and back
            - especially the RQG test "table_stress" revealed a crowd of asserts but they were all
              already covered by bug reports for 10.2 till 10.4 
            

            mleich Matthias Leich added a comment - Results of RQG testing (several hours with rqg_batch.pl on box with 8 CPU's) on bb-10.4-MDEV-15563 commit b710e66b4912c0a0806609330c78679d809ecb36 - three new bugs found but all were not related to bb-10.4-MDEV-15563 - https://jira.mariadb.org/browse/MDEV-17989 InnoDB problem which exists already in 10.2 - to https://jira.mariadb.org/browse/MDEV-15850 added as activity Two RQG problems+now fixed. - the RQG grammar 'table_stress' was extended to include - certain InnoDB Row formats (especially redundant) - ALTERING columns of different type between NULL (allowed) and NOT NULL and back - especially the RQG test "table_stress" revealed a crowd of asserts but they were all already covered by bug reports for 10.2 till 10.4

            I pushed bb-10.4-MDEV-15563 (which only implemented the original first part of this task, instant NOT NULL removal for ROW_FORMAT=REDUNDANT tables) to 10.4, and it should be part of the 10.4.1 release.

            This task should no longer depend on MDEV-17520, if we change the course so that the most generic instant length and type changes are limited to ROW_FORMAT=REDUNDANT.

            For ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPACT, we should be able to do the following instantly, without changing the storage format:

            1. change CHAR(0) to any VARCHAR (I believe that it is encoded as variable-length)
            2. change a CHAR that is longer than 255 bytes to VARCHAR or wider CHAR
            3. change a VARCHAR that is shorter than 128 bytes into any longer VARCHAR
            4. (10.2 already supports changing VARCHAR to wider one, except if the maximum length grows from less than 256 bytes to at least 256 bytes)
            marko Marko Mäkelä added a comment - I pushed bb-10.4- MDEV-15563 (which only implemented the original first part of this task, instant NOT NULL removal for ROW_FORMAT=REDUNDANT tables) to 10.4, and it should be part of the 10.4.1 release. This task should no longer depend on MDEV-17520 , if we change the course so that the most generic instant length and type changes are limited to ROW_FORMAT=REDUNDANT . For ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPACT , we should be able to do the following instantly, without changing the storage format: change CHAR(0) to any VARCHAR (I believe that it is encoded as variable-length) change a CHAR that is longer than 255 bytes to VARCHAR or wider CHAR change a VARCHAR that is shorter than 128 bytes into any longer VARCHAR (10.2 already supports changing VARCHAR to wider one, except if the maximum length grows from less than 256 bytes to at least 256 bytes)

            I split this into 3 more commits:
            MDEV-15563: Allow instant VARCHAR extension from <128 bytes
            MDEV-15563: Instant VARCHAR extension for ROW_FORMAT=REDUNDANT
            MDEV-15563: Instant ROW_FORMAT=REDUNDANT column extension
            The last two are based on the work of midenok.

            I decided to omit UNSIGNED-bigger-signed.patch, because something like that could be better done later as part of MDEV-17520, in a way that does not add much overhead to the current ROW_FORMAT=DYNAMIC.

            marko Marko Mäkelä added a comment - I split this into 3 more commits: MDEV-15563: Allow instant VARCHAR extension from <128 bytes MDEV-15563: Instant VARCHAR extension for ROW_FORMAT=REDUNDANT MDEV-15563: Instant ROW_FORMAT=REDUNDANT column extension The last two are based on the work of midenok . I decided to omit UNSIGNED-bigger-signed.patch , because something like that could be better done later as part of MDEV-17520 , in a way that does not add much overhead to the current ROW_FORMAT=DYNAMIC .

            The third change (instant fixed-size column extension for ROW_FORMAT=REDUNDANT) had to be reverted due to incorrect results for indexed columns, in MDEV-18627. We might revive it in a later version related to the task MDEV-17520.

            marko Marko Mäkelä added a comment - The third change (instant fixed-size column extension for ROW_FORMAT=REDUNDANT ) had to be reverted due to incorrect results for indexed columns, in MDEV-18627 . We might revive it in a later version related to the task MDEV-17520 .

            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.