diff options
author | Nikita Malyavin <nikitamalyavin@gmail.com> | 2022-10-21 15:08:26 +0300 |
---|---|---|
committer | Nikita Malyavin <nikitamalyavin@gmail.com> | 2023-05-05 15:59:20 +0300 |
commit | f5b63e1df1eb1d1488dd8586fa3068b41bbe4640 (patch) | |
tree | 83d356c20bf0ebc8cff4f6f6290ad1be284c079c | |
parent | 25d93b82914fdcd06ae17c9b295dc33963a0a532 (diff) | |
download | mariadb-git-f5b63e1df1eb1d1488dd8586fa3068b41bbe4640.tar.gz |
MDEV-29068 Cascade foreign key updates do not apply in online alter
-rw-r--r-- | mysql-test/main/alter_table_online.result | 40 | ||||
-rw-r--r-- | mysql-test/main/alter_table_online.test | 51 | ||||
-rw-r--r-- | sql/sql_table.cc | 12 |
3 files changed, 103 insertions, 0 deletions
diff --git a/mysql-test/main/alter_table_online.result b/mysql-test/main/alter_table_online.result index a0850b0b8fc..e8c67c5fb39 100644 --- a/mysql-test/main/alter_table_online.result +++ b/mysql-test/main/alter_table_online.result @@ -80,3 +80,43 @@ XA END 'xid'; XA COMMIT 'xid' ONE PHASE; DROP TABLE t; disconnect con1; +connection default; +# +# MDEV-29068 Cascade foreign key updates do not apply in online alter +# +create table t1 (a int primary key) engine=InnoDB; +insert into t1 values (1),(2),(3); +create table t2 (b int, foreign key (b) +references t1 (a) +on update cascade) engine=InnoDB; +insert into t2 values (1),(2),(3); +alter table t2 add c int, algorithm=copy, lock=none; +ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED +alter table t2 add c int, algorithm=inplace, lock=none; +create or replace table t2 (b int, foreign key (b) +references t1 (a) +on delete set null) engine=InnoDB; +alter table t2 add c int, algorithm=copy, lock=none; +ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED +alter table t2 add c int, algorithm=inplace, lock=none; +create or replace table t2 (b int, foreign key (b) +references t1 (a) +on delete no action) engine=InnoDB; +insert into t2 values (1),(2),(3); +alter table t2 add c int, algorithm=copy, lock=none; +create or replace table t2 (b int, foreign key (b) +references t1 (a) +on update restrict) engine=InnoDB; +insert into t2 values (1),(2),(3); +alter table t2 add c int, algorithm=copy, lock=none; +drop table t2, t1; +create table t1 (a int primary key, b int unique) engine=InnoDB; +insert into t1 values (1, 1),(2, 2),(3, 3); +create table t2 (a int references t1 (a), +b int references t1 (b) on update cascade) engine=InnoDB; +insert into t2 values (1, 1),(2, 2); +alter table t2 add c int, algorithm=copy, lock=none; +ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED +alter table t2 add c int, algorithm=copy; +alter table t2 add d int, algorithm=inplace; +drop table t2, t1; diff --git a/mysql-test/main/alter_table_online.test b/mysql-test/main/alter_table_online.test index 224e506ef88..e2a4e085d26 100644 --- a/mysql-test/main/alter_table_online.test +++ b/mysql-test/main/alter_table_online.test @@ -64,3 +64,54 @@ XA COMMIT 'xid' ONE PHASE; DROP TABLE t; --disconnect con1 +--connection default + +--echo # +--echo # MDEV-29068 Cascade foreign key updates do not apply in online alter +--echo # +create table t1 (a int primary key) engine=InnoDB; +insert into t1 values (1),(2),(3); +create table t2 (b int, foreign key (b) + references t1 (a) + on update cascade) engine=InnoDB; +insert into t2 values (1),(2),(3); + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t2 add c int, algorithm=copy, lock=none; +alter table t2 add c int, algorithm=inplace, lock=none; + +create or replace table t2 (b int, foreign key (b) + references t1 (a) + on delete set null) engine=InnoDB; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t2 add c int, algorithm=copy, lock=none; +alter table t2 add c int, algorithm=inplace, lock=none; + +create or replace table t2 (b int, foreign key (b) + references t1 (a) + on delete no action) engine=InnoDB; + +insert into t2 values (1),(2),(3); +alter table t2 add c int, algorithm=copy, lock=none; + +create or replace table t2 (b int, foreign key (b) + references t1 (a) + on update restrict) engine=InnoDB; + +insert into t2 values (1),(2),(3); +alter table t2 add c int, algorithm=copy, lock=none; +drop table t2, t1; + +create table t1 (a int primary key, b int unique) engine=InnoDB; +insert into t1 values (1, 1),(2, 2),(3, 3); +create table t2 (a int references t1 (a), + b int references t1 (b) on update cascade) engine=InnoDB; +insert into t2 values (1, 1),(2, 2); + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t2 add c int, algorithm=copy, lock=none; +alter table t2 add c int, algorithm=copy; +alter table t2 add d int, algorithm=inplace; +# Cleanup +drop table t2, t1; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 0bbc0926061..ac5975a6809 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -10069,6 +10069,18 @@ bool mysql_alter_table(THD *thd, const LEX_CSTRING *new_db, online= online && !table->s->tmp_table; + List<FOREIGN_KEY_INFO> fk_list; + table->file->get_foreign_key_list(thd, &fk_list); + for (auto &fk: fk_list) + { + if (fk_modifies_child(fk.delete_method) + || fk_modifies_child(fk.update_method)) + { + online= false; + break; + } + } + #ifdef WITH_WSREP if (WSREP(thd) && (thd->lex->sql_command == SQLCOM_ALTER_TABLE || |