diff options
author | Nikita Malyavin <nikitamalyavin@gmail.com> | 2023-01-11 18:57:44 +0300 |
---|---|---|
committer | Nikita Malyavin <nikitamalyavin@gmail.com> | 2023-01-12 21:51:48 +0300 |
commit | 7a98d232e42b66efc759d584b05214e91681c346 (patch) | |
tree | ae182cc4f354717c04f6e68faaefff85eb61f721 | |
parent | eb145e5ad7afa29f1d298452b80fcca36a6c3bbe (diff) | |
download | mariadb-git-7a98d232e42b66efc759d584b05214e91681c346.tar.gz |
MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT constraint
node->is_delete was incorrectly set to NO_DELETE for a set of operations.
In general we shouldn't rely on sql_command and look for more abstract ways
to control the behavior.
trg_event_map seems to be a suitable way. To mind replica nodes, it is ORed
with slave_fk_event_map, which stores trg_event_map when replica has
triggers disabled.
-rw-r--r-- | mysql-test/suite/versioning/r/foreign.result | 39 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/foreign.test | 46 | ||||
-rw-r--r-- | sql/sql_insert.cc | 1 | ||||
-rw-r--r-- | storage/innobase/handler/ha_innodb.cc | 4 |
4 files changed, 89 insertions, 1 deletions
diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index 3eb968f1a33..9b5946e5262 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -493,3 +493,42 @@ set foreign_key_checks= on; delete history from t1; delete from t1; drop table t1; +# +# MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT +# constraint +# +create table t0 (pk integer primary key) with system versioning engine=innodb; +create table t1 (pk integer primary key, +foreign key(pk) references t0(pk) +on delete restrict on update cascade) engine=innodb; +create table t2 (pk integer); +insert into t0 (pk) values (1); +insert into t1 (pk) values (1); +insert into t2 (pk) values (1); +delete from t0; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +replace t0 values (1); +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +select * into outfile 'load_t0' from t0 ; +load data infile 'load_t0' replace into table t0; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +delete t0, t2 from t0 join t2; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +select pk from t0; +pk +1 +# Cleanup +drop table t1, t0, t2; +# create_select for a temporary table didn't set up pos_in_locked_tables. +create table t (a int unique) engine=innodb +replace select 1 as a, 2 as b union select 1 as a, 3 as c; +select * from t; +a b +1 3 +drop table t; +create temporary table t (a int unique) engine=innodb +replace select 1 as a, 2 as b union select 1 as a, 3 as c; +select * from t; +a b +1 3 +drop table t; diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test index f4e4fa7a354..85b0c3e92e4 100644 --- a/mysql-test/suite/versioning/t/foreign.test +++ b/mysql-test/suite/versioning/t/foreign.test @@ -527,4 +527,50 @@ delete from t1; # cleanup drop table t1; +--echo # +--echo # MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT +--echo # constraint +--echo # +create table t0 (pk integer primary key) with system versioning engine=innodb; +create table t1 (pk integer primary key, + foreign key(pk) references t0(pk) + on delete restrict on update cascade) engine=innodb; +create table t2 (pk integer); + +insert into t0 (pk) values (1); +insert into t1 (pk) values (1); +insert into t2 (pk) values (1); + +--error ER_ROW_IS_REFERENCED_2 +delete from t0; + +--error ER_ROW_IS_REFERENCED_2 +replace t0 values (1); + +select * into outfile 'load_t0' from t0 ; +--error ER_ROW_IS_REFERENCED_2 +load data infile 'load_t0' replace into table t0; + +--error ER_ROW_IS_REFERENCED_2 +delete t0, t2 from t0 join t2; + +select pk from t0; + +--echo # Cleanup +drop table t1, t0, t2; +--let $datadir= `select @@datadir` +--remove_file $datadir/test/load_t0 + + +--echo # create_select for a temporary table didn't set up pos_in_locked_tables. +create table t (a int unique) engine=innodb + replace select 1 as a, 2 as b union select 1 as a, 3 as c; +select * from t; +drop table t; + +create temporary table t (a int unique) engine=innodb + replace select 1 as a, 2 as b union select 1 as a, 3 as c; +select * from t; +drop table t; + --source suite/versioning/common_finish.inc diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index b743fc88061..0fd18c3e597 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -4396,6 +4396,7 @@ TABLE *select_create::create_table_from_items(THD *thd, */ DBUG_ASSERT(0); } + create_table->table->pos_in_table_list= create_table; } } else diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 58b2068b56f..440c6cf3f64 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -8957,10 +8957,12 @@ ha_innobase::update_row( const bool vers_ins_row = vers_set_fields && thd_sql_command(m_user_thd) != SQLCOM_ALTER_TABLE; + TABLE_LIST *tl= table->pos_in_table_list; + uint8 op_map= tl->trg_event_map | tl->slave_fk_event_map; /* This is not a delete */ m_prebuilt->upd_node->is_delete = (vers_set_fields && !vers_ins_row) || - (thd_sql_command(m_user_thd) == SQLCOM_DELETE && + (op_map & trg2bit(TRG_EVENT_DELETE) && table->versioned(VERS_TIMESTAMP)) ? VERSIONED_DELETE : NO_DELETE; |