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ä created issue -
            marko Marko Mäkelä made changes -
            Field Original Value New Value
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            NRE Approved Yes [ 10304 ]
            marko Marko Mäkelä made changes -
            Summary Instant NOT NULL removal and CHAR or VARCHAR extension for ROW_FORMAT=REDUNDANT Instant NOT NULL removal and conversion to a wider type for ROW_FORMAT=REDUNDANT
            marko Marko Mäkelä made changes -
            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.

            For {{ROW_FORMAT=REDUNDANT}}, we should allow an instantaneous change of a column from {{NOT NULL}} to {{NULL}}.

            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.

            Unfortunately, we cannot support instant conversion of {{CHAR}} to {{VARCHAR}}, because the instant conversion would not trim trailing space, unlike the full conversion:
            {code:sql}
            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;
            {code}
            The above example would read {{'a'}} from the column and not {{'a '}}.

            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?
            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.

            For {{ROW_FORMAT=REDUNDANT}}, we should allow an instantaneous change of a column from {{NOT NULL}} to {{NULL}}.

            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 'default 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 'default row' 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:
            {code:sql}
            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;
            {code}
            The above example would read {{'a'}} from the column and not {{'a '}}.

            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?
            julien.fritsch Julien Fritsch made changes -
            Epic Link PT-80 [ 68561 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Rank Ranked higher
            ralf.gebhardt Ralf Gebhardt made changes -
            Target end 12/Feb/19 [ 2019-02-12 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Eugene Kosov [ kevg ]
            marko Marko Mäkelä made changes -
            kevg Eugene Kosov (Inactive) made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            marko Marko Mäkelä made changes -
            kevg Eugene Kosov (Inactive) made changes -
            Assignee Eugene Kosov [ kevg ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Eugene Kosov [ kevg ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            kevg Eugene Kosov (Inactive) made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            kevg Eugene Kosov (Inactive) made changes -
            Assignee Eugene Kosov [ kevg ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Eugene Kosov [ kevg ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            midenok Aleksey Midenkov made changes -
            Assignee Eugene Kosov [ kevg ] Aleksey Midenok [ midenok ]
            midenok Aleksey Midenkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            kevg Eugene Kosov (Inactive) made changes -
            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.

            For {{ROW_FORMAT=REDUNDANT}}, we should allow an instantaneous change of a column from {{NOT NULL}} to {{NULL}}.

            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 'default 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 'default row' 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:
            {code:sql}
            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;
            {code}
            The above example would read {{'a'}} from the column and not {{'a '}}.

            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?
            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.

            For {{ROW_FORMAT=REDUNDANT}}, we should allow an instantaneous change of a column from {{NOT NULL}} to {{NULL}}.

            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 'metadata row' 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:
            {code:sql}
            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;
            {code}
            The above example would read {{'a'}} from the column and not {{'a '}}.

            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?
            kevg Eugene Kosov (Inactive) made changes -
            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.

            For {{ROW_FORMAT=REDUNDANT}}, we should allow an instantaneous change of a column from {{NOT NULL}} to {{NULL}}.

            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 'metadata row' 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:
            {code:sql}
            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;
            {code}
            The above example would read {{'a'}} from the column and not {{'a '}}.

            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?
            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.

            For {{ROW_FORMAT=REDUNDANT}}, we should allow an instantaneous change of a column from {{NOT NULL}} to {{NULL}}.

            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 'metadata row' 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:
            {code:sql}
            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;
            {code}
            The above example would read {{'a'}} from the column and not {{'a '}}.

            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?
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            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.

            For {{ROW_FORMAT=REDUNDANT}}, we should allow an instantaneous change of a column from {{NOT NULL}} to {{NULL}}.

            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 'metadata row' 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:
            {code:sql}
            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;
            {code}
            The above example would read {{'a'}} from the column and not {{'a '}}.

            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?
            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:
            {code:sql}
            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;
            {code}
            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?
            Summary Instant NOT NULL removal and conversion to a wider type for ROW_FORMAT=REDUNDANT Instant failure-free data type conversions
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenok [ midenok ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            midenok Aleksey Midenkov made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            kevg Eugene Kosov (Inactive) made changes -
            marko Marko Mäkelä made changes -
            Attachment UNSIGNED-bigger-signed.patch [ 47290 ]
            marko Marko Mäkelä made changes -
            issue.field.resolutiondate 2019-02-13 16:54:37.0 2019-02-13 16:54:37.819
            marko Marko Mäkelä made changes -
            Fix Version/s 10.4.3 [ 23230 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status In Review [ 10002 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            NRE Projects NRE-310017 RM_104_NRE NRE-310017 RM_104_NRE RM_102ES_CANDIDATE RM_103ES_CANDIDATE
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 86022 ] MariaDB v4 [ 133494 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -

            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.