Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
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
- blocks
-
-
- Closed
-
-
-
- Closed
-
- causes
-
MDEV-18124 PK on inplace-enlarged type fails
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Confirmed
-
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-33069 Instant Modify Column
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue blocks |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| NRE Approved | Yes [ 10304 ] |
| 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 |
| 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? |
| Epic Link | PT-80 [ 68561 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Rank | Ranked higher |
| Target end | 12/Feb/19 [ 2019-02-12 ] |
| Assignee | Thirunarayanan Balathandayuthapani [ thiru ] | Eugene Kosov [ kevg ] |
| Link |
This issue relates to |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Link | This issue blocks MDEV-17520 [ MDEV-17520 ] |
| Assignee | Eugene Kosov [ kevg ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
The first part (NOT NULL to NULL) conversion looks mostly OK, except for one condition in the code. We must check the ROW_FORMAT from InnoDB dict_table_t, because it could be wrong in the .frm file.
| Assignee | Marko Mäkelä [ marko ] | Eugene Kosov [ kevg ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Eugene Kosov [ kevg ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
Analysis is in progress for part 2: https://github.com/tempesta-tech/mariadb/issues/555
The first part (NOT NULL removal) was moved to MDEV-17520.
The second part (instant failure-free changes of column type) will depend on MDEV-17520. Until MDEV-17520 is pushed to 10.4, the initial version of the first part can be used for developing the column type changes for ROW_FORMAT=REDUNDANT.
| Assignee | Marko Mäkelä [ marko ] | Eugene Kosov [ kevg ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Eugene Kosov [ kevg ] | Aleksey Midenok [ midenok ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| 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? |
| 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? |
| Link | This issue is blocked by MDEV-17520 [ MDEV-17520 ] |
| Link | This issue blocks MDEV-17520 [ MDEV-17520 ] |
| 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 {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 |
| Assignee | Aleksey Midenok [ midenok ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
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
|
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)
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue blocks |
| Link | This issue is blocked by MDEV-17520 [ MDEV-17520 ] |
| Link | This issue is blocked by MDEV-18326 [ MDEV-18326 ] |
| Attachment | UNSIGNED-bigger-signed.patch [ 47290 ] |
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.
| issue.field.resolutiondate | 2019-02-13 16:54:37.0 | 2019-02-13 16:54:37.819 |
| Fix Version/s | 10.4.3 [ 23230 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
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.
| Link |
This issue relates to |
| Link | This issue is blocked by MDEV-18326 [ MDEV-18326 ] |
| Link |
This issue causes |
| Link | This issue relates to MDEV-18780 [ MDEV-18780 ] |
| NRE Projects | NRE-310017 RM_104_NRE | NRE-310017 RM_104_NRE RM_102ES_CANDIDATE RM_103ES_CANDIDATE |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Workflow | MariaDB v3 [ 86022 ] | MariaDB v4 [ 133494 ] |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-33069 [ MDEV-33069 ] |
When it comes to avoiding rebuilding secondary indexes,
MDEV-16849might be useful.