mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 18:05:32 +01:00
d6d7e169fb
table_already_fk_prelocked() was looking for a table in the wrong list (not the complete list of prelocked tables, but only in its tail, starting from the current table - which is always empty for the last added table), so for circular FKs it kept adding same tables to the list indefinitely.
139 lines
3.7 KiB
Text
139 lines
3.7 KiB
Text
--source include/have_innodb.inc
|
|
|
|
--echo #
|
|
--echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
|
|
--echo # DICT_CREATE_FOREIGN_CONSTR
|
|
--echo #
|
|
|
|
create table t1 (f1 int primary key) engine=InnoDB;
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t2 (f1 int primary key,
|
|
constraint c1 foreign key (f1) references t1(f1),
|
|
constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB;
|
|
create table t2 (f1 int primary key,
|
|
constraint c1 foreign key (f1) references t1(f1)) engine=innodb;
|
|
|
|
--replace_regex /#sql-[0-9a-f_]*/#sql-temporary/
|
|
--error ER_CANT_CREATE_TABLE
|
|
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
|
|
|
|
set foreign_key_checks = 0;
|
|
--error ER_DUP_CONSTRAINT_NAME
|
|
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo #
|
|
--echo # Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN
|
|
--echo # NULL/NOT NULL MISMATCH
|
|
--echo #
|
|
|
|
set foreign_key_checks = 1;
|
|
show variables like 'foreign_key_checks';
|
|
|
|
CREATE TABLE t1
|
|
(a INT NOT NULL,
|
|
b INT NOT NULL,
|
|
INDEX idx(a)) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
|
|
ENGINE=InnoDB;
|
|
|
|
show create table t1;
|
|
show create table t2;
|
|
|
|
INSERT INTO t1 VALUES (1, 80);
|
|
INSERT INTO t1 VALUES (2, 81);
|
|
INSERT INTO t1 VALUES (3, 82);
|
|
INSERT INTO t1 VALUES (4, 83);
|
|
INSERT INTO t1 VALUES (5, 84);
|
|
|
|
INSERT INTO t2 VALUES (51, 1);
|
|
INSERT INTO t2 VALUES (52, 2);
|
|
INSERT INTO t2 VALUES (53, 3);
|
|
INSERT INTO t2 VALUES (54, 4);
|
|
INSERT INTO t2 VALUES (55, 5);
|
|
|
|
SELECT a, b FROM t1 ORDER BY a;
|
|
SELECT a, b FROM t2 ORDER BY a;
|
|
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
INSERT INTO t2 VALUES (56, 6);
|
|
|
|
ALTER TABLE t1 CHANGE a id INT;
|
|
|
|
SELECT id, b FROM t1 ORDER BY id;
|
|
SELECT a, b FROM t2 ORDER BY a;
|
|
|
|
--echo # Operations on child table
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
INSERT INTO t2 VALUES (56, 6);
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
UPDATE t2 SET b = 99 WHERE a = 51;
|
|
DELETE FROM t2 WHERE a = 53;
|
|
SELECT id, b FROM t1 ORDER BY id;
|
|
SELECT a, b FROM t2 ORDER BY a;
|
|
|
|
--echo # Operations on parent table
|
|
DELETE FROM t1 WHERE id = 1;
|
|
UPDATE t1 SET id = 50 WHERE id = 5;
|
|
SELECT id, b FROM t1 ORDER BY id;
|
|
SELECT a, b FROM t2 ORDER BY a;
|
|
|
|
DROP TABLE t2, t1;
|
|
|
|
--echo #
|
|
--echo # bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
|
|
--echo # base bug#24818604 [GR]
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1))
|
|
ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t2 VALUES (1);
|
|
|
|
SELECT unique_constraint_name FROM information_schema.referential_constraints
|
|
WHERE table_name = 't2';
|
|
|
|
--source include/restart_mysqld.inc
|
|
|
|
SELECT unique_constraint_name FROM information_schema.referential_constraints
|
|
WHERE table_name = 't2';
|
|
|
|
SELECT * FROM t1;
|
|
|
|
SELECT unique_constraint_name FROM information_schema.referential_constraints
|
|
WHERE table_name = 't2';
|
|
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# MDEV-12669 Circular foreign keys cause a loop and OOM upon LOCK TABLE
|
|
#
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
CREATE TABLE staff (
|
|
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
store_id TINYINT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (staff_id),
|
|
KEY idx_fk_store_id (store_id),
|
|
CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE store (
|
|
store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
manager_staff_id TINYINT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (store_id),
|
|
UNIQUE KEY idx_unique_manager (manager_staff_id),
|
|
CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
SET FOREIGN_KEY_CHECKS=DEFAULT;
|
|
|
|
LOCK TABLE staff WRITE;
|
|
UNLOCK TABLES;
|
|
DROP TABLES staff, store;
|