mirror of
https://github.com/MariaDB/server.git
synced 2025-02-20 20:33:15 +01:00
ceb64ee4d1
Given that key part lengths may differ on two sides of foreign key relation, it's important to use a child (referenced) table's keys during the lengths calculation.
291 lines
13 KiB
Text
291 lines
13 KiB
Text
create table t1 (x int primary key) engine=innodb;
|
|
create table t2 (x int, y int,
|
|
foreign key (x) references t1(x)) engine=innodb;
|
|
set statement foreign_key_checks = 0 for
|
|
drop table t1;
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Error Table 'test.t1' doesn't exist
|
|
test.t1 check status Operation failed
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check Warning Table test.t1 is not found. Needed for a foreign key t2_ibfk_1
|
|
test.t2 check error Corrupt
|
|
set statement foreign_key_checks = 0 for
|
|
create table t1 (x int) engine=innodb;
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t1
|
|
test.t1 check error Corrupt
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t1
|
|
test.t2 check error Corrupt
|
|
set statement foreign_key_checks = 0 for
|
|
drop index x on t2;
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t2
|
|
test.t1 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t1
|
|
test.t1 check error Corrupt
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t2
|
|
test.t2 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t1
|
|
test.t2 check error Corrupt
|
|
set statement foreign_key_checks = 0 for
|
|
create unique index p on t1(x);
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t2
|
|
test.t1 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t1
|
|
test.t1 check error Corrupt
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t2
|
|
test.t2 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t1
|
|
test.t2 check error Corrupt
|
|
set statement foreign_key_checks = 0 for
|
|
create index x on t2(x);
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t2
|
|
test.t1 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t1
|
|
test.t1 check error Corrupt
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t2
|
|
test.t2 check Warning No suitable key found for foreign key t2_ibfk_1 in table test.t1
|
|
test.t2 check error Corrupt
|
|
set statement foreign_key_checks = 0 for
|
|
drop table t2;
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check Error Table 'test.t2' doesn't exist
|
|
test.t2 check status Operation failed
|
|
# ======================================================================
|
|
set statement foreign_key_checks = 0 for
|
|
create table t2 (x int, y int, key(x),
|
|
foreign key (x) references t1(x)) engine=innodb;
|
|
insert into t1 (x) values (1);
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
insert into t2 (x, y) values (1, 10);
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
set statement foreign_key_checks = 0 for
|
|
insert into t2 (x, y) values (2, 20), (2, 20), (3, 30);
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2')
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2')
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '3')
|
|
test.t1 check error Corrupt
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2')
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2')
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '3')
|
|
test.t2 check error Corrupt
|
|
insert into t1 (x) values (2);
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '3')
|
|
test.t1 check error Corrupt
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '3')
|
|
test.t2 check error Corrupt
|
|
insert into t1 (x) values (3);
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
drop table t2, t1;
|
|
create table t1 (x int primary key, y int, z int,
|
|
key(x, z), unique(y)) engine=innodb;
|
|
insert into t1 (x, y, z) values (1, 10, 100);
|
|
create table t2 (x int, t text, y int, z int,
|
|
foreign key (x, y) references t1(x, z),
|
|
foreign key(z) references t1(y)) engine=innodb;
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`x` int(11) DEFAULT NULL,
|
|
`t` text DEFAULT NULL,
|
|
`y` int(11) DEFAULT NULL,
|
|
`z` int(11) DEFAULT NULL,
|
|
KEY `x` (`x`,`y`),
|
|
KEY `z` (`z`),
|
|
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`x`, `y`) REFERENCES `t1` (`x`, `z`),
|
|
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`z`) REFERENCES `t1` (`y`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
set statement foreign_key_checks = 0 for
|
|
insert into t2 (x, y, z, t) values (2, 2, 200, "two, two, 200"),
|
|
(2, 22, 202, "two, twenty two, 202"),
|
|
(3, 30, 300, "three, thirty, 300"),
|
|
(0, 0, 10, NULL);
|
|
create table t3 (x int, t text, y int, z int
|
|
#, foreign key (x) references t2(x)
|
|
) engine=innodb;
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2-2')
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2-22')
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '3-30')
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '0-0')
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_2, record: '200')
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_2, record: '202')
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_2, record: '300')
|
|
test.t1 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_2, record: '10')
|
|
test.t1 check error Corrupt
|
|
check table t2 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2-2')
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2-22')
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '3-30')
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '0-0')
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_2, record: '200')
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_2, record: '202')
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_2, record: '300')
|
|
test.t2 check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_2, record: '10')
|
|
test.t2 check error Corrupt
|
|
check table t3 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t3 check status OK
|
|
drop table t3, t2, t1;
|
|
# MDEV-35733 ASAN errors in row_mysql_store_col_in_innobase_format
|
|
create table t (pk int primary key, a int, b int, key(b),
|
|
foreign key (a) references t (b)) engine=innodb;
|
|
insert into t values (1,10,10);
|
|
check table t extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t check status OK
|
|
drop table t;
|
|
# MDEV-35730 Server crashes in Field::ptr_in_record
|
|
CREATE TABLE t (id INT, a TIME, b BIT, KEY(a,id)) ENGINE=InnoDB;
|
|
INSERT INTO t VALUES (1,'00:00:00',0);
|
|
SET FOREIGN_KEY_CHECKS = OFF;
|
|
ALTER TABLE t ADD FOREIGN KEY (b) REFERENCES t (a);
|
|
SET FOREIGN_KEY_CHECKS = ON;
|
|
CHECK TABLE t EXTENDED;
|
|
Table Op Msg_type Msg_text
|
|
test.t check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t_ibfk_1, record: '\x00')
|
|
test.t check error Corrupt
|
|
DROP TABLE t;
|
|
# MDEV-35731 Assertion `(mem_root->flags & 4) == 0' failed upon 2nd
|
|
# execution of CHECK TABLE with FK
|
|
create table t1 (a int) engine=innodb;
|
|
set foreign_key_checks = off;
|
|
alter table t1 add foreign key (a) references x(x);
|
|
set foreign_key_checks = on;
|
|
prepare stmt from 'check table t1 extended';
|
|
execute stmt;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Warning Table test.x is not found. Needed for a foreign key t1_ibfk_1
|
|
test.t1 check error Corrupt
|
|
execute stmt;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Warning Table test.x is not found. Needed for a foreign key t1_ibfk_1
|
|
test.t1 check error Corrupt
|
|
create table t2 (a int) engine=innodb;
|
|
set foreign_key_checks = off;
|
|
alter table t2 add foreign key (a) references x(x);
|
|
set foreign_key_checks = on;
|
|
prepare stmt from 'check table t1, t2, nosuchtable extended';
|
|
execute stmt;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Warning Table test.x is not found. Needed for a foreign key t1_ibfk_1
|
|
test.t1 check error Corrupt
|
|
test.t2 check Warning Table test.x is not found. Needed for a foreign key t2_ibfk_1
|
|
test.t2 check error Corrupt
|
|
test.nosuchtable check Error Table 'test.nosuchtable' doesn't exist
|
|
test.nosuchtable check status Operation failed
|
|
execute stmt;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check Warning Table test.x is not found. Needed for a foreign key t1_ibfk_1
|
|
test.t1 check error Corrupt
|
|
test.t2 check Warning Table test.x is not found. Needed for a foreign key t2_ibfk_1
|
|
test.t2 check error Corrupt
|
|
test.nosuchtable check Error Table 'test.nosuchtable' doesn't exist
|
|
test.nosuchtable check status Operation failed
|
|
drop table t2;
|
|
drop table t1;
|
|
# MDEV-35737 MSAN errors in check_table_referential_checks_needed upon
|
|
# LOAD INDEX
|
|
create table t (pk int primary key) engine=innodb;
|
|
load index into cache t;
|
|
Table Op Msg_type Msg_text
|
|
test.t preload_keys note The storage engine for the table doesn't support preload_keys
|
|
drop table t;
|
|
# MDEV-35908 Unexpected error, crash, MSAN errors, assertion failures
|
|
# upon CHECK
|
|
create table t (pk int primary key, a varchar(15), b char(15), key(a), key(b),
|
|
foreign key (a) references t(b) on delete cascade)
|
|
engine=innodb;
|
|
insert into t values (1,'foo', 'foo');
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t`, CONSTRAINT `t_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`b`) ON DELETE CASCADE)
|
|
set foreign_key_checks = off;
|
|
insert into t values (1,'foo', 'foo');
|
|
insert into t values (2,'fee', 'qwe');
|
|
insert into t values (3,'fii', 'fiii');
|
|
# This one is really confusing, but it's a valid reference to 'foo'
|
|
# See the select results after b='foo' record is deleted.
|
|
insert into t values (4,'foo ', 'floo');
|
|
set foreign_key_checks = on;
|
|
check table t extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t_ibfk_1, record: 'fee')
|
|
test.t check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t_ibfk_1, record: 'fii')
|
|
test.t check error Corrupt
|
|
drop table t;
|
|
create table t (pk int primary key, a char(15), b varchar(15), key(b), foreign key (a) references t(b)) engine=innodb;
|
|
insert into t values (1,'foo', 'foo');
|
|
check table t extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t check status OK
|
|
drop table t;
|
|
# Bogus table: length of `b` is bigger than length of `a`
|
|
create table t (a time, b inet6, key(a)) engine=innodb;
|
|
alter table t add foreign key (b) references t (a);
|
|
insert into t values ('00:00:00','8000:0020:2020:2020:2020:2020:2020:2020');
|
|
check table t extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t check Warning Bogus foreign key t_ibfk_1 is skipped.
|
|
test.t check error Corrupt
|
|
drop table t;
|
|
# In a->b relation, `a` key_part can be shorter than `b`
|
|
create table t (a varchar(1), b varchar(8), key(b),
|
|
foreign key(a) references t(b)) engine=innodb;
|
|
set foreign_key_checks = off;
|
|
insert into t values ('d','dd');
|
|
set foreign_key_checks = on;
|
|
check table t extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t check Warning Cannot add or update a child row: a foreign key constraint fails (Key: t_ibfk_1, record: 'd')
|
|
test.t check error Corrupt
|
|
drop table t;
|
|
# MDEV-35936 More ASAN errors in check_key_referential_integrity
|
|
create table t1 (a char(64)) engine=innodb;
|
|
insert into t1 values ('foo');
|
|
create table t2 (b char(128), key(b)) engine=innodb;
|
|
insert into t2 values ('foo');
|
|
alter table t1 add foreign key (a) references t2 (b);
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
drop table t1;
|
|
drop table t2;
|