Description:
When WL#8149 introduced support for indexed virtual columns, it failed to include sufficient information about the indexes or columns in the undo log records.
The problem occurs when virtual columns are dropped and added.
Before WL#8149, there was no problem, because all indexed columns were also explicitly represented in clustered index records, and ADD/DROP COLUMN would require a table rebuild, which would reassign the table_id (both in WL#6255 ALGORITHM=INPLACE, and in the old ALGORITHM=COPY case). A table-rebuilding ALTER would essentially discard all old undo log records for the table by assigning a new table_id.
How to repeat:
--source include/have_innodb.inc
--source include/count_sessions.inc
CREATE TABLE t1 (a INT, b INT,
a1 INT GENERATED ALWAYS AS (a) VIRTUAL, INDEX(a1)
) ENGINE=InnoDB;
INSERT INTO t1 (a,b) VALUES(1,1);
connect (con1,localhost,root,,);
# disable purge
CREATE TABLE t0 (a INT) ENGINE=InnoDB;
BEGIN; SELECT * FROM t0;
connection default;
# write the problematic update_undo log record
UPDATE t1 SET a=0;
# side note: this does not work in a single operation (arbitrary limitation)
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 DROP COLUMN a1,
ADD COLUMN b1 INT GENERATED ALWAYS AS (b) VIRTUAL, ADD INDEX(b1),
ALGORITHM=INPLACE;
# so, let us retry in 2 operations:
ALTER TABLE t1 DROP COLUMN a1, ALGORITHM=INPLACE;
ALTER TABLE t1 ADD COLUMN b1 INT GENERATED ALWAYS AS (b) VIRTUAL, ADD INDEX(b1),
ALGORITHM=INPLACE;
connection con1;
# enable purge
COMMIT;
disconnect con1;
connection default;
# wait for purge to process the update_undo record. CRASH!
--source include/wait_innodb_all_purged.inc
CHECK TABLE t1;
SELECT b1 FROM t1 FORCE INDEX b1;
SELECT b1 FROM t1;
DROP TABLE t1, t0;
--source include/wait_until_count_sessions.inc
In this case, the purge thread will fail as follows:
2015-09-20T07:21:33.248358Z 0 [ERROR] InnoDB: tried to purge non-delete-marked record in index `b1` of table `test`.`t1`: tuple: TUPLE (info_bits=0, 2 fields): {[4] (0x00000001),[6] (0x000000000200)}, record: COMPACT RECORD(info_bits=0, 2 fields): {[4] (0x00000001),[6] (0x000000000200)}
2015-09-20 10:21:33 0x7f5a28e19700 InnoDB: Assertion failure in thread 140025209657088 in file row0purge.cc line 498
The above assertion failure is only present in debug builds. Stack trace:
row_purge_remove_sec_if_poss_leaf
row_purge_remove_sec_if_poss
row_purge_upd_exist_or_extern_func
row_purge_record_func
row_purge
...
srv_worker_thread
Suggested fix:
Two main possibilities:
(1) Change the undo log format of indexed virtual columns.
For example, include the index_id_t in the update_undo log records, and check for index_id match
(2) Keep the existing undo log format, but add some prevention logic.
(2a) Prevent ADD INDEX if any indexed virtual column has ever been dropped in the past.
Cumbersome to the user, and would probably require a change to the data dictionary format.
(2b) Wait for the purge for the table to be completed before allowing the ADD INDEX to complete on a virtual column.
Seems impractical; could significantly increasing the probability of failed operations (timeouts).
