diff options
author | Aleksey Midenkov <midenok@gmail.com> | 2023-01-18 16:52:12 +0300 |
---|---|---|
committer | Aleksey Midenkov <midenok@gmail.com> | 2023-01-26 17:15:21 +0300 |
commit | 5a7871b59924a9171a4c5e5ce48de7539bacd29c (patch) | |
tree | c01ec49d6ddd836d7c2791802996e0cd92231e5a | |
parent | 86d25a76a3a0ee0f10a5314efe2c1633d9a4fb46 (diff) | |
download | mariadb-git-5a7871b59924a9171a4c5e5ce48de7539bacd29c.tar.gz |
MDEV-29701 Inconsistencies and eventual failure upon CREATE OR REPLACE with foreign keys
finalize_atomic_replace(): when creating new table it must be checked
that it is valid reference for any existing foreign keys even with
FOREIGN_KEY_CHECKS=OFF. That is the original behavior.
RENAME_CONSTRAINT_IDS: the algorithm was broken when we CREATE OR
REPLACE referenced table. REF_NAME was renamed to backup name and the
new table did't see any referenced keys. We must not change REF_NAME
to backup name in that case. The owner of that foreign key is some
different table and its constraints must be intact. But that doesn't
apply to self-references. The owner of self-reference is the table
being replaced, so we must get them away before the new table is
checked.
-rw-r--r-- | mysql-test/suite/innodb/r/foreign_key.result | 63 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/foreign_key.test | 36 | ||||
-rw-r--r-- | sql/sql_table.cc | 12 | ||||
-rw-r--r-- | storage/innobase/include/sql_funcs.h | 16 |
4 files changed, 118 insertions, 9 deletions
diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result index 0b85048b650..5489fdd94c9 100644 --- a/mysql-test/suite/innodb/r/foreign_key.result +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -1,3 +1,4 @@ +call mtr.add_suppression("foreign key constraints"); # # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW # DICT_CREATE_FOREIGN_CONSTR @@ -732,7 +733,6 @@ SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; # MDEV-17187 table doesn't exist in engine after ALTER other tables # with CONSTRAINTs # -call mtr.add_suppression("\\[Warning\\] InnoDB: In ALTER TABLE `test`\\.`t2` has or is referenced in foreign key constraints which are not compatible with the new table definition."); set foreign_key_checks=on; create table t1 (id int not null primary key) engine=innodb; create table t2 (id int not null primary key, fid int not null, @@ -1028,3 +1028,64 @@ disconnect con1; connection default; DROP TABLE IF EXISTS t2, t1; # End of 10.6 tests +# +# MDEV-29701 Inconsistencies and eventual failure upon CREATE OR REPLACE with foreign keys +# +create table t2 (x int primary key) engine=innodb; +create table t1 (a int references t2 (x), b int references t1 (a)) engine=innodb; +set session foreign_key_checks = off; +create or replace table t2 (x int, y int) engine=innodb; +ERROR HY000: Can't create table `test`.`./test/t2` (errno: 150 "Foreign key constraint is incorrectly formed") +select * from information_schema.innodb_sys_foreign; +ID FOR_NAME REF_NAME N_COLS TYPE +test/t1_ibfk_1 test/t1 test/t2 1 0 +test/t1_ibfk_2 test/t1 test/t1 1 0 +create or replace table t2 (x int primary key, new int) engine=innodb; +select * from information_schema.innodb_sys_foreign; +ID FOR_NAME REF_NAME N_COLS TYPE +test/t1_ibfk_1 test/t1 test/t2 1 0 +test/t1_ibfk_2 test/t1 test/t1 1 0 +create or replace table t2 (x int, z int) engine=innodb; +ERROR HY000: Can't create table `test`.`./test/t2` (errno: 150 "Foreign key constraint is incorrectly formed") +select * from information_schema.innodb_sys_foreign; +ID FOR_NAME REF_NAME N_COLS TYPE +test/t1_ibfk_1 test/t1 test/t2 1 0 +test/t1_ibfk_2 test/t1 test/t1 1 0 +set session foreign_key_checks= on; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `x` int(11) NOT NULL, + `new` int(11) DEFAULT NULL, + PRIMARY KEY (`x`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + KEY `a` (`a`), + KEY `b` (`b`), + CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`x`), + CONSTRAINT `t1_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +create or replace table t1 (a int references t1 (a)) engine=innodb; +ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +select * from information_schema.innodb_sys_foreign; +ID FOR_NAME REF_NAME N_COLS TYPE +test/t1_ibfk_1 test/t1 test/t2 1 0 +test/t1_ibfk_2 test/t1 test/t1 1 0 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + KEY `a` (`a`), + KEY `b` (`b`), + CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`x`), + CONSTRAINT `t1_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +create or replace table t1 (a int) engine=innodb; +select * from information_schema.innodb_sys_foreign; +ID FOR_NAME REF_NAME N_COLS TYPE +drop tables if exists t1, t2; diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test index 918d8e8df20..4d8eaf88c13 100644 --- a/mysql-test/suite/innodb/t/foreign_key.test +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -2,6 +2,8 @@ --source include/count_sessions.inc --source include/default_charset.inc +call mtr.add_suppression("foreign key constraints"); + --echo # --echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW --echo # DICT_CREATE_FOREIGN_CONSTR @@ -183,7 +185,6 @@ SET FOREIGN_KEY_CHECKS=1; --disable_query_log call mtr.add_suppression("InnoDB: Table rename might cause two FOREIGN KEY"); -call mtr.add_suppression("InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15\\."); --enable_query_log CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; @@ -739,8 +740,6 @@ SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; --echo # with CONSTRAINTs --echo # -call mtr.add_suppression("\\[Warning\\] InnoDB: In ALTER TABLE `test`\\.`t2` has or is referenced in foreign key constraints which are not compatible with the new table definition."); - set foreign_key_checks=on; create table t1 (id int not null primary key) engine=innodb; create table t2 (id int not null primary key, fid int not null, @@ -1072,4 +1071,35 @@ DROP TABLE IF EXISTS t2, t1; --echo # End of 10.6 tests +--echo # +--echo # MDEV-29701 Inconsistencies and eventual failure upon CREATE OR REPLACE with foreign keys +--echo # +create table t2 (x int primary key) engine=innodb; +create table t1 (a int references t2 (x), b int references t1 (a)) engine=innodb; + +set session foreign_key_checks = off; +--error ER_CANT_CREATE_TABLE +create or replace table t2 (x int, y int) engine=innodb; +select * from information_schema.innodb_sys_foreign; +create or replace table t2 (x int primary key, new int) engine=innodb; +select * from information_schema.innodb_sys_foreign; +--error ER_CANT_CREATE_TABLE +create or replace table t2 (x int, z int) engine=innodb; +select * from information_schema.innodb_sys_foreign; +set session foreign_key_checks= on; + +show create table t2; +show create table t1; + +--error ER_CANT_CREATE_TABLE +create or replace table t1 (a int references t1 (a)) engine=innodb; +select * from information_schema.innodb_sys_foreign; +show create table t1; + +create or replace table t1 (a int) engine=innodb; +select * from information_schema.innodb_sys_foreign; + +drop tables if exists t1, t2; + + --source include/wait_until_count_sessions.inc diff --git a/sql/sql_table.cc b/sql/sql_table.cc index b25f0a541fb..4502ed3d20b 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4486,15 +4486,19 @@ bool HA_CREATE_INFO::finalize_atomic_replace(THD *thd, TABLE_LIST *orig_table) param.old_version= tabledef_version; param.old_alias= tmp_name.table_name; param.new_alias= table_name; + ulonglong option_bits_save= thd->variables.option_bits; + thd->variables.option_bits&= ~OPTION_NO_FOREIGN_KEY_CHECKS; if (ddl_log_create_table(ddl_log_state_create, param.from_table_hton, &cpath, &db, &table_name, false) || rename_table_and_triggers(thd, ¶m, NULL, &tmp_name, &db, false, &dummy)) { + thd->variables.option_bits= option_bits_save; if (locked_tables_decremented) thd->locked_tables_list.add_back_last_deleted_lock(pos_in_locked_tables); return true; } + thd->variables.option_bits= option_bits_save; debug_crash_here("ddl_log_create_after_install_new"); return false; } @@ -5585,7 +5589,13 @@ mysql_rename_table(handlerton *base, const LEX_CSTRING *old_db, else if (error == ENOTDIR) my_error(ER_BAD_DB_ERROR, MYF(0), new_db->str); else if (error) - my_error(ER_ERROR_ON_RENAME, MYF(0), from, to, error); + { + if (thd->lex->sql_command == SQLCOM_CREATE_TABLE && + flags & FN_FROM_IS_TMP) + my_error(ER_CANT_CREATE_TABLE, MYF(0), new_db->str, to, error); + else + my_error(ER_ERROR_ON_RENAME, MYF(0), from, to, error); + } else if (!(flags & FN_IS_TMP)) mysql_audit_rename_table(thd, old_db, old_name, new_db, new_name); diff --git a/storage/innobase/include/sql_funcs.h b/storage/innobase/include/sql_funcs.h index 1506a2a5a48..2c3eda1abf3 100644 --- a/storage/innobase/include/sql_funcs.h +++ b/storage/innobase/include/sql_funcs.h @@ -104,8 +104,16 @@ R"===(PROCEDURE RENAME_CONSTRAINT_IDS () IS END IF; END IF; END LOOP; - UPDATE SYS_FOREIGN SET REF_NAME = :new_table_name - WHERE REF_NAME = :old_table_name - AND TO_BINARY(REF_NAME) - = TO_BINARY(:old_table_name); + IF (:new_is_tmp = 0) + THEN + UPDATE SYS_FOREIGN SET REF_NAME = :new_table_name + WHERE REF_NAME = :old_table_name + AND TO_BINARY(REF_NAME) = TO_BINARY(:old_table_name); + ELSE + UPDATE SYS_FOREIGN SET REF_NAME = :new_table_name + WHERE REF_NAME = :old_table_name + AND TO_BINARY(REF_NAME) = TO_BINARY(:old_table_name) + AND FOR_NAME = :new_table_name + AND TO_BINARY(FOR_NAME) = TO_BINARY(:new_table_name); + END IF; END;)==="; |