diff options
author | Aleksey Midenkov <midenok@gmail.com> | 2021-11-02 11:13:21 +0300 |
---|---|---|
committer | Aleksey Midenkov <midenok@gmail.com> | 2021-11-02 11:13:21 +0300 |
commit | 6fe3c59ee81c1523a358a7bdd9c411cd13f0526e (patch) | |
tree | d0239a7059ba481b6e2fbcaa88c0cc2ded485edd /mysql-test/suite | |
parent | 1203b65849cd1ecefe5f2df07f3165b9aa5e2a9d (diff) | |
download | mariadb-git-6fe3c59ee81c1523a358a7bdd9c411cd13f0526e.tar.gz |
MDEV-25004 Missing row in FTS_DOC_ID_INDEX during DELETE HISTORY
1. In case of system-versioned table add row_end into FTS_DOC_ID index
in fts_create_common_tables() and innobase_create_key_defs().
fts_n_uniq() returns 1 or 2 depending on whether the table is
system-versioned.
After this patch recreate of FTS_DOC_ID index is required for
existing system-versioned tables. If you see this message in error
log or server warnings: "InnoDB: Table db/t1 contains 2 indexes
inside InnoDB, which is different from the number of indexes 1
defined in the MariaDB" use this command to fix the table:
ALTER TABLE db.t1 FORCE;
2. Fix duplicate history for secondary unique index like it was done
in MDEV-23644 for clustered index (932ec586aad). In case of
existing history row which conflicts with currently inseted row we
check in row_ins_scan_sec_index_for_duplicate() whether that row
was inserted as part of current transaction. In that case we
indicate with DB_FOREIGN_DUPLICATE_KEY that new history row is not
needed and should be silently skipped.
3. Some parts of MDEV-21138 (7410ff436e9) reverted. Skipping of
FTS_DOC_ID index for history rows made problems with purge
system. Now this is fixed differently by p.2.
4. wait_all_purged.inc checks that we didn't affect non-history rows
so they are deleted and purged correctly.
Additional FTS fixes
fts_init_get_doc_id(): exclude history rows from max_doc_id
calculation. fts_init_get_doc_id() callback is used only for crash
recovery.
fts_add_doc_by_id(): set max value for row_end field.
fts_read_stopword(): stopwords table can be system-versioned too. We
now read stopwords only for current data.
row_insert_for_mysql(): exclude history rows from doc_id validation.
row_merge_read_clustered_index(): exclude history_rows from doc_id
processing.
Diffstat (limited to 'mysql-test/suite')
-rw-r--r-- | mysql-test/suite/versioning/r/delete.result | 1 | ||||
-rw-r--r-- | mysql-test/suite/versioning/r/delete_history.result | 13 | ||||
-rw-r--r-- | mysql-test/suite/versioning/r/foreign.result | 37 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/delete_history.test | 15 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/foreign.test | 33 |
5 files changed, 98 insertions, 1 deletions
diff --git a/mysql-test/suite/versioning/r/delete.result b/mysql-test/suite/versioning/r/delete.result index 0f9e2c22130..6f8c8921790 100644 --- a/mysql-test/suite/versioning/r/delete.result +++ b/mysql-test/suite/versioning/r/delete.result @@ -146,6 +146,5 @@ delete from t1; select f1, f3, check_row_ts(row_start, row_end) from t1 for system_time all; f1 f3 check_row_ts(row_start, row_end) 1 1 HISTORICAL ROW -1 NULL ERROR: row_end == row_start 1 1 HISTORICAL ROW drop table t1; diff --git a/mysql-test/suite/versioning/r/delete_history.result b/mysql-test/suite/versioning/r/delete_history.result index 95d7304c3dd..4065494f163 100644 --- a/mysql-test/suite/versioning/r/delete_history.result +++ b/mysql-test/suite/versioning/r/delete_history.result @@ -1,3 +1,5 @@ +set @saved_frequency= @@global.innodb_purge_rseg_truncate_frequency; +set global innodb_purge_rseg_truncate_frequency= 1; create table t (a int); delete history from t before system_time now(); ERROR HY000: Table `t` is not system-versioned @@ -162,3 +164,14 @@ x 1 drop prepare stmt; drop table t1; +# +# MDEV-25004 Missing row in FTS_DOC_ID_INDEX during DELETE HISTORY +# +create table t1 (a integer, c0 varchar(255), fulltext key (c0)) +with system versioning engine innodb; +insert into t1 values (1, 'politician'); +update t1 set c0= 'criminal'; +InnoDB 0 transactions not purged +delete history from t1; +drop table t1; +set global innodb_purge_rseg_truncate_frequency= @saved_frequency; diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index d157916c60c..3eb968f1a33 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -443,6 +443,43 @@ pk f1 f2 left(f3, 4) check_row_ts(row_start, row_end) 1 8 8 SHOR HISTORICAL ROW 2 8 8 LONG HISTORICAL ROW drop table t1; +# Shorter case for clustered index (MDEV-25004) +create table t1 ( +y int primary key, r int, f int, key (r), +foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +y r f check_row_ts(row_start, row_end) +1 6 6 HISTORICAL ROW +2 6 6 HISTORICAL ROW +drop tables t1; +# Secondary unique index +create table t1 ( +y int unique null, r int, f int, key (r), +foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +y r f check_row_ts(row_start, row_end) +1 6 6 HISTORICAL ROW +2 6 6 HISTORICAL ROW +drop tables t1; +# Non-unique index cannot be fixed because it does not trigger duplicate error +create table t1 ( +y int, r int, f int, key (y), key (r), +foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +y r f check_row_ts(row_start, row_end) +1 6 6 HISTORICAL ROW +2 6 NULL ERROR: row_end == row_start +2 6 6 HISTORICAL ROW +drop tables t1; # # MDEV-21555 Assertion secondary index is out of sync on delete from versioned table # diff --git a/mysql-test/suite/versioning/t/delete_history.test b/mysql-test/suite/versioning/t/delete_history.test index bd605c689ad..31c6038783f 100644 --- a/mysql-test/suite/versioning/t/delete_history.test +++ b/mysql-test/suite/versioning/t/delete_history.test @@ -2,6 +2,9 @@ --source include/have_partition.inc --source suite/versioning/engines.inc +set @saved_frequency= @@global.innodb_purge_rseg_truncate_frequency; +set global innodb_purge_rseg_truncate_frequency= 1; + create table t (a int); --error ER_VERS_NOT_VERSIONED delete history from t before system_time now(); @@ -163,4 +166,16 @@ select * from t1; drop prepare stmt; drop table t1; +--echo # +--echo # MDEV-25004 Missing row in FTS_DOC_ID_INDEX during DELETE HISTORY +--echo # +create table t1 (a integer, c0 varchar(255), fulltext key (c0)) +with system versioning engine innodb; +insert into t1 values (1, 'politician'); +update t1 set c0= 'criminal'; +--source suite/innodb/include/wait_all_purged.inc +delete history from t1; +drop table t1; + +set global innodb_purge_rseg_truncate_frequency= @saved_frequency; --source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test index 1c834719e0f..f4e4fa7a354 100644 --- a/mysql-test/suite/versioning/t/foreign.test +++ b/mysql-test/suite/versioning/t/foreign.test @@ -476,6 +476,39 @@ select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for sys # cleanup drop table t1; +--echo # Shorter case for clustered index (MDEV-25004) +create table t1 ( + y int primary key, r int, f int, key (r), + foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; + +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +drop tables t1; + +--echo # Secondary unique index +create table t1 ( + y int unique null, r int, f int, key (r), + foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; + +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +drop tables t1; + +--echo # Non-unique index cannot be fixed because it does not trigger duplicate error +create table t1 ( + y int, r int, f int, key (y), key (r), + foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; + +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +drop tables t1; + --echo # --echo # MDEV-21555 Assertion secondary index is out of sync on delete from versioned table --echo # |