mirror of
https://github.com/MariaDB/server.git
synced 2025-12-18 10:15:43 +01:00
a518e1dd42
1. Access foreign keys via TABLE_SHARE::foreign_keys and
TABLE_SHARE::referenced_keys;
foreign_keys and referenced_keys are lists in TABLE_SHARE.
2. Remove handler FK interface:
- get_foreign_key_list()
- get_parent_foreign_key_list()
- referenced_by_foreign_key()
3. Invalidate referenced shares on:
- RENAME TABLE
- DROP TABLE
- RENAME COLUMN
- ADD FOREIGN KEY
When foreign table is created or altered by the above operations
all referenced shares are closed. This blocks the operation while
any referenced shares are used (when at least one its TABLE
instance is locked).
4. Update referenced shares on:
- CREATE TABLE
On CREATE TABLE add items to referenced_keys of referenced
shares. States of referenced shares are restored in case of errors.
5. Invalidate foreign shares on:
- RENAME TABLE
- RENAME COLUMN
The above-mentioned blocking takes effect.
6. Check foreign/referenced shares consistency on:
- CHECK TABLE
7. Temporary change until MDEV-21051:
InnoDB fill foreign key info at handler open().
FOREIGN_KEY_INFO is refactored to FK_info holding Lex_cstring.
On first TABLE open FK_info is loaded from storage engine into
TABLE_SHARE. All referenced shares (if any exists) are closed. This
leads to blocking of first time foreign table open while referenced
tables are used.
MDEV-21311 Converge Foreign_key and supplemental generated Key together
mysql_prepare_create_table() does data validation and such utilities
as automatic name generation. But it does that only for indexes and
ignores Foreign_key objects. Now as Foreign_key data needs to be
stored in FRM files as well this processing must be done for it like
for any other Key objects.
Replace Key::FOREIGN_KEY type with Key::foreign flag of type
Key::MULTIPLE and Key::generated set to true. Construct one object
with Key::foreign == true instead of two objects of type
Key::FOREIGN_KEY and Key::MULTIPLE.
MDEV-21051 datadict refactorings
- Move read_extra2() to datadict.cc
- Refactored extra2_fields to Extra2_info
- build_frm_image() readability
MDEV-21051 build_table_shadow_filename() refactoring
mysql_prepare_alter_table() leaks fixes
MDEV-21051 amend system tables locking restriction
Table mysql.help_relation has foreign key to mysql.help_keyword. On
bootstrap when help_relation is opened, it preopens help_keyword for
READ and fails in lock_tables_check().
If system table is opened for write then fk references are opened for
write.
Related to: Bug#25422, WL#3984
Tests: main.lock
MDEV-21051 Store and read foreign key info into/from FRM files
1. Introduce Foreign_key_io class which creates/parses binary stream
containing foreign key structures. Referenced tables store there only
hints about foreign tables (their db and name), they restore full info
from the corresponding tables.
Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY_INFO field in
extra2 section of FRM file.
2. Modify mysql_prepare_create_table() to generate names for foreign
keys. Until InnoDB storage of foreign keys is removed, FK names must
be unique across the database: the FK name must be based on table
name.
3. Keep stored data in sync on DDL changes. Referenced tables update
their foreign hints after following operations on foreign tables:
- RENAME TABLE
- DROP TABLE
- CREATE TABLE
- ADD FOREIGN KEY
- DROP FOREIGN KEY
Foreign tables update their foreign info after following operations on
referenced tables:
- RENAME TABLE
- RENAME COLUMN
4. To achieve 3. there must be ability to rewrite extra2 section of
FRM file without full reparse. FRM binary is built from primary
structures like HA_CREATE_INFO and cannot be built from TABLE_SHARE.
Use shadow write and rename like fast_alter_partition_table()
does. Shadow FRM is new FRM file that replaces the old one.
CREATE TABLE workflow:
1. Foreign_key is constructed in parser, placed into
alter_info->key_list;
2. mysql_prepare_create_table() translates them to FK_info, assigns
foreign_id if needed;
3. build_frm_image() writes two FK_info lists into FRM's extra2
section, for referenced keys it stores only table names (hints);
4. init_from_binary_frm_image() parses extra2 section and fills
foreign_keys and referenced_keys of TABLE_SHARE.
It restores referenced_keys by reading hint list of table names,
opening corresponding shares and restoring FK_info from their
foreign_keys. Hints resolution is done only when initializing
non-temporary shares. Usually temporary share has different
(temporary) name and it is impossible to resolve foreign keys by
that name (as we identify them by both foreign and referenced
table names). Another not unimportant reason is performance: this
saves spare share acquisitions.
ALTER TABLE workflow:
1. Foreign_key is constructed in parser, placed into
alter_info->key_list;
2. mysql_prepare_alter_table() prepares action lists and share list
of foreigns/references;
3. mysql_prepare_alter_table() locks list of foreigns/references by
MDL_INTENTION_EXCLUSIVE, acquires shares;
4. prepare_create_table() converts key_list into FK_list, assigns
foreign_id;
5. shadow FRM of altered table is created;
6. data is copied;
7. altered table is locked by MDL_EXCLUSIVE;
8. fk_handle_alter() processes action lists, creates FK backups,
modifies shares, writes shadow FRMs;
9. altered table is closed;
10. shadow FRMs are installed;
11. altered table is renamed, FRM backup deleted;
12. (TBD in MDEV-21053) shadow FRMs installation log closed, backups
deleted;
On FK backup system:
In case of failed DDL operation all shares that was modified must be
restored into original state. This is done by FK_ddl_backup (CREATE,
DROP), FK_rename_backup (RENAME), FK_alter_backup (ALTER).
On STL usage:
STL is used for utility not performance-critical algorithms, core
structures hold native List. A wrapper was made to convert STL
exception into bool error status or NULL value.
MDEV-20865 fk_check_consistency() in CHECK TABLE
Self-refs fix
Test table_flags fix: "debug" deviation is now gone.
FIXMEs: +16 -1
1252 lines
43 KiB
Text
1252 lines
43 KiB
Text
SET GLOBAL innodb_stats_persistent = 0;
|
|
#
|
|
# Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
|
|
# DICT_CREATE_FOREIGN_CONSTR
|
|
#
|
|
create table t1 (f1 int primary key) engine=InnoDB;
|
|
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;
|
|
ERROR HY000: Duplicate FOREIGN KEY constraint name 'c1'
|
|
create table t2 (f1 int primary key,
|
|
constraint c1 foreign key (f1) references t1(f1)) engine=innodb;
|
|
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
|
|
ERROR HY000: Duplicate FOREIGN KEY constraint name 'c1'
|
|
set statement foreign_key_checks = 0 for
|
|
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
|
|
ERROR HY000: Duplicate FOREIGN KEY constraint name 'c1'
|
|
drop table t2, t1;
|
|
#
|
|
# Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN
|
|
# NULL/NOT NULL MISMATCH
|
|
#
|
|
set foreign_key_checks = 1;
|
|
show variables like 'foreign_key_checks';
|
|
Variable_name Value
|
|
foreign_key_checks ON
|
|
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;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
KEY `idx` (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `ind` (`b`),
|
|
CONSTRAINT `1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
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;
|
|
a b
|
|
1 80
|
|
2 81
|
|
3 82
|
|
4 83
|
|
5 84
|
|
SELECT a, b FROM t2 ORDER BY a;
|
|
a b
|
|
51 1
|
|
52 2
|
|
53 3
|
|
54 4
|
|
55 5
|
|
INSERT INTO t2 VALUES (56, 6);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE)
|
|
ALTER TABLE t1 CHANGE a id INT;
|
|
SELECT id, b FROM t1 ORDER BY id;
|
|
id b
|
|
1 80
|
|
2 81
|
|
3 82
|
|
4 83
|
|
5 84
|
|
SELECT a, b FROM t2 ORDER BY a;
|
|
a b
|
|
51 1
|
|
52 2
|
|
53 3
|
|
54 4
|
|
55 5
|
|
# Operations on child table
|
|
INSERT INTO t2 VALUES (56, 6);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
|
|
UPDATE t2 SET b = 99 WHERE a = 51;
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
|
|
DELETE FROM t2 WHERE a = 53;
|
|
SELECT id, b FROM t1 ORDER BY id;
|
|
id b
|
|
1 80
|
|
2 81
|
|
3 82
|
|
4 83
|
|
5 84
|
|
SELECT a, b FROM t2 ORDER BY a;
|
|
a b
|
|
51 1
|
|
52 2
|
|
54 4
|
|
55 5
|
|
# 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;
|
|
id b
|
|
2 81
|
|
3 82
|
|
4 83
|
|
50 84
|
|
SELECT a, b FROM t2 ORDER BY a;
|
|
a b
|
|
52 2
|
|
54 4
|
|
55 50
|
|
DROP TABLE t2, t1;
|
|
#
|
|
# bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
|
|
# base bug#24818604 [GR]
|
|
#
|
|
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';
|
|
unique_constraint_name
|
|
PRIMARY
|
|
#
|
|
# MDEV-28317 Assertion failure on rollback of FOREIGN KEY operation
|
|
#
|
|
SET foreign_key_checks=0;
|
|
CREATE TABLE parent(a INT PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE child(a INT,FOREIGN KEY(a) REFERENCES parent(a) ON DELETE CASCADE)
|
|
ENGINE=InnoDB;
|
|
INSERT INTO child VALUES(1);
|
|
ALTER TABLE child DROP INDEX a;
|
|
connect incomplete, localhost, root,,;
|
|
BEGIN;
|
|
DELETE FROM child;
|
|
connection default;
|
|
INSERT INTO parent SET a=0;
|
|
FLUSH TABLES;
|
|
# restart
|
|
disconnect incomplete;
|
|
SET GLOBAL innodb_stats_persistent = 0;
|
|
INSERT INTO child SET a=0;
|
|
INSERT INTO child SET a=1;
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE CASCADE)
|
|
DELETE FROM parent;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE CASCADE)
|
|
ALTER TABLE child ADD INDEX(a);
|
|
DELETE FROM parent;
|
|
DROP TABLE child,parent;
|
|
SELECT unique_constraint_name FROM information_schema.referential_constraints
|
|
WHERE table_name = 't2';
|
|
unique_constraint_name
|
|
PRIMARY
|
|
SELECT * FROM t1;
|
|
c1
|
|
1
|
|
SELECT unique_constraint_name FROM information_schema.referential_constraints
|
|
WHERE table_name = 't2';
|
|
unique_constraint_name
|
|
PRIMARY
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
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;
|
|
LOCK TABLE staff WRITE;
|
|
UNLOCK TABLES;
|
|
DROP TABLES staff, store;
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
#
|
|
# MDEV-17531 Crash in RENAME TABLE with FOREIGN KEY and FULLTEXT INDEX
|
|
#
|
|
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE DATABASE best default character set latin1;
|
|
CREATE TABLE t3 (a INT PRIMARY KEY,
|
|
CONSTRAINT t2_ibfk_1 FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB;
|
|
CREATE TABLE best.t2 (a INT PRIMARY KEY, b TEXT, FULLTEXT INDEX(b),
|
|
FOREIGN KEY (a) REFERENCES test.t1(a)) ENGINE=InnoDB;
|
|
RENAME TABLE best.t2 TO test.t2;
|
|
SHOW CREATE TABLE test.t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` text DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
FULLTEXT KEY `b` (`b`),
|
|
CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
RENAME TABLE test.t2 TO best.t2;
|
|
SHOW CREATE TABLE best.t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` text DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
FULLTEXT KEY `b` (`b`),
|
|
CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `test`.`t1` (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
DROP DATABASE best;
|
|
#
|
|
# MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs
|
|
#
|
|
connect con1, localhost, root,,;
|
|
INSERT INTO t1 SET a=1;
|
|
BEGIN;
|
|
DELETE FROM t1;
|
|
connection default;
|
|
INSERT INTO t3 SET a=1;
|
|
connection con1;
|
|
kill query @id;
|
|
connection default;
|
|
ERROR 70100: Query execution was interrupted
|
|
connection con1;
|
|
ROLLBACK;
|
|
connection default;
|
|
disconnect con1;
|
|
DROP TABLE t3,t1;
|
|
#
|
|
# MDEV-18222 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N
|
|
# or ASAN heap-use-after-free in dict_foreign_remove_from_cache upon CHANGE COLUMN
|
|
#
|
|
CREATE TABLE t1 (a INT, UNIQUE(a), KEY(a)) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (a);
|
|
SET SESSION FOREIGN_KEY_CHECKS = OFF;
|
|
ALTER TABLE t1 CHANGE COLUMN a a TIME NOT NULL;
|
|
ERROR HY000: Cannot change column 'a': used in a foreign key constraint '1'
|
|
ALTER TABLE t1 ADD pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
|
|
ALTER TABLE t1 CHANGE COLUMN a b TIME;
|
|
ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint '1'
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`pk` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`pk`),
|
|
UNIQUE KEY `a` (`a`),
|
|
KEY `a_2` (`a`),
|
|
CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 CHANGE COLUMN a b TIME, DROP FOREIGN KEY `1`;
|
|
SET SESSION FOREIGN_KEY_CHECKS = ON;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-18256 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N
|
|
# upon DROP FOREIGN KEY
|
|
#
|
|
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY fk1 (b) REFERENCES t1 (a))
|
|
ENGINE=InnoDB;
|
|
ALTER TABLE t2 DROP FOREIGN KEY fk1, DROP FOREIGN KEY fk1;
|
|
DROP TABLE t2, t1;
|
|
CREATE TABLE t1 (f VARCHAR(256)) ENGINE=InnoDB;
|
|
SET SESSION FOREIGN_KEY_CHECKS = OFF;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x);
|
|
ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
|
|
ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f);
|
|
Warnings:
|
|
Note 1831 Duplicate index `ft2`. This is deprecated and will be disallowed in a future release
|
|
SET SESSION FOREIGN_KEY_CHECKS = ON;
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (f VARCHAR(256), FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY)
|
|
ENGINE=InnoDB;
|
|
SET SESSION FOREIGN_KEY_CHECKS = OFF;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x);
|
|
ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
|
|
ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f);
|
|
Warnings:
|
|
Note 1831 Duplicate index `ft2`. This is deprecated and will be disallowed in a future release
|
|
SET SESSION FOREIGN_KEY_CHECKS = ON;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-18630 Conditional jump or move depends on uninitialised value
|
|
# in ib_push_warning / dict_create_foreign_constraints_low
|
|
#
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b);
|
|
ERROR 42S02: Table 'test.t2' doesn't exist
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error
|
|
#
|
|
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f1)) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (f INT, KEY(f)) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t2 (f);
|
|
ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1);
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1 (a INT, b INT, KEY idx(a)) ENGINE=InnoDB;
|
|
SET FOREIGN_KEY_CHECKS= OFF;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES tx(x);
|
|
ALTER TABLE t1 DROP KEY idx;
|
|
ALTER TABLE t1 CHANGE a c INT;
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY idx(f1)) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1 (f1);
|
|
ALTER TABLE t1 ADD COLUMN f INT;
|
|
SET FOREIGN_KEY_CHECKS= OFF;
|
|
ALTER TABLE t1 DROP KEY idx;
|
|
ALTER TABLE t1 ADD KEY idx (f1);
|
|
SET FOREIGN_KEY_CHECKS= ON;
|
|
ALTER TABLE t1 DROP f3;
|
|
ALTER TABLE t1 CHANGE f f3 INT;
|
|
DROP TABLE t1;
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
#
|
|
# Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE
|
|
# ADD FOREIGN KEY
|
|
#
|
|
CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT,
|
|
PRIMARY KEY (`department_id`)) engine=innodb;
|
|
CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT,
|
|
`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb;
|
|
CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb;
|
|
ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES
|
|
`people` (`people_id`);
|
|
ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people`
|
|
(`people_id`);
|
|
ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people`
|
|
(`people_id`);
|
|
drop table title, department, people;
|
|
create table t1 (a int primary key, b int) engine=innodb;
|
|
create table t2 (c int primary key, d int,
|
|
foreign key (d) references t1 (a) on update cascade) engine=innodb;
|
|
insert t1 values (1,1),(2,2),(3,3);
|
|
insert t2 values (4,1),(5,2),(6,3);
|
|
flush table t2 with read lock;
|
|
connect con1,localhost,root;
|
|
delete from t1 where a=2;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE)
|
|
update t1 set a=10 where a=1;
|
|
connection default;
|
|
unlock tables;
|
|
connection con1;
|
|
connection default;
|
|
lock table t2 write;
|
|
connection con1;
|
|
delete from t1 where a=2;
|
|
connection default;
|
|
unlock tables;
|
|
connection con1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE)
|
|
connection default;
|
|
unlock tables;
|
|
disconnect con1;
|
|
create user foo;
|
|
grant select,update on test.t1 to foo;
|
|
connect foo,localhost,foo;
|
|
update t1 set a=30 where a=3;
|
|
disconnect foo;
|
|
connection default;
|
|
select * from t2;
|
|
c d
|
|
5 2
|
|
4 10
|
|
6 30
|
|
drop table t2, t1;
|
|
drop user foo;
|
|
#
|
|
# MDEV-17595 - Server crashes in copy_data_between_tables or
|
|
# Assertion `thd->transaction.stmt.is_empty() ||
|
|
# (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)'
|
|
# fails in close_tables_for_reopen upon concurrent
|
|
# ALTER TABLE and FLUSH
|
|
#
|
|
CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES(1),(2);
|
|
CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE;
|
|
DROP TABLE t2, t1;
|
|
create table t1 (pk int primary key, data int) engine=innodb;
|
|
insert t1 values (1,1),(2,2),(3,3);
|
|
create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb;
|
|
insert t2 values (1),(2);
|
|
insert t2 values (10);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `1` FOREIGN KEY (`t1_pk`) REFERENCES `t1` (`pk`))
|
|
flush tables;
|
|
flush status;
|
|
update t1 set data=10 where pk+1>10;
|
|
show status like '%opened_tab%';
|
|
Variable_name Value
|
|
Opened_table_definitions 5
|
|
Opened_tables 4
|
|
flush tables;
|
|
flush status;
|
|
update t2 set t1_pk=11 where t1_pk+1>10;
|
|
show status like '%opened_tab%';
|
|
Variable_name Value
|
|
Opened_table_definitions 4
|
|
Opened_tables 4
|
|
flush tables;
|
|
flush status;
|
|
lock tables t1 write;
|
|
show status like '%opened_tab%';
|
|
Variable_name Value
|
|
Opened_table_definitions 2
|
|
Opened_tables 2
|
|
insert t1 values (4,4);
|
|
show status like '%opened_tab%';
|
|
Variable_name Value
|
|
Opened_table_definitions 5
|
|
Opened_tables 5
|
|
unlock tables;
|
|
create function foo() returns int
|
|
begin
|
|
insert t1 values (5,5);
|
|
return 5;
|
|
end|
|
|
flush tables;
|
|
flush status;
|
|
select foo();
|
|
foo()
|
|
5
|
|
show status like '%opened_tab%';
|
|
Variable_name Value
|
|
Opened_table_definitions 6
|
|
Opened_tables 5
|
|
drop function foo;
|
|
drop table t2, t1;
|
|
CREATE TABLE t1 (pk INT, a INT, PRIMARY KEY (pk)) ENGINE=InnoDB;
|
|
XA START 'xid';
|
|
INSERT INTO t1 VALUES (1,2);
|
|
CREATE TABLE x AS SELECT * FROM t1;
|
|
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
|
|
connect con1,localhost,root,,test;
|
|
SET foreign_key_checks= OFF, innodb_lock_wait_timeout= 0;
|
|
SET lock_wait_timeout=2;
|
|
ALTER TABLE t1 ADD FOREIGN KEY f (a) REFERENCES t1 (pk), LOCK=EXCLUSIVE;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
disconnect con1;
|
|
connection default;
|
|
XA END 'xid';
|
|
XA ROLLBACK 'xid';
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (pk INT PRIMARY KEY,
|
|
f1 VARCHAR(10), f2 VARCHAR(10),
|
|
f3 VARCHAR(10), f4 VARCHAR(10),
|
|
f5 VARCHAR(10), f6 VARCHAR(10),
|
|
f7 VARCHAR(10), f8 VARCHAR(10),
|
|
INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
|
|
INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
|
|
'mariadb', 'mariadb', 'mariadb', 'mariadb'),
|
|
(2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
|
|
'mariadb', 'mariadb', 'mariadb', 'mariadb'),
|
|
(3, 'innodb', 'innodb', 'innodb', 'innodb',
|
|
'innodb', 'innodb', 'innodb', 'innodb');
|
|
ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL;
|
|
START TRANSACTION;
|
|
DELETE FROM t1 where f1='mariadb';
|
|
SELECT * FROM t1;
|
|
pk f1 f2 f3 f4 f5 f6 f7 f8
|
|
2 NULL mariadb mariadb mariadb mariadb mariadb mariadb mariadb
|
|
3 innodb innodb innodb innodb innodb innodb innodb innodb
|
|
ROLLBACK;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE;
|
|
START TRANSACTION;
|
|
DELETE FROM t1 where f3='mariadb';
|
|
SELECT * FROM t1;
|
|
pk f1 f2 f3 f4 f5 f6 f7 f8
|
|
3 innodb innodb innodb innodb innodb innodb innodb innodb
|
|
ROLLBACK;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
|
|
UPDATE t1 SET f6='update';
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
|
|
ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE;
|
|
UPDATE t1 SET f6='cascade';
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
|
|
DROP TABLE t1;
|
|
# Start of 10.2 tests
|
|
#
|
|
# MDEV-13246 Stale rows despite ON DELETE CASCADE constraint
|
|
#
|
|
CREATE TABLE users (
|
|
id int unsigned AUTO_INCREMENT PRIMARY KEY,
|
|
name varchar(32) NOT NULL DEFAULT ''
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
CREATE TABLE matchmaking_groups (
|
|
id bigint unsigned AUTO_INCREMENT PRIMARY KEY,
|
|
host_user_id int unsigned NOT NULL UNIQUE,
|
|
CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
CREATE TABLE matchmaking_group_users (
|
|
matchmaking_group_id bigint unsigned NOT NULL,
|
|
user_id int unsigned NOT NULL,
|
|
PRIMARY KEY (matchmaking_group_id,user_id),
|
|
UNIQUE KEY user_id (user_id),
|
|
CONSTRAINT FOREIGN KEY (matchmaking_group_id)
|
|
REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT FOREIGN KEY (user_id)
|
|
REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
CREATE TABLE matchmaking_group_maps (
|
|
matchmaking_group_id bigint unsigned NOT NULL,
|
|
map_id tinyint unsigned NOT NULL,
|
|
PRIMARY KEY (matchmaking_group_id,map_id),
|
|
CONSTRAINT FOREIGN KEY (matchmaking_group_id)
|
|
REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
|
|
INSERT INTO matchmaking_groups VALUES (10,1),(11,2);
|
|
INSERT INTO matchmaking_group_users VALUES (10,1),(11,2);
|
|
INSERT INTO matchmaking_group_maps VALUES (10,55),(11,66);
|
|
BEGIN;
|
|
UPDATE users SET name = 'qux' WHERE id = 1;
|
|
connect con1,localhost,root;
|
|
connection con1;
|
|
SET innodb_lock_wait_timeout= 0;
|
|
DELETE FROM matchmaking_groups WHERE id = 10;
|
|
connection default;
|
|
COMMIT;
|
|
SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
|
|
matchmaking_group_id user_id
|
|
SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
|
|
matchmaking_group_id map_id
|
|
SELECT * FROM users;
|
|
id name
|
|
1 qux
|
|
2 bar
|
|
DROP TABLE
|
|
matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
|
|
#
|
|
# MDEV-13331 FK DELETE CASCADE does not honor innodb_lock_wait_timeout
|
|
#
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (
|
|
id INT NOT NULL PRIMARY KEY,
|
|
ref_id INT NOT NULL DEFAULT 0,
|
|
f INT NULL,
|
|
FOREIGN KEY (ref_id) REFERENCES t1 (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1),(2);
|
|
INSERT INTO t2 VALUES (1,1,10),(2,2,20);
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`id` int(11) NOT NULL,
|
|
`ref_id` int(11) NOT NULL DEFAULT 0,
|
|
`f` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `ref_id` (`ref_id`),
|
|
CONSTRAINT `1` FOREIGN KEY (`ref_id`) REFERENCES `t1` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
connection con1;
|
|
BEGIN;
|
|
UPDATE t2 SET f = 11 WHERE id = 1;
|
|
connection default;
|
|
SET innodb_lock_wait_timeout= 0;
|
|
DELETE FROM t1 WHERE id = 1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
select @innodb_lock_wait_timeout_save;
|
|
@innodb_lock_wait_timeout_save
|
|
NULL
|
|
SET innodb_lock_wait_timeout= 50;
|
|
connection con1;
|
|
COMMIT;
|
|
connection default;
|
|
SELECT * FROM t2;
|
|
id ref_id f
|
|
1 1 11
|
|
2 2 20
|
|
DELETE FROM t1 WHERE id = 1;
|
|
SELECT * FROM t2;
|
|
id ref_id f
|
|
2 2 20
|
|
DROP TABLE t2, t1;
|
|
#
|
|
# MDEV-15199 Referential integrity broken in ON DELETE CASCADE
|
|
#
|
|
CREATE TABLE member (id int AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO member VALUES (1);
|
|
CREATE TABLE address (
|
|
id int AUTO_INCREMENT PRIMARY KEY,
|
|
member_id int NOT NULL,
|
|
KEY address_FI_1 (member_id),
|
|
CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO address VALUES (2,1);
|
|
CREATE TABLE payment_method (
|
|
id int AUTO_INCREMENT PRIMARY KEY,
|
|
member_id int NOT NULL,
|
|
cardholder_address_id int DEFAULT NULL,
|
|
KEY payment_method_FI_1 (member_id),
|
|
KEY payment_method_FI_2 (cardholder_address_id),
|
|
CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO payment_method VALUES (3,1,2);
|
|
BEGIN;
|
|
UPDATE member SET id=42;
|
|
SELECT * FROM member;
|
|
id
|
|
42
|
|
SELECT * FROM address;
|
|
id member_id
|
|
2 42
|
|
SELECT * FROM payment_method;
|
|
id member_id cardholder_address_id
|
|
3 42 2
|
|
DELETE FROM member;
|
|
COMMIT;
|
|
SELECT * FROM member;
|
|
id
|
|
SELECT * FROM address;
|
|
id member_id
|
|
SELECT * FROM payment_method;
|
|
id member_id cardholder_address_id
|
|
DROP TABLE payment_method,address,member;
|
|
#
|
|
# Bug #26958695 INNODB NESTED STORED FIELD WITH CONSTRAINT KEY
|
|
# PRODUCE BROKEN TABLE (no bug in MariaDB)
|
|
#
|
|
create table t1(f1 int,f2 int, primary key(f1), key(f2, f1))engine=innodb;
|
|
create table t2(f1 int, f2 int as (2) stored, f3 int as (f2) stored,
|
|
foreign key(f1) references t1(f2) on update set NULL)
|
|
engine=innodb;
|
|
insert into t1 values(1, 1);
|
|
insert into t2(f1) values(1);
|
|
drop table t2, t1;
|
|
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;
|
|
LOCK TABLE staff WRITE;
|
|
UNLOCK TABLES;
|
|
DROP TABLES staff, store;
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
#
|
|
# MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs
|
|
#
|
|
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES t1(a))
|
|
ENGINE=InnoDB;
|
|
connection con1;
|
|
INSERT INTO t1 SET a=1;
|
|
BEGIN;
|
|
DELETE FROM t1;
|
|
connection default;
|
|
INSERT INTO t2 SET a=1;
|
|
connection con1;
|
|
kill query @id;
|
|
connection default;
|
|
ERROR 70100: Query execution was interrupted
|
|
connection con1;
|
|
ROLLBACK;
|
|
connection default;
|
|
DROP TABLE t2,t1;
|
|
#
|
|
# MDEV-18272 InnoDB index corruption after failed DELETE CASCADE
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
a TINYINT UNSIGNED NOT NULL, b TINYINT UNSIGNED NOT NULL, KEY(b),
|
|
CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t1 (a,b) VALUES
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,1),(0,1),(1,0);
|
|
connection con1;
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
connection default;
|
|
DELETE IGNORE FROM t1 WHERE b = 1;
|
|
Warnings:
|
|
Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop extra constraints and try again
|
|
Warning 1296 Got error 193 '`test`.`t1`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB
|
|
Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop extra constraints and try again
|
|
Warning 1296 Got error 193 '`test`.`t1`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB
|
|
SELECT a FROM t1 FORCE INDEX(a);
|
|
a
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
1
|
|
SELECT * FROM t1;
|
|
pk a b
|
|
1 0 0
|
|
2 0 0
|
|
3 0 0
|
|
4 0 0
|
|
5 0 0
|
|
6 0 0
|
|
7 0 0
|
|
8 0 0
|
|
9 0 0
|
|
10 0 0
|
|
11 0 0
|
|
12 0 0
|
|
13 0 1
|
|
14 0 1
|
|
15 1 0
|
|
connection con1;
|
|
COMMIT;
|
|
connection default;
|
|
InnoDB 0 transactions not purged
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Note Found 1 self-references
|
|
test.t1 check status OK
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-17187 table doesn't exist in engine after ALTER other tables
|
|
# with CONSTRAINTs
|
|
#
|
|
call mtr.add_suppression("\\[Warning\\] InnoDB: In ALTER TABLE `test`\\.`t[12]` 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,
|
|
CONSTRAINT fk_fid FOREIGN KEY (fid) REFERENCES t1 (id))engine=innodb;
|
|
insert into t1 values (1), (2), (3);
|
|
insert into t2 values (1, 1), (2, 1), (3, 2);
|
|
set foreign_key_checks=off;
|
|
alter table t2 drop index fk_fid;
|
|
set foreign_key_checks=on;
|
|
delete from t1 where id=2;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`))
|
|
insert into t2 values(4, 99);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`))
|
|
select * from t1;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
select * from t2;
|
|
id fid
|
|
1 1
|
|
2 1
|
|
3 2
|
|
set foreign_key_checks=off;
|
|
delete from t1 where id=2;
|
|
insert into t2 values(4, 99);
|
|
set foreign_key_checks=on;
|
|
select * from t1;
|
|
id
|
|
1
|
|
3
|
|
select * from t2;
|
|
id fid
|
|
1 1
|
|
2 1
|
|
3 2
|
|
4 99
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`id` int(11) NOT NULL,
|
|
`fid` int(11) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t1,t2;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (fk_fid)
|
|
drop table t1,t2;
|
|
ERROR 42S02: Unknown table 'test.t2'
|
|
#
|
|
# MDEV-22934 Table disappear after two alter table command
|
|
#
|
|
CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT,
|
|
f2 INT NOT NULL,
|
|
PRIMARY KEY (f1), INDEX (f2))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL,
|
|
f2 INT NOT NULL, f3 INT NOT NULL,
|
|
PRIMARY KEY(f1, f2), UNIQUE KEY(f2),
|
|
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE CASCADE,
|
|
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (f1) REFERENCES t1(f1) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(f3), ALGORITHM=INPLACE;
|
|
ALTER TABLE t2 DROP INDEX `f2`, ALGORITHM=COPY;
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`f1` int(11) NOT NULL,
|
|
`f2` int(11) NOT NULL,
|
|
`f3` int(11) NOT NULL,
|
|
PRIMARY KEY (`f3`),
|
|
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f2`) ON DELETE CASCADE,
|
|
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
CREATE TABLE t2 (f1 INT NOT NULL)ENGINE=InnoDB;
|
|
ERROR 42S01: Table 't2' already exists
|
|
DROP TABLE t2, t1;
|
|
#
|
|
# MDEV-23685 SIGSEGV on ADD FOREIGN KEY after failed attempt
|
|
# to create unique key on virtual column
|
|
#
|
|
CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, b INT AS (a)) ENGINE=InnODB;
|
|
INSERT INTO t1 (pk,a) VALUES (1,10),(2,10);
|
|
ALTER TABLE t1 ADD UNIQUE INDEX ind9 (b), LOCK=SHARED;
|
|
ERROR 23000: Duplicate entry '10' for key 'ind9'
|
|
SET FOREIGN_KEY_CHECKS= 0;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (pk);
|
|
DROP TABLE t1;
|
|
SET FOREIGN_KEY_CHECKS= 1;
|
|
#
|
|
# MDEV-23455 Hangs + Sig11 in unknown location(s) due to single complex FK query
|
|
#
|
|
Parsing foreign keys 1...
|
|
ERROR 42S02: Table 'test.t1' doesn't exist
|
|
Parsing foreign keys 2...
|
|
ERROR HY000: Missing index for constraint 't1.aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa0' in the referenced table 't1'
|
|
Parsing foreign keys 3...
|
|
ERROR HY000: Missing index for constraint 't1.aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa0' in the referenced table 't1'
|
|
Parsing foreign keys 4...
|
|
#
|
|
# MDEV-27583 InnoDB uses different constants for FK cascade
|
|
# error message in SQL vs error log
|
|
#
|
|
CREATE TABLE t1
|
|
(a INT, b INT, KEY(b),
|
|
CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE)
|
|
ENGINE=InnoDB;
|
|
INSERT INTO t1 (a,b) VALUES
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,1),(1,0);
|
|
DELETE FROM t1 WHERE b = 1;
|
|
ERROR HY000: Got error 193 '`test`.`t1`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop extra constraints and try again
|
|
Error 1296 Got error 193 '`test`.`t1`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB
|
|
DROP TABLE t1;
|
|
FOUND 1 /InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15.*/ in mysqld.1.err
|
|
# End of 10.2 tests
|
|
#
|
|
# MDEV-28980 InnoDB: Failing assertion: len <= MAX_TABLE_NAME_LEN
|
|
#
|
|
SET NAMES utf8;
|
|
CREATE TABLE t (a INT PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE DATABASE `db`;
|
|
CREATE TABLE `db`.u (
|
|
a INT PRIMARY KEY,
|
|
CONSTRAINT `††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††`
|
|
FOREIGN KEY (a) REFERENCES test.t (a)) ENGINE=InnoDB;
|
|
DROP TABLE `db`.u;
|
|
DROP DATABASE `db`;
|
|
DROP TABLE t;
|
|
# End of 10.3 tests
|
|
CREATE TABLE t1 (a GEOMETRY, INDEX(a(8)),
|
|
FOREIGN KEY (a) REFERENCES x (xx)) ENGINE=InnoDB;
|
|
ERROR 42S02: Table 'test.x' doesn't exist
|
|
#
|
|
# MDEV-23675 Assertion `pos < table->n_def' in dict_table_get_nth_col
|
|
#
|
|
CREATE TABLE t1 (pk int PRIMARY KEY, a INT, b INT, c INT, KEY(c),
|
|
FOREIGN KEY fx (b) REFERENCES t1 (c))
|
|
ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1,0,10,10);
|
|
ALTER TABLE t1 DROP a, ALGORITHM=INSTANT;
|
|
SET FOREIGN_KEY_CHECKS= 0;
|
|
DROP INDEX fx ON t1;
|
|
INSERT INTO t1 VALUES (2,11,11);
|
|
DROP TABLE t1;
|
|
SET FOREIGN_KEY_CHECKS=DEFAULT;
|
|
#
|
|
# MDEV-32018 Allow the setting of Auto_increment on FK referenced columns
|
|
#
|
|
CREATE TABLE t1 (
|
|
id int unsigned NOT NULL PRIMARY KEY
|
|
);
|
|
CREATE TABLE t2 (
|
|
id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
t1_id int unsigned DEFAULT NULL,
|
|
CONSTRAINT FK_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id)
|
|
);
|
|
ALTER TABLE t1 MODIFY id INT unsigned AUTO_INCREMENT;
|
|
DROP TABLE t2, t1;
|
|
#
|
|
# MDEV-31441 BLOB corruption on UPDATE of PRIMARY KEY with FOREIGN KEY
|
|
#
|
|
CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1(pk))
|
|
ENGINE=InnoDB;
|
|
SET @blob = REPEAT('A', @@innodb_page_size / 2);
|
|
INSERT INTO t1 SET pk=1, t=@blob;
|
|
INSERT INTO t2 SET pk=1;
|
|
connection con1;
|
|
BEGIN;
|
|
DELETE FROM t2;
|
|
connection default;
|
|
UPDATE t1 SET pk=12;
|
|
connection con1;
|
|
COMMIT;
|
|
disconnect con1;
|
|
connection default;
|
|
UPDATE t1 SET pk=1;
|
|
SELECT pk,t=@blob FROM t1;
|
|
pk t=@blob
|
|
1 1
|
|
DROP TABLE t2, t1;
|
|
# End of 10.4 tests
|
|
#
|
|
# MDEV-20729 Fix REFERENCES constraint in column definition
|
|
#
|
|
set default_storage_engine= innodb;
|
|
create table t1 (x int primary key, y int unique);
|
|
create table t2 (x int references t1(x), y int constraint fk references t1(y));
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`x` int(11) DEFAULT NULL,
|
|
`y` int(11) DEFAULT NULL,
|
|
KEY `x` (`x`),
|
|
KEY `fk` (`y`),
|
|
CONSTRAINT `1` FOREIGN KEY (`x`) REFERENCES `t1` (`x`),
|
|
CONSTRAINT `fk` FOREIGN KEY (`y`) REFERENCES `t1` (`y`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
create table t3 (z int);
|
|
alter table t3 add x int references t1(x), add y int constraint fk2 references t1(y);
|
|
show create table t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`z` int(11) DEFAULT NULL,
|
|
`x` int(11) DEFAULT NULL,
|
|
`y` int(11) DEFAULT NULL,
|
|
KEY `x` (`x`),
|
|
KEY `fk2` (`y`),
|
|
CONSTRAINT `1` FOREIGN KEY (`x`) REFERENCES `t1` (`x`),
|
|
CONSTRAINT `fk2` FOREIGN KEY (`y`) REFERENCES `t1` (`y`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop tables t3, t2, t1;
|
|
create table t1 (id int primary key);
|
|
create table t2 (id2 int references t1);
|
|
ERROR 42000: Incorrect foreign key definition for 'id2': referenced field not found
|
|
create table t2 (id int references t1);
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`id` int(11) DEFAULT NULL,
|
|
KEY `id` (`id`),
|
|
CONSTRAINT `1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop tables t2, t1;
|
|
set default_storage_engine= default;
|
|
#
|
|
# MDEV-21690 LeakSanitizer: detected memory leaks in mem_heap_create_block_func
|
|
#
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
CREATE TABLE t1 (a TEXT, b TEXT) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b);
|
|
ERROR HY000: Missing index for constraint 't1.1' in the referenced table 't1'
|
|
SET FOREIGN_KEY_CHECKS=DEFAULT;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-22602 Disable UPDATE CASCADE for SQL constraints
|
|
#
|
|
# TODO: enable them after MDEV-16417 is finished
|
|
create or replace table t1 (a int primary key) engine=innodb;
|
|
create or replace table t2 (a int, constraint foo check(a > 0), foreign key(a) references t1(a) on update cascade) engine=innodb;
|
|
ERROR HY000: Function or expression 'a' cannot be used in the CHECK clause of `foo`
|
|
create or replace table t2 (a int, check(a > 0), foreign key(a) references t1(a) on update cascade) engine=innodb;
|
|
ERROR HY000: Function or expression 'a' cannot be used in the CHECK clause of `CONSTRAINT_1`
|
|
create or replace table t1 (f1 int, f2 date, f3 date, key(f1,f3,f2)) engine=innodb;
|
|
create or replace table t2 (
|
|
a int, s date, e date,
|
|
period for p (s, e),
|
|
primary key (a, p without overlaps),
|
|
foreign key (a, e, s) references t1 (f1, f3, f2) on delete cascade on update cascade) engine=innodb;
|
|
ERROR HY000: Key `PRIMARY` cannot have WITHOUT OVERLAPS
|
|
create or replace table t1 (a varchar(4096) unique) engine=innodb;
|
|
create or replace table t2 (pk int primary key, a varchar(4096) unique, foreign key(a) references t1(a) on update cascade) engine=innodb;
|
|
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
drop table t1;
|
|
#
|
|
# MDEV-26824 Can't add foreign key with empty referenced columns list
|
|
#
|
|
create table t2(a int primary key) engine=innodb;
|
|
create table t1(a int primary key, b int) engine=innodb;
|
|
alter table t2 add foreign key(a) references t1(a, b);
|
|
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
|
|
create or replace table t1(a tinyint primary key) engine innodb;
|
|
alter table t2 add foreign key(a) references t1;
|
|
ERROR 42000: Incorrect foreign key definition for 'a': foreign-referenced fields type mismatch
|
|
create or replace table t1(b int primary key) engine innodb;
|
|
alter table t2 add foreign key(a) references t1;
|
|
ERROR 42000: Incorrect foreign key definition for 'a': referenced field not found
|
|
create or replace table t1(a int primary key, b int) engine innodb;
|
|
alter table t2 add foreign key(a) references t1;
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`),
|
|
CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop tables t2, t1;
|
|
#
|
|
# MDEV-29182 Assertion fld->field_no < table->n_v_def failed on cascade
|
|
#
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b VARCHAR(3), c INT AS (LENGTH(b)) VIRTUAL,
|
|
INDEX(c)) ENGINE=InnoDB;
|
|
CREATE TABLE t2(a INT REFERENCES t1(a) ON UPDATE CASCADE,
|
|
b INT GENERATED ALWAYS AS(a) VIRTUAL, INDEX(b)) ENGINE=InnoDB;
|
|
INSERT INTO t1 SET a=1,b='fu';
|
|
INSERT INTO t2 SET a=1;
|
|
UPDATE t1 SET a=2,b='bar';
|
|
SELECT * FROM t1;
|
|
a b c
|
|
2 bar 3
|
|
SELECT * FROM t2;
|
|
a b
|
|
2 2
|
|
DROP TABLE t2,t1;
|
|
# End of 10.5 tests
|
|
#
|
|
# MDEV-26554 Table-rebuilding DDL on parent table causes crash
|
|
# for INSERT into child table
|
|
#
|
|
CREATE TABLE parent(a INT PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE child(a INT PRIMARY KEY REFERENCES parent(a)) ENGINE=InnoDB;
|
|
connect con1, localhost, root,,;
|
|
BEGIN;
|
|
INSERT INTO child SET a=1;
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`))
|
|
connection default;
|
|
SET innodb_lock_wait_timeout= 1;
|
|
TRUNCATE TABLE parent;
|
|
ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`child`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `test`.`parent` (`a`))
|
|
DROP TABLE parent;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (1)
|
|
SET lock_wait_timeout=0;
|
|
RENAME TABLE parent TO transparent;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
ALTER TABLE parent FORCE, ALGORITHM=COPY;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET foreign_key_checks=0;
|
|
TRUNCATE TABLE parent;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
DROP TABLE parent;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
ALTER TABLE parent FORCE, ALGORITHM=COPY;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
connection con1;
|
|
COMMIT;
|
|
connection default;
|
|
SET innodb_lock_wait_timeout= 50;
|
|
TRUNCATE TABLE parent;
|
|
ALTER TABLE parent FORCE, ALGORITHM=COPY;
|
|
ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
|
|
ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT;
|
|
SET foreign_key_checks=ON;
|
|
TRUNCATE TABLE parent;
|
|
ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`child`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `test`.`parent` (`a`))
|
|
ALTER TABLE parent FORCE, ALGORITHM=COPY;
|
|
ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
|
|
RENAME TABLE parent TO transparent;
|
|
DROP TABLE child, transparent;
|
|
#
|
|
# MDEV-26217 Failing assertion: list.count > 0 in ut_list_remove
|
|
# or Assertion `lock->trx == this' failed in dberr_t trx_t::drop_table
|
|
#
|
|
CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1);
|
|
CREATE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY(pk) REFERENCES t1(pk))
|
|
ENGINE=InnoDB;
|
|
connection con1;
|
|
SET FOREIGN_KEY_CHECKS=OFF;
|
|
CREATE OR REPLACE TABLE t1 (b INT) ENGINE=InnoDB;
|
|
connection default;
|
|
INSERT INTO t2 VALUES (1);
|
|
connection con1;
|
|
connection default;
|
|
DROP TABLE IF EXISTS t2, t1;
|
|
#
|
|
# MDEV-30531 Corrupt index(es) on busy table when using FOREIGN KEY
|
|
#
|
|
CREATE TABLE collections (
|
|
id int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
collectionhash varchar(255) NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY ix_collection_collectionhash (collectionhash)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE binaries (
|
|
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
collections_id int(11) unsigned NOT NULL DEFAULT 0,
|
|
binaryhash binary(16) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY ix_binary_binaryhash (binaryhash),
|
|
CONSTRAINT FK_collections FOREIGN KEY (collections_id) REFERENCES collections (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO collections (id) VALUES (NULL);
|
|
connection con1;
|
|
INSERT INTO binaries (id,collections_id) VALUES (NULL,1);
|
|
REPLACE INTO collections (id) VALUES (NULL);
|
|
connection default;
|
|
REPLACE INTO binaries (id) VALUES (NULL);
|
|
SET GLOBAL innodb_max_purge_lag_wait=0;
|
|
CHECK TABLE binaries, collections EXTENDED;
|
|
Table Op Msg_type Msg_text
|
|
test.binaries check Note Found 1 foreign keys
|
|
test.binaries check status OK
|
|
test.collections check Note Found 1 referenced keys
|
|
test.collections check status OK
|
|
disconnect con1;
|
|
DROP TABLE binaries, collections;
|
|
CREATE SCHEMA `#mysql50##mysql50#d-b`;
|
|
CREATE TABLE `#mysql50##mysql50#d-b`.t1 (a INT PRIMARY KEY, b INT UNIQUE) engine=InnoDB;
|
|
USE `#mysql50##mysql50#d-b`;
|
|
CREATE TABLE t2 (a INT PRIMARY KEY, b INT UNIQUE REFERENCES t1(b)) ENGINE=InnoDB;
|
|
SET STATEMENT foreign_key_checks=0 FOR
|
|
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
UNIQUE KEY `b` (`b`),
|
|
CONSTRAINT `1` FOREIGN KEY (`b`) REFERENCES `t1` (`b`),
|
|
CONSTRAINT `2` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
INSERT INTO t1 SET a=1;
|
|
INSERT INTO t2 SET a=1;
|
|
DELETE FROM t1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`#mysql50#d-b`.`t2`, CONSTRAINT `2` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
|
|
DELETE FROM t2;
|
|
DELETE FROM t1;
|
|
DROP DATABASE `#mysql50##mysql50#d-b`;
|
|
USE test;
|
|
#
|
|
# MDEV-35962 CREATE INDEX fails to heal a FOREIGN KEY constraint
|
|
#
|
|
CREATE TABLE t2 (b INT, FOREIGN KEY (b) REFERENCES t1(a)) ENGINE=InnoDB;
|
|
ERROR 42S02: Table 'test.t1' doesn't exist
|
|
SET STATEMENT foreign_key_checks=0 FOR
|
|
CREATE TABLE t2 (b INT, FOREIGN KEY (b) REFERENCES t1(a)) ENGINE=InnoDB;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
SET STATEMENT foreign_key_checks=0 FOR
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t2 VALUES (1);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
|
|
ALTER TABLE t1 ADD KEY(a), ALGORITHM=NOCOPY;
|
|
INSERT INTO t2 VALUES (1);
|
|
DROP INDEX b ON t2;
|
|
ERROR HY000: Missing index for constraint '1' in the foreign table 't2'
|
|
SET STATEMENT foreign_key_checks=0 FOR
|
|
DROP INDEX b ON t2;
|
|
DELETE FROM t2;
|
|
DELETE FROM t1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
|
|
ALTER TABLE t2 ADD KEY(b), ALGORITHM=NOCOPY;
|
|
DELETE FROM t1;
|
|
DROP TABLE t2, t1;
|
|
#
|
|
# MDEV-33167 ASAN errors after failing to load foreign key
|
|
# relation for the table
|
|
#
|
|
call mtr.add_suppression("InnoDB: Load table `test`.`t3` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.");
|
|
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR
|
|
CREATE TABLE t1(f1 VARCHAR(8),
|
|
FOREIGN KEY(f1) REFERENCES test.t3(f1))ENGINE=InnoDB;
|
|
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR
|
|
CREATE TABLE t2(f1 VARCHAR(8),
|
|
FOREIGN KEY(f1) REFERENCES test.t3(f1))
|
|
ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
|
|
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR
|
|
CREATE TABLE t3(f1 VARCHAR(8) PRIMARY KEY)
|
|
ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
set GLOBAL innodb_fast_shutdown=0;
|
|
# restart
|
|
ALTER TABLE t2 FORCE;
|
|
DROP TABLE t2, t1, t3;
|
|
# End of 10.6 tests
|
|
CREATE TABLE t1
|
|
(
|
|
f1 VARCHAR(32)BINARY NOT NULL,
|
|
f2 VARCHAR(32)BINARY NOT NULL,
|
|
PRIMARY KEY (f1),
|
|
INDEX(f2)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES('MySQL', 'InnoDB'), ('MariaDB', 'NDB');
|
|
CREATE TABLE t2
|
|
(
|
|
f1 VARCHAR(32)BINARY NOT NULL,
|
|
f2 VARCHAR(255)BINARY NOT NULL,
|
|
f3 int, PRIMARY KEY (f1), INDEX(f1), INDEX(f2)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES('MySQL', 'MySQL', 1),
|
|
('MariaDB', 'MariaDB', 1);
|
|
ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t2 (f2);
|
|
ALTER TABLE t2 ADD FOREIGN KEY (f2) REFERENCES t2 (f2),
|
|
ADD UNIQUE INDEX(f3);
|
|
ERROR HY000: Cannot delete rows from table which is parent in a foreign key constraint '1' of table 't1'
|
|
drop table t1, t2;
|
|
# End of 10.11 tests
|