Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL)
Description
When MySQL 5.7 introduced indexed virtual columns in InnoDB, it added many limitations around ALTER TABLE…ALGORITHM=INPLACE. One of them is that altering or adding virtual columns is not supported when the same ALTER TABLE statement is also adding, dropping or reordering stored columns. MariaDB 10.2 inherited these limitations in MDEV-5800 and related work.
In MDEV-15562 the right thing would be to allow any combination of ADD/DROP/reorder of virtual and stored columns. (Only the ADD of virtual persistent aka generated stored columns cannot possibly be instantaneous.)
I ran into trouble with the following in the test gcol.innodb_virtual_basic, around line 541:
CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)); |
|
INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT); |
INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT); |
INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT); |
|
alter table t add x int, add xx int generated ALWAYS AS(x); |
|
DROP TABLE t; |
The problem is that instant ADD of persistent columns (MDEV-11369) would modify the data dictionary cache already before commit, while adding virtual columns uses a different approach, deferring modifications of the table to the commit time.
In this case, we should be able to avoid a table rebuild, and instantly add both columns, then generate the values for the stored column.
Furthermore, there is the function check_v_col_in_order() whose sole purpose is to refuse operations that would change the order of virtual columns. This function should be removed and the code be fixed.
Last but not least, the function ha_innobase::commit_inplace_alter_table() is evicting and reloading the table definition if any virtual columns were added or dropped. This code does not appear to work for partitioned tables (or it is only operating on the first partition or subpartition of the table). It is causing an expensive operation of emptying the adaptive hash index. The purpose of the exercise seems to be to invoke dict_load_column_low(), which is the only place where num_base for virtual columns is assigned to nonzero. It would be better to update this field directly in the data dictionary cache.
Attachments
Issue Links
- is blocked by
-
MDEV-32668 tables with UNIQUE blob columns cannot be alter_algorithm=INSTANT modified
- Open
- relates to
-
MDEV-5800 indexes on virtual (not materialized) columns
- Closed
-
MDEV-11369 Instant add column for InnoDB
- Closed
-
MDEV-11424 Instant ALTER TABLE of failure-free record format changes
- Closed
-
MDEV-14046 Allow ALGORITHM=INPLACE for 10.1 tables that contain virtual columns
- Closed
-
MDEV-14341 Allow LOCK=NONE in table-rebuilding ALTER when indexed virtual columns exist
- Open
-
MDEV-15476 Inplace algorithm doesn't support changing virtual column datatype
- Stalled
-
MDEV-15562 Instant DROP COLUMN or changing the order of columns
- Closed
-
MDEV-16332 Allow ALGORITHM=NOCOPY or INSTANT for changes of virtual column type
- Confirmed
-
MDEV-17035 Support ALGORITHM=NOCOPY for CHANGE virtual column expression
- Open
-
MDEV-19214 Virtual column type cannot be converted from one to another - unhelpful error message
- Open
-
MDEV-22363 Reimplement the InnoDB virtual column code
- Open