diff options
| -rw-r--r-- | mysql-test/main/alter_table_online.result | 37 | ||||
| -rw-r--r-- | mysql-test/main/alter_table_online.test | 48 | ||||
| -rw-r--r-- | sql/sql_table.cc | 12 |
3 files changed, 97 insertions, 0 deletions
diff --git a/mysql-test/main/alter_table_online.result b/mysql-test/main/alter_table_online.result index a0850b0b8fc..8b43281be7d 100644 --- a/mysql-test/main/alter_table_online.result +++ b/mysql-test/main/alter_table_online.result @@ -80,3 +80,40 @@ 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 +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 +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; +drop table t2, t1; diff --git a/mysql-test/main/alter_table_online.test b/mysql-test/main/alter_table_online.test index 03cd78084fd..1561000c258 100644 --- a/mysql-test/main/alter_table_online.test +++ b/mysql-test/main/alter_table_online.test @@ -63,3 +63,51 @@ 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; + +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; + +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; +# Cleanup +drop table t2, t1; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 8401a905f95..883a904c449 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -10477,6 +10477,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 || |