1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
|
--source include/have_innodb.inc
--source include/count_sessions.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;
SET FOREIGN_KEY_CHECKS=1;
--echo #
--echo # MDEV-13246 Stale rows despite ON DELETE CASCADE constraint
--echo #
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,,)
SET innodb_lock_wait_timeout= 1;
DELETE FROM matchmaking_groups WHERE id = 10;
--connection default
COMMIT;
--sorted_result
SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
--sorted_result
SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
--sorted_result
SELECT * FROM users;
DROP TABLE
matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
--echo #
--echo # MDEV-13331 FK DELETE CASCADE does not honor innodb_lock_wait_timeout
--echo #
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;
--connection con1
BEGIN;
UPDATE t2 SET f = 11 WHERE id = 1;
--connection default
SET innodb_lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
DELETE FROM t1 WHERE id = 1;
--connection con1
COMMIT;
--disconnect con1
--connection default
SELECT * FROM t2;
DELETE FROM t1 WHERE id = 1;
SELECT * FROM t2;
DROP TABLE t2, t1;
--source include/wait_until_count_sessions.inc
|