| 1 |
--source include/have_innodb.inc |
| 2 |
--source include/count_sessions.inc |
| 3 |
--source include/default_charset.inc |
| 4 |
|
| 5 |
--disable_query_log |
| 6 |
call mtr.add_suppression("InnoDB: Transaction was aborted due to "); |
| 7 |
call mtr.add_suppression("Invalid \\(old\\?\\) table or database name '#mysql50#d-b'"); |
| 8 |
--enable_query_log |
| 9 |
|
| 10 |
SET GLOBAL innodb_stats_persistent = 0; |
| 11 |
|
| 12 |
--echo # |
| 13 |
--echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW |
| 14 |
--echo # DICT_CREATE_FOREIGN_CONSTR |
| 15 |
--echo # |
| 16 |
|
| 17 |
create table t1 (f1 int primary key) engine=InnoDB; |
| 18 |
--error ER_CANT_CREATE_TABLE |
| 19 |
create table t2 (f1 int primary key, |
| 20 |
constraint c1 foreign key (f1) references t1(f1), |
| 21 |
constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB; |
| 22 |
create table t2 (f1 int primary key, |
| 23 |
constraint c1 foreign key (f1) references t1(f1)) engine=innodb; |
| 24 |
|
| 25 |
--error ER_CANT_CREATE_TABLE |
| 26 |
alter table t2 add constraint c1 foreign key (f1) references t1(f1); |
| 27 |
|
| 28 |
set foreign_key_checks = 0; |
| 29 |
--error ER_DUP_CONSTRAINT_NAME |
| 30 |
alter table t2 add constraint c1 foreign key (f1) references t1(f1); |
| 31 |
|
| 32 |
drop table t2, t1; |
| 33 |
|
| 34 |
--echo # |
| 35 |
--echo # Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN |
| 36 |
--echo # NULL/NOT NULL MISMATCH |
| 37 |
--echo # |
| 38 |
|
| 39 |
set foreign_key_checks = 1; |
| 40 |
show variables like 'foreign_key_checks'; |
| 41 |
|
| 42 |
CREATE TABLE t1 |
| 43 |
(a INT NOT NULL, |
| 44 |
b INT NOT NULL, |
| 45 |
INDEX idx(a)) ENGINE=InnoDB; |
| 46 |
|
| 47 |
CREATE TABLE t2 |
| 48 |
(a INT KEY, |
| 49 |
b INT, |
| 50 |
INDEX ind(b), |
| 51 |
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) |
| 52 |
ENGINE=InnoDB; |
| 53 |
|
| 54 |
show create table t1; |
| 55 |
show create table t2; |
| 56 |
|
| 57 |
INSERT INTO t1 VALUES (1, 80); |
| 58 |
INSERT INTO t1 VALUES (2, 81); |
| 59 |
INSERT INTO t1 VALUES (3, 82); |
| 60 |
INSERT INTO t1 VALUES (4, 83); |
| 61 |
INSERT INTO t1 VALUES (5, 84); |
| 62 |
|
| 63 |
INSERT INTO t2 VALUES (51, 1); |
| 64 |
INSERT INTO t2 VALUES (52, 2); |
| 65 |
INSERT INTO t2 VALUES (53, 3); |
| 66 |
INSERT INTO t2 VALUES (54, 4); |
| 67 |
INSERT INTO t2 VALUES (55, 5); |
| 68 |
|
| 69 |
SELECT a, b FROM t1 ORDER BY a; |
| 70 |
SELECT a, b FROM t2 ORDER BY a; |
| 71 |
|
| 72 |
--error ER_NO_REFERENCED_ROW_2 |
| 73 |
INSERT INTO t2 VALUES (56, 6); |
| 74 |
|
| 75 |
ALTER TABLE t1 CHANGE a id INT; |
| 76 |
|
| 77 |
SELECT id, b FROM t1 ORDER BY id; |
| 78 |
SELECT a, b FROM t2 ORDER BY a; |
| 79 |
|
| 80 |
--echo # Operations on child table |
| 81 |
--error ER_NO_REFERENCED_ROW_2 |
| 82 |
INSERT INTO t2 VALUES (56, 6); |
| 83 |
--error ER_NO_REFERENCED_ROW_2 |
| 84 |
UPDATE t2 SET b = 99 WHERE a = 51; |
| 85 |
DELETE FROM t2 WHERE a = 53; |
| 86 |
SELECT id, b FROM t1 ORDER BY id; |
| 87 |
SELECT a, b FROM t2 ORDER BY a; |
| 88 |
|
| 89 |
--echo # Operations on parent table |
| 90 |
DELETE FROM t1 WHERE id = 1; |
| 91 |
UPDATE t1 SET id = 50 WHERE id = 5; |
| 92 |
SELECT id, b FROM t1 ORDER BY id; |
| 93 |
SELECT a, b FROM t2 ORDER BY a; |
| 94 |
|
| 95 |
DROP TABLE t2, t1; |
| 96 |
|
| 97 |
--echo # |
| 98 |
--echo # bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART |
| 99 |
--echo # base bug#24818604 [GR] |
| 100 |
--echo # |
| 101 |
|
| 102 |
CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=InnoDB; |
| 103 |
CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1)) |
| 104 |
ENGINE=InnoDB; |
| 105 |
|
| 106 |
INSERT INTO t1 VALUES (1); |
| 107 |
INSERT INTO t2 VALUES (1); |
| 108 |
|
| 109 |
SELECT unique_constraint_name FROM information_schema.referential_constraints |
| 110 |
WHERE table_name = 't2'; |
| 111 |
|
| 112 |
--echo # |
| 113 |
--echo # MDEV-28317 Assertion failure on rollback of FOREIGN KEY operation |
| 114 |
--echo # |
| 115 |
|
| 116 |
SET foreign_key_checks=0; |
| 117 |
CREATE TABLE parent(a INT PRIMARY KEY) ENGINE=InnoDB; |
| 118 |
CREATE TABLE child(a INT,FOREIGN KEY(a) REFERENCES parent(a) ON DELETE CASCADE) |
| 119 |
ENGINE=InnoDB; |
| 120 |
INSERT INTO child VALUES(1); |
| 121 |
ALTER TABLE child DROP INDEX a; |
| 122 |
|
| 123 |
connect(incomplete, localhost, root,,); |
| 124 |
BEGIN; |
| 125 |
DELETE FROM child; |
| 126 |
|
| 127 |
connection default; |
| 128 |
INSERT INTO parent SET a=0; |
| 129 |
FLUSH TABLES; |
| 130 |
|
| 131 |
--let $shutdown_timeout=0 |
| 132 |
--source include/restart_mysqld.inc |
| 133 |
--let $shutdown_timeout= |
| 134 |
disconnect incomplete; |
| 135 |
|
| 136 |
SET GLOBAL innodb_stats_persistent = 0; |
| 137 |
|
| 138 |
INSERT INTO child SET a=0; |
| 139 |
--error ER_NO_REFERENCED_ROW_2 |
| 140 |
INSERT INTO child SET a=1; |
| 141 |
--error ER_ROW_IS_REFERENCED_2 |
| 142 |
DELETE FROM parent; |
| 143 |
ALTER TABLE child ADD INDEX(a); |
| 144 |
DELETE FROM parent; |
| 145 |
DROP TABLE child,parent; |
| 146 |
|
| 147 |
SELECT unique_constraint_name FROM information_schema.referential_constraints |
| 148 |
WHERE table_name = 't2'; |
| 149 |
|
| 150 |
SELECT * FROM t1; |
| 151 |
|
| 152 |
SELECT unique_constraint_name FROM information_schema.referential_constraints |
| 153 |
WHERE table_name = 't2'; |
| 154 |
|
| 155 |
DROP TABLE t2; |
| 156 |
DROP TABLE t1; |
| 157 |
|
| 158 |
# |
| 159 |
# MDEV-12669 Circular foreign keys cause a loop and OOM upon LOCK TABLE |
| 160 |
# |
| 161 |
SET FOREIGN_KEY_CHECKS=0; |
| 162 |
CREATE TABLE staff ( |
| 163 |
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 164 |
store_id TINYINT UNSIGNED NOT NULL, |
| 165 |
PRIMARY KEY (staff_id), |
| 166 |
KEY idx_fk_store_id (store_id), |
| 167 |
CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE |
| 168 |
) ENGINE=InnoDB; |
| 169 |
CREATE TABLE store ( |
| 170 |
store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 171 |
manager_staff_id TINYINT UNSIGNED NOT NULL, |
| 172 |
PRIMARY KEY (store_id), |
| 173 |
UNIQUE KEY idx_unique_manager (manager_staff_id), |
| 174 |
CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE |
| 175 |
) ENGINE=InnoDB; |
| 176 |
|
| 177 |
LOCK TABLE staff WRITE; |
| 178 |
UNLOCK TABLES; |
| 179 |
DROP TABLES staff, store; |
| 180 |
|
| 181 |
SET FOREIGN_KEY_CHECKS=1; |
| 182 |
|
| 183 |
--echo # |
| 184 |
--echo # MDEV-17531 Crash in RENAME TABLE with FOREIGN KEY and FULLTEXT INDEX |
| 185 |
--echo # |
| 186 |
|
| 187 |
--disable_query_log |
| 188 |
call mtr.add_suppression("InnoDB: Table rename might cause two FOREIGN KEY"); |
| 189 |
call mtr.add_suppression("InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15\\."); |
| 190 |
--enable_query_log |
| 191 |
|
| 192 |
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; |
| 193 |
CREATE DATABASE best default character set latin1; |
| 194 |
CREATE TABLE t3 (a INT PRIMARY KEY, |
| 195 |
CONSTRAINT t2_ibfk_1 FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB; |
| 196 |
CREATE TABLE best.t2 (a INT PRIMARY KEY, b TEXT, FULLTEXT INDEX(b), |
| 197 |
FOREIGN KEY (a) REFERENCES test.t1(a)) ENGINE=InnoDB; |
| 198 |
--replace_regex /Table '.*t2'/Table 't2'/ |
| 199 |
--error ER_TABLE_EXISTS_ERROR |
| 200 |
RENAME TABLE best.t2 TO test.t2; |
| 201 |
SHOW CREATE TABLE best.t2; |
| 202 |
DROP DATABASE best; |
| 203 |
|
| 204 |
--echo # |
| 205 |
--echo # MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs |
| 206 |
--echo # |
| 207 |
connect (con1, localhost, root,,); |
| 208 |
INSERT INTO t1 SET a=1; |
| 209 |
BEGIN; |
| 210 |
DELETE FROM t1; |
| 211 |
|
| 212 |
connection default; |
| 213 |
let $ID= `SELECT @id := CONNECTION_ID()`; |
| 214 |
send INSERT INTO t3 SET a=1; |
| 215 |
|
| 216 |
connection con1; |
| 217 |
# Check that the above SELECT is blocked |
| 218 |
let $wait_condition= |
| 219 |
select count(*) = 1 from information_schema.processlist |
| 220 |
where state = 'update' and info = 'INSERT INTO t3 SET a=1'; |
| 221 |
--source include/wait_condition.inc |
| 222 |
let $ignore= `SELECT @id := $ID`; |
| 223 |
kill query @id; |
| 224 |
|
| 225 |
connection default; |
| 226 |
--error ER_QUERY_INTERRUPTED |
| 227 |
reap; |
| 228 |
|
| 229 |
connection con1; |
| 230 |
ROLLBACK; |
| 231 |
connection default; |
| 232 |
disconnect con1; |
| 233 |
|
| 234 |
DROP TABLE t3,t1; |
| 235 |
|
| 236 |
--echo # |
| 237 |
--echo # MDEV-18222 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N |
| 238 |
--echo # or ASAN heap-use-after-free in dict_foreign_remove_from_cache upon CHANGE COLUMN |
| 239 |
--echo # |
| 240 |
CREATE TABLE t1 (a INT, UNIQUE(a), KEY(a)) ENGINE=InnoDB; |
| 241 |
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (a); |
| 242 |
SET SESSION FOREIGN_KEY_CHECKS = OFF; |
| 243 |
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD |
| 244 |
ALTER TABLE t1 CHANGE COLUMN a a TIME NOT NULL; |
| 245 |
ALTER TABLE t1 ADD pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY; |
| 246 |
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON |
| 247 |
ALTER TABLE t1 CHANGE COLUMN a b TIME; |
| 248 |
ALTER TABLE t1 CHANGE COLUMN a b TIME, DROP FOREIGN KEY t1_ibfk_1; |
| 249 |
SET SESSION FOREIGN_KEY_CHECKS = ON; |
| 250 |
DROP TABLE t1; |
| 251 |
|
| 252 |
--echo # |
| 253 |
--echo # MDEV-18256 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N |
| 254 |
--echo # upon DROP FOREIGN KEY |
| 255 |
--echo # |
| 256 |
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; |
| 257 |
CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY fk1 (b) REFERENCES t1 (a)) |
| 258 |
ENGINE=InnoDB; |
| 259 |
--error ER_CANT_DROP_FIELD_OR_KEY |
| 260 |
ALTER TABLE t2 DROP FOREIGN KEY fk1, DROP FOREIGN KEY fk1; |
| 261 |
DROP TABLE t2, t1; |
| 262 |
|
| 263 |
CREATE TABLE t1 (f VARCHAR(256)) ENGINE=InnoDB; |
| 264 |
SET SESSION FOREIGN_KEY_CHECKS = OFF; |
| 265 |
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x); |
| 266 |
SET SESSION FOREIGN_KEY_CHECKS = ON; |
| 267 |
ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f); |
| 268 |
ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f); |
| 269 |
DROP TABLE t1; |
| 270 |
|
| 271 |
CREATE TABLE t1 (f VARCHAR(256), FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY) |
| 272 |
ENGINE=InnoDB; |
| 273 |
SET SESSION FOREIGN_KEY_CHECKS = OFF; |
| 274 |
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x); |
| 275 |
SET SESSION FOREIGN_KEY_CHECKS = ON; |
| 276 |
ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f); |
| 277 |
ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f); |
| 278 |
DROP TABLE t1; |
| 279 |
|
| 280 |
--echo # |
| 281 |
--echo # MDEV-18630 Conditional jump or move depends on uninitialised value |
| 282 |
--echo # in ib_push_warning / dict_create_foreign_constraints_low |
| 283 |
--echo # |
| 284 |
CREATE TABLE t1 (a INT) ENGINE=InnoDB; |
| 285 |
--error ER_CANT_CREATE_TABLE |
| 286 |
ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b); |
| 287 |
SHOW WARNINGS; |
| 288 |
DROP TABLE t1; |
| 289 |
|
| 290 |
--echo # |
| 291 |
--echo # MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error |
| 292 |
--echo # |
| 293 |
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f1)) ENGINE=InnoDB; |
| 294 |
CREATE TABLE t2 (f INT, KEY(f)) ENGINE=InnoDB; |
| 295 |
ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t2 (f); |
| 296 |
ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1); |
| 297 |
DROP TABLE t1, t2; |
| 298 |
|
| 299 |
# MDEV-19092 Server crash when renaming the column when |
| 300 |
# FOREIGN_KEY_CHECKS is disabled |
| 301 |
CREATE TABLE t1 (a INT, b INT, KEY idx(a)) ENGINE=InnoDB; |
| 302 |
SET FOREIGN_KEY_CHECKS= OFF; |
| 303 |
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES tx(x); |
| 304 |
ALTER TABLE t1 DROP KEY idx; |
| 305 |
ALTER TABLE t1 CHANGE a c INT; |
| 306 |
# Cleanup |
| 307 |
DROP TABLE t1; |
| 308 |
|
| 309 |
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY idx(f1)) ENGINE=InnoDB; |
| 310 |
ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1 (f1); |
| 311 |
ALTER TABLE t1 ADD COLUMN f INT; |
| 312 |
SET FOREIGN_KEY_CHECKS= OFF; |
| 313 |
ALTER TABLE t1 DROP KEY idx; |
| 314 |
ALTER TABLE t1 ADD KEY idx (f1); |
| 315 |
SET FOREIGN_KEY_CHECKS= ON; |
| 316 |
ALTER TABLE t1 DROP f3; |
| 317 |
ALTER TABLE t1 CHANGE f f3 INT; |
| 318 |
# Cleanup |
| 319 |
DROP TABLE t1; |
| 320 |
|
| 321 |
SET FOREIGN_KEY_CHECKS=1; |
| 322 |
|
| 323 |
--echo # |
| 324 |
--echo # Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE |
| 325 |
--echo # ADD FOREIGN KEY |
| 326 |
--echo # |
| 327 |
|
| 328 |
CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT, |
| 329 |
PRIMARY KEY (`department_id`)) engine=innodb; |
| 330 |
|
| 331 |
CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT, |
| 332 |
`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb; |
| 333 |
|
| 334 |
CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb; |
| 335 |
|
| 336 |
ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES |
| 337 |
`people` (`people_id`); |
| 338 |
|
| 339 |
ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people` |
| 340 |
(`people_id`); |
| 341 |
|
| 342 |
ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people` |
| 343 |
(`people_id`); |
| 344 |
|
| 345 |
drop table title, department, people; |
| 346 |
|
| 347 |
# |
| 348 |
# FK and prelocking: |
| 349 |
# child table accesses (reads and writes) wait for locks. |
| 350 |
# |
| 351 |
create table t1 (a int primary key, b int) engine=innodb; |
| 352 |
create table t2 (c int primary key, d int, |
| 353 |
foreign key (d) references t1 (a) on update cascade) engine=innodb; |
| 354 |
insert t1 values (1,1),(2,2),(3,3); |
| 355 |
insert t2 values (4,1),(5,2),(6,3); |
| 356 |
flush table t2 with read lock; # this takes MDL_SHARED_NO_WRITE |
| 357 |
connect (con1,localhost,root); |
| 358 |
--error ER_ROW_IS_REFERENCED_2 |
| 359 |
delete from t1 where a=2; |
| 360 |
send update t1 set a=10 where a=1; |
| 361 |
connection default; |
| 362 |
let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock'; |
| 363 |
source include/wait_condition.inc; |
| 364 |
unlock tables; |
| 365 |
connection con1; |
| 366 |
reap; |
| 367 |
connection default; |
| 368 |
lock table t2 write; # this takes MDL_SHARED_NO_READ_WRITE |
| 369 |
connection con1; |
| 370 |
send delete from t1 where a=2; |
| 371 |
connection default; |
| 372 |
let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock'; |
| 373 |
source include/wait_condition.inc; |
| 374 |
unlock tables; |
| 375 |
connection con1; |
| 376 |
--error ER_ROW_IS_REFERENCED_2 |
| 377 |
reap; |
| 378 |
connection default; |
| 379 |
unlock tables; |
| 380 |
disconnect con1; |
| 381 |
|
| 382 |
# but privileges should not be checked |
| 383 |
create user foo; |
| 384 |
grant select,update on test.t1 to foo; |
| 385 |
connect(foo,localhost,foo); |
| 386 |
update t1 set a=30 where a=3; |
| 387 |
disconnect foo; |
| 388 |
connection default; |
| 389 |
select * from t2; |
| 390 |
drop table t2, t1; |
| 391 |
drop user foo; |
| 392 |
|
| 393 |
--echo # |
| 394 |
--echo # MDEV-17595 - Server crashes in copy_data_between_tables or |
| 395 |
--echo # Assertion `thd->transaction.stmt.is_empty() || |
| 396 |
--echo # (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)' |
| 397 |
--echo # fails in close_tables_for_reopen upon concurrent |
| 398 |
--echo # ALTER TABLE and FLUSH |
| 399 |
--echo # |
| 400 |
CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB; |
| 401 |
INSERT INTO t1 VALUES(1),(2); |
| 402 |
CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB; |
| 403 |
INSERT INTO t2 VALUES(2); |
| 404 |
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE; |
| 405 |
DROP TABLE t2, t1; |
| 406 |
|
| 407 |
# |
| 408 |
# MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys |
| 409 |
# |
| 410 |
|
| 411 |
create table t1 (pk int primary key, data int) engine=innodb; |
| 412 |
insert t1 values (1,1),(2,2),(3,3); |
| 413 |
create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb; |
| 414 |
insert t2 values (1),(2); |
| 415 |
error ER_NO_REFERENCED_ROW_2; |
| 416 |
insert t2 values (10); |
| 417 |
flush tables; |
| 418 |
flush status; |
| 419 |
# with ON UPDATE RESTRICT child tables are not opened |
| 420 |
update t1 set data=10 where pk+1>10; |
| 421 |
show status like '%opened_tab%'; |
| 422 |
flush tables; |
| 423 |
flush status; |
| 424 |
# neither are parent tables |
| 425 |
update t2 set t1_pk=11 where t1_pk+1>10; |
| 426 |
show status like '%opened_tab%'; |
| 427 |
# under LOCK TABLES |
| 428 |
flush tables; |
| 429 |
flush status; |
| 430 |
lock tables t1 write; |
| 431 |
show status like '%opened_tab%'; |
| 432 |
insert t1 values (4,4); |
| 433 |
show status like '%opened_tab%'; |
| 434 |
unlock tables; |
| 435 |
delimiter |; |
| 436 |
create function foo() returns int |
| 437 |
begin |
| 438 |
insert t1 values (5,5); |
| 439 |
return 5; |
| 440 |
end| |
| 441 |
delimiter ;| |
| 442 |
flush tables; |
| 443 |
flush status; |
| 444 |
--disable_ps2_protocol |
| 445 |
select foo(); |
| 446 |
--enable_ps2_protocol |
| 447 |
show status like '%opened_tab%'; |
| 448 |
drop function foo; |
| 449 |
drop table t2, t1; |
| 450 |
|
| 451 |
CREATE TABLE t1 (pk INT, a INT, PRIMARY KEY (pk)) ENGINE=InnoDB; |
| 452 |
XA START 'xid'; |
| 453 |
INSERT INTO t1 VALUES (1,2); |
| 454 |
--error ER_XAER_RMFAIL |
| 455 |
CREATE TABLE x AS SELECT * FROM t1; |
| 456 |
--connect (con1,localhost,root,,test) |
| 457 |
SET foreign_key_checks= OFF, innodb_lock_wait_timeout= 0; |
| 458 |
SET lock_wait_timeout=2; |
| 459 |
--error ER_LOCK_WAIT_TIMEOUT |
| 460 |
ALTER TABLE t1 ADD FOREIGN KEY f (a) REFERENCES t1 (pk), LOCK=EXCLUSIVE;# Cleanup |
| 461 |
--disconnect con1 |
| 462 |
--connection default |
| 463 |
XA END 'xid'; |
| 464 |
XA ROLLBACK 'xid'; |
| 465 |
DROP TABLE t1; |
| 466 |
|
| 467 |
CREATE TABLE t1 (pk INT PRIMARY KEY, |
| 468 |
f1 VARCHAR(10), f2 VARCHAR(10), |
| 469 |
f3 VARCHAR(10), f4 VARCHAR(10), |
| 470 |
f5 VARCHAR(10), f6 VARCHAR(10), |
| 471 |
f7 VARCHAR(10), f8 VARCHAR(10), |
| 472 |
INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4), |
| 473 |
INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB; |
| 474 |
INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb', |
| 475 |
'mariadb', 'mariadb', 'mariadb', 'mariadb'), |
| 476 |
(2, 'mariadb', 'mariadb', 'mariadb', 'mariadb', |
| 477 |
'mariadb', 'mariadb', 'mariadb', 'mariadb'), |
| 478 |
(3, 'innodb', 'innodb', 'innodb', 'innodb', |
| 479 |
'innodb', 'innodb', 'innodb', 'innodb'); |
| 480 |
ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL; |
| 481 |
START TRANSACTION; |
| 482 |
DELETE FROM t1 where f1='mariadb'; |
| 483 |
SELECT * FROM t1; |
| 484 |
ROLLBACK; |
| 485 |
|
| 486 |
ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE; |
| 487 |
|
| 488 |
START TRANSACTION; |
| 489 |
DELETE FROM t1 where f3='mariadb'; |
| 490 |
SELECT * FROM t1; |
| 491 |
ROLLBACK; |
| 492 |
|
| 493 |
ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL; |
| 494 |
--error ER_ROW_IS_REFERENCED_2 |
| 495 |
UPDATE t1 SET f6='update'; |
| 496 |
|
| 497 |
ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE; |
| 498 |
--error ER_ROW_IS_REFERENCED_2 |
| 499 |
UPDATE t1 SET f6='cascade'; |
| 500 |
DROP TABLE t1; |
| 501 |
# |
| 502 |
# End of 10.1 tests |
| 503 |
# |
| 504 |
|
| 505 |
--echo # Start of 10.2 tests |
| 506 |
|
| 507 |
--echo # |
| 508 |
--echo # MDEV-13246 Stale rows despite ON DELETE CASCADE constraint |
| 509 |
--echo # |
| 510 |
|
| 511 |
CREATE TABLE users ( |
| 512 |
id int unsigned AUTO_INCREMENT PRIMARY KEY, |
| 513 |
name varchar(32) NOT NULL DEFAULT '' |
| 514 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 515 |
|
| 516 |
CREATE TABLE matchmaking_groups ( |
| 517 |
id bigint unsigned AUTO_INCREMENT PRIMARY KEY, |
| 518 |
host_user_id int unsigned NOT NULL UNIQUE, |
| 519 |
CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id) |
| 520 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 521 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 522 |
|
| 523 |
CREATE TABLE matchmaking_group_users ( |
| 524 |
matchmaking_group_id bigint unsigned NOT NULL, |
| 525 |
user_id int unsigned NOT NULL, |
| 526 |
PRIMARY KEY (matchmaking_group_id,user_id), |
| 527 |
UNIQUE KEY user_id (user_id), |
| 528 |
CONSTRAINT FOREIGN KEY (matchmaking_group_id) |
| 529 |
REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 530 |
CONSTRAINT FOREIGN KEY (user_id) |
| 531 |
REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE |
| 532 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 533 |
|
| 534 |
CREATE TABLE matchmaking_group_maps ( |
| 535 |
matchmaking_group_id bigint unsigned NOT NULL, |
| 536 |
map_id tinyint unsigned NOT NULL, |
| 537 |
PRIMARY KEY (matchmaking_group_id,map_id), |
| 538 |
CONSTRAINT FOREIGN KEY (matchmaking_group_id) |
| 539 |
REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE |
| 540 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 541 |
|
| 542 |
INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar'); |
| 543 |
INSERT INTO matchmaking_groups VALUES (10,1),(11,2); |
| 544 |
INSERT INTO matchmaking_group_users VALUES (10,1),(11,2); |
| 545 |
INSERT INTO matchmaking_group_maps VALUES (10,55),(11,66); |
| 546 |
|
| 547 |
BEGIN; |
| 548 |
UPDATE users SET name = 'qux' WHERE id = 1; |
| 549 |
|
| 550 |
connect (con1,localhost,root); |
| 551 |
--connection con1 |
| 552 |
SET innodb_lock_wait_timeout= 0; |
| 553 |
DELETE FROM matchmaking_groups WHERE id = 10; |
| 554 |
|
| 555 |
--connection default |
| 556 |
COMMIT; |
| 557 |
--sorted_result |
| 558 |
SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); |
| 559 |
--sorted_result |
| 560 |
SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); |
| 561 |
--sorted_result |
| 562 |
SELECT * FROM users; |
| 563 |
|
| 564 |
DROP TABLE |
| 565 |
matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users; |
| 566 |
|
| 567 |
--echo # |
| 568 |
--echo # MDEV-13331 FK DELETE CASCADE does not honor innodb_lock_wait_timeout |
| 569 |
--echo # |
| 570 |
|
| 571 |
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; |
| 572 |
|
| 573 |
CREATE TABLE t2 ( |
| 574 |
id INT NOT NULL PRIMARY KEY, |
| 575 |
ref_id INT NOT NULL DEFAULT 0, |
| 576 |
f INT NULL, |
| 577 |
FOREIGN KEY (ref_id) REFERENCES t1 (id) ON DELETE CASCADE |
| 578 |
) ENGINE=InnoDB; |
| 579 |
|
| 580 |
INSERT INTO t1 VALUES (1),(2); |
| 581 |
INSERT INTO t2 VALUES (1,1,10),(2,2,20); |
| 582 |
|
| 583 |
SHOW CREATE TABLE t2; |
| 584 |
|
| 585 |
--connection con1 |
| 586 |
BEGIN; |
| 587 |
UPDATE t2 SET f = 11 WHERE id = 1; |
| 588 |
|
| 589 |
--connection default |
| 590 |
SET innodb_lock_wait_timeout= 0; |
| 591 |
--error ER_LOCK_WAIT_TIMEOUT |
| 592 |
DELETE FROM t1 WHERE id = 1; |
| 593 |
SET innodb_lock_wait_timeout= 1; |
| 594 |
|
| 595 |
--connection con1 |
| 596 |
COMMIT; |
| 597 |
|
| 598 |
--connection default |
| 599 |
SELECT * FROM t2; |
| 600 |
DELETE FROM t1 WHERE id = 1; |
| 601 |
SELECT * FROM t2; |
| 602 |
DROP TABLE t2, t1; |
| 603 |
|
| 604 |
--echo # |
| 605 |
--echo # MDEV-15199 Referential integrity broken in ON DELETE CASCADE |
| 606 |
--echo # |
| 607 |
|
| 608 |
CREATE TABLE member (id int AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; |
| 609 |
INSERT INTO member VALUES (1); |
| 610 |
CREATE TABLE address ( |
| 611 |
id int AUTO_INCREMENT PRIMARY KEY, |
| 612 |
member_id int NOT NULL, |
| 613 |
KEY address_FI_1 (member_id), |
| 614 |
CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) |
| 615 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 616 |
) ENGINE=InnoDB; |
| 617 |
|
| 618 |
INSERT INTO address VALUES (2,1); |
| 619 |
CREATE TABLE payment_method ( |
| 620 |
id int AUTO_INCREMENT PRIMARY KEY, |
| 621 |
member_id int NOT NULL, |
| 622 |
cardholder_address_id int DEFAULT NULL, |
| 623 |
KEY payment_method_FI_1 (member_id), |
| 624 |
KEY payment_method_FI_2 (cardholder_address_id), |
| 625 |
CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 626 |
CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE |
| 627 |
) ENGINE=InnoDB; |
| 628 |
|
| 629 |
INSERT INTO payment_method VALUES (3,1,2); |
| 630 |
|
| 631 |
BEGIN; |
| 632 |
UPDATE member SET id=42; |
| 633 |
SELECT * FROM member; |
| 634 |
SELECT * FROM address; |
| 635 |
SELECT * FROM payment_method; |
| 636 |
DELETE FROM member; |
| 637 |
COMMIT; |
| 638 |
SELECT * FROM member; |
| 639 |
SELECT * FROM address; |
| 640 |
SELECT * FROM payment_method; |
| 641 |
|
| 642 |
DROP TABLE payment_method,address,member; |
| 643 |
|
| 644 |
--echo # |
| 645 |
--echo # Bug #26958695 INNODB NESTED STORED FIELD WITH CONSTRAINT KEY |
| 646 |
--echo # PRODUCE BROKEN TABLE (no bug in MariaDB) |
| 647 |
--echo # |
| 648 |
create table t1(f1 int,f2 int, primary key(f1), key(f2, f1))engine=innodb; |
| 649 |
create table t2(f1 int, f2 int as (2) stored, f3 int as (f2) stored, |
| 650 |
foreign key(f1) references t1(f2) on update set NULL) |
| 651 |
engine=innodb; |
| 652 |
insert into t1 values(1, 1); |
| 653 |
insert into t2(f1) values(1); |
| 654 |
drop table t2, t1; |
| 655 |
|
| 656 |
# |
| 657 |
# MDEV-12669 Circular foreign keys cause a loop and OOM upon LOCK TABLE |
| 658 |
# |
| 659 |
SET FOREIGN_KEY_CHECKS=0; |
| 660 |
CREATE TABLE staff ( |
| 661 |
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 662 |
store_id TINYINT UNSIGNED NOT NULL, |
| 663 |
PRIMARY KEY (staff_id), |
| 664 |
KEY idx_fk_store_id (store_id), |
| 665 |
CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE |
| 666 |
) ENGINE=InnoDB; |
| 667 |
CREATE TABLE store ( |
| 668 |
store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 669 |
manager_staff_id TINYINT UNSIGNED NOT NULL, |
| 670 |
PRIMARY KEY (store_id), |
| 671 |
UNIQUE KEY idx_unique_manager (manager_staff_id), |
| 672 |
CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE |
| 673 |
) ENGINE=InnoDB; |
| 674 |
|
| 675 |
LOCK TABLE staff WRITE; |
| 676 |
UNLOCK TABLES; |
| 677 |
DROP TABLES staff, store; |
| 678 |
SET FOREIGN_KEY_CHECKS=1; |
| 679 |
|
| 680 |
--echo # |
| 681 |
--echo # MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs |
| 682 |
--echo # |
| 683 |
|
| 684 |
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; |
| 685 |
CREATE TABLE t2 (a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES t1(a)) |
| 686 |
ENGINE=InnoDB; |
| 687 |
|
| 688 |
connection con1; |
| 689 |
INSERT INTO t1 SET a=1; |
| 690 |
BEGIN; |
| 691 |
DELETE FROM t1; |
| 692 |
|
| 693 |
connection default; |
| 694 |
let $ID= `SELECT @id := CONNECTION_ID()`; |
| 695 |
send INSERT INTO t2 SET a=1; |
| 696 |
|
| 697 |
connection con1; |
| 698 |
let $wait_condition= |
| 699 |
select count(*) = 1 from information_schema.processlist |
| 700 |
where state = 'update' and info = 'INSERT INTO t2 SET a=1'; |
| 701 |
--source include/wait_condition.inc |
| 702 |
let $ignore= `SELECT @id := $ID`; |
| 703 |
kill query @id; |
| 704 |
|
| 705 |
connection default; |
| 706 |
--error ER_QUERY_INTERRUPTED |
| 707 |
reap; |
| 708 |
|
| 709 |
connection con1; |
| 710 |
ROLLBACK; |
| 711 |
connection default; |
| 712 |
|
| 713 |
DROP TABLE t2,t1; |
| 714 |
|
| 715 |
--echo # |
| 716 |
--echo # MDEV-18272 InnoDB index corruption after failed DELETE CASCADE |
| 717 |
--echo # |
| 718 |
CREATE TABLE t1 ( |
| 719 |
pk TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| 720 |
a TINYINT UNSIGNED NOT NULL, b TINYINT UNSIGNED NOT NULL, KEY(b), |
| 721 |
CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE |
| 722 |
) ENGINE=InnoDB; |
| 723 |
|
| 724 |
INSERT INTO t1 (a,b) VALUES |
| 725 |
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), |
| 726 |
(0,1),(0,1),(1,0); |
| 727 |
connection con1; |
| 728 |
START TRANSACTION WITH CONSISTENT SNAPSHOT; |
| 729 |
|
| 730 |
connection default; |
| 731 |
DELETE IGNORE FROM t1 WHERE b = 1; |
| 732 |
|
| 733 |
SELECT a FROM t1 FORCE INDEX(a); |
| 734 |
# This would wrongly return the empty result if |
| 735 |
# the "goto rollback_to_savept" in row_mysql_handle_errors() is reverted. |
| 736 |
SELECT * FROM t1; |
| 737 |
# Allow purge to continue by closing the read view. |
| 738 |
connection con1; |
| 739 |
COMMIT; |
| 740 |
connection default; |
| 741 |
|
| 742 |
# Wait for purge. With the fix reverted, the server would crash here. |
| 743 |
--source include/wait_all_purged.inc |
| 744 |
CHECK TABLE t1; |
| 745 |
DROP TABLE t1; |
| 746 |
|
| 747 |
--echo # |
| 748 |
--echo # MDEV-17187 table doesn't exist in engine after ALTER other tables |
| 749 |
--echo # with CONSTRAINTs |
| 750 |
--echo # |
| 751 |
|
| 752 |
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."); |
| 753 |
|
| 754 |
set foreign_key_checks=on; |
| 755 |
create table t1 (id int not null primary key) engine=innodb; |
| 756 |
create table t2 (id int not null primary key, fid int not null, |
| 757 |
CONSTRAINT fk_fid FOREIGN KEY (fid) REFERENCES t1 (id))engine=innodb; |
| 758 |
|
| 759 |
insert into t1 values (1), (2), (3); |
| 760 |
insert into t2 values (1, 1), (2, 1), (3, 2); |
| 761 |
|
| 762 |
set foreign_key_checks=off; |
| 763 |
alter table t2 drop index fk_fid; |
| 764 |
set foreign_key_checks=on; |
| 765 |
|
| 766 |
--error ER_ROW_IS_REFERENCED_2 |
| 767 |
delete from t1 where id=2; |
| 768 |
--error ER_NO_REFERENCED_ROW_2 |
| 769 |
insert into t2 values(4, 99); |
| 770 |
|
| 771 |
select * from t1; |
| 772 |
select * from t2; |
| 773 |
|
| 774 |
set foreign_key_checks=off; |
| 775 |
delete from t1 where id=2; |
| 776 |
insert into t2 values(4, 99); |
| 777 |
set foreign_key_checks=on; |
| 778 |
|
| 779 |
select * from t1; |
| 780 |
select * from t2; |
| 781 |
|
| 782 |
show create table t1; |
| 783 |
show create table t2; |
| 784 |
|
| 785 |
# Optional: test DROP TABLE without any prior ha_innobase::open(). |
| 786 |
# This was tested manually, but it would cause --embedded to skip the test, |
| 787 |
# and the restart would significantly increase the running time. |
| 788 |
# --source include/restart_mysqld.inc |
| 789 |
|
| 790 |
--error ER_ROW_IS_REFERENCED_2 |
| 791 |
drop table t1,t2; |
| 792 |
--error ER_BAD_TABLE_ERROR |
| 793 |
drop table t1,t2; |
| 794 |
|
| 795 |
--echo # |
| 796 |
--echo # MDEV-22934 Table disappear after two alter table command |
| 797 |
--echo # |
| 798 |
CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT, |
| 799 |
f2 INT NOT NULL, |
| 800 |
PRIMARY KEY (f1), INDEX (f2))ENGINE=InnoDB; |
| 801 |
CREATE TABLE t2(f1 INT NOT NULL, |
| 802 |
f2 INT NOT NULL, f3 INT NOT NULL, |
| 803 |
PRIMARY KEY(f1, f2), UNIQUE KEY(f2), |
| 804 |
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE CASCADE, |
| 805 |
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (f1) REFERENCES t1(f1) ON DELETE CASCADE |
| 806 |
) ENGINE=InnoDB; |
| 807 |
|
| 808 |
SET FOREIGN_KEY_CHECKS=0; |
| 809 |
ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(f3), ALGORITHM=INPLACE; |
| 810 |
ALTER TABLE t2 DROP INDEX `f2`, ALGORITHM=COPY; |
| 811 |
SHOW CREATE TABLE t2; |
| 812 |
--error ER_TABLE_EXISTS_ERROR |
| 813 |
CREATE TABLE t2 (f1 INT NOT NULL)ENGINE=InnoDB; |
| 814 |
DROP TABLE t2, t1; |
| 815 |
|
| 816 |
--echo # |
| 817 |
--echo # MDEV-23685 SIGSEGV on ADD FOREIGN KEY after failed attempt |
| 818 |
--echo # to create unique key on virtual column |
| 819 |
--echo # |
| 820 |
CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, b INT AS (a)) ENGINE=InnODB; |
| 821 |
|
| 822 |
INSERT INTO t1 (pk,a) VALUES (1,10),(2,10); |
| 823 |
--error ER_DUP_ENTRY |
| 824 |
ALTER TABLE t1 ADD UNIQUE INDEX ind9 (b), LOCK=SHARED; |
| 825 |
SET FOREIGN_KEY_CHECKS= 0; |
| 826 |
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (pk); |
| 827 |
DROP TABLE t1; |
| 828 |
SET FOREIGN_KEY_CHECKS= 1; |
| 829 |
|
| 830 |
--echo # |
| 831 |
--echo # MDEV-23455 Hangs + Sig11 in unknown location(s) due to single complex FK query |
| 832 |
--echo # |
| 833 |
let $constr_prefix= aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; |
| 834 |
let $fk_ref= xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; |
| 835 |
let $fk_field= yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy; |
| 836 |
let $constr_count= 200; # each 100 constrs is 1 sec of test execution |
| 837 |
let $i= 0; |
| 838 |
|
| 839 |
while ($i < $constr_count) |
| 840 |
{ |
| 841 |
let $p= $constr_prefix$i; |
| 842 |
let $constr= CONSTRAINT $p FOREIGN KEY ($fk_field) REFERENCES t1($fk_ref) ON UPDATE SET NULL; |
| 843 |
if ($i) |
| 844 |
{ |
| 845 |
let $constrs= $constrs, $constr; |
| 846 |
} |
| 847 |
if (!$i) |
| 848 |
{ |
| 849 |
let $constrs= $constr; |
| 850 |
} |
| 851 |
inc $i; |
| 852 |
} |
| 853 |
--disable_query_log |
| 854 |
--echo Parsing foreign keys 1... |
| 855 |
--error ER_CANT_CREATE_TABLE |
| 856 |
eval create table t0($fk_field int, $constrs) engine innodb; |
| 857 |
--echo Parsing foreign keys 2... |
| 858 |
--error ER_CANT_CREATE_TABLE |
| 859 |
eval create table t1($fk_field int, $constrs) engine innodb; |
| 860 |
--echo Parsing foreign keys 3... |
| 861 |
--error ER_CANT_CREATE_TABLE |
| 862 |
eval create table t1($fk_ref int, $fk_field int, $constrs) engine innodb; |
| 863 |
--echo Parsing foreign keys 4... |
| 864 |
eval create table t1($fk_ref int primary key, $fk_field int, $constrs) engine innodb; |
| 865 |
drop table t1; |
| 866 |
--enable_query_log |
| 867 |
|
| 868 |
--echo # |
| 869 |
--echo # MDEV-27583 InnoDB uses different constants for FK cascade |
| 870 |
--echo # error message in SQL vs error log |
| 871 |
--echo # |
| 872 |
|
| 873 |
CREATE TABLE t1 |
| 874 |
(a INT, b INT, KEY(b), |
| 875 |
CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE) |
| 876 |
ENGINE=InnoDB; |
| 877 |
|
| 878 |
INSERT INTO t1 (a,b) VALUES |
| 879 |
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), |
| 880 |
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,1),(1,0); |
| 881 |
|
| 882 |
--error ER_GET_ERRMSG |
| 883 |
DELETE FROM t1 WHERE b = 1; |
| 884 |
SHOW WARNINGS; |
| 885 |
DROP TABLE t1; |
| 886 |
|
| 887 |
let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err; |
| 888 |
let SEARCH_PATTERN= InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15.*; |
| 889 |
-- source include/search_pattern_in_file.inc |
| 890 |
|
| 891 |
--echo # End of 10.2 tests |
| 892 |
|
| 893 |
--echo # |
| 894 |
--echo # MDEV-28980 InnoDB: Failing assertion: len <= MAX_TABLE_NAME_LEN |
| 895 |
--echo # |
| 896 |
|
| 897 |
SET NAMES utf8; |
| 898 |
CREATE TABLE t (a INT PRIMARY KEY) ENGINE=InnoDB; |
| 899 |
# The maximum identifier length is 64 characters. |
| 900 |
# my_charset_filename will expand some characters to 5 characters, |
| 901 |
# e.g., # to @0023. |
| 902 |
# Many operating systems (such as Linux) or file systems |
| 903 |
# limit the path component length to 255 bytes, such as 51*5 characters. |
| 904 |
# The bug was repeated with a shorter length, which we will use here, |
| 905 |
# to avoid exceeding MAX_PATH on Microsoft Windows. |
| 906 |
let $db=##########################; |
| 907 |
--replace_result $db db |
| 908 |
eval CREATE DATABASE `$db`; |
| 909 |
--replace_result $db db |
| 910 |
eval CREATE TABLE `$db`.u ( |
| 911 |
a INT PRIMARY KEY, |
| 912 |
CONSTRAINT `††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††` |
| 913 |
FOREIGN KEY (a) REFERENCES test.t (a)) ENGINE=InnoDB; |
| 914 |
--replace_result $db db |
| 915 |
eval DROP TABLE `$db`.u; |
| 916 |
--replace_result $db db |
| 917 |
eval DROP DATABASE `$db`; |
| 918 |
DROP TABLE t; |
| 919 |
|
| 920 |
--echo # End of 10.3 tests |
| 921 |
|
| 922 |
# MDEV-21792 Server aborts upon attempt to create foreign key on spatial field |
| 923 |
# Fail to create foreign key for spatial fields |
| 924 |
--error ER_CANT_CREATE_TABLE |
| 925 |
CREATE TABLE t1 (a GEOMETRY, INDEX(a(8)), |
| 926 |
FOREIGN KEY (a) REFERENCES x (xx)) ENGINE=InnoDB; |
| 927 |
|
| 928 |
--echo # |
| 929 |
--echo # MDEV-23675 Assertion `pos < table->n_def' in dict_table_get_nth_col |
| 930 |
--echo # |
| 931 |
CREATE TABLE t1 (pk int PRIMARY KEY, a INT, b INT, c INT, KEY(c), |
| 932 |
FOREIGN KEY fx (b) REFERENCES t1 (c)) |
| 933 |
ENGINE=InnoDB; |
| 934 |
INSERT INTO t1 VALUES (1,0,10,10); |
| 935 |
ALTER TABLE t1 DROP a, ALGORITHM=INSTANT; |
| 936 |
SET FOREIGN_KEY_CHECKS= 0; |
| 937 |
DROP INDEX fx ON t1; |
| 938 |
INSERT INTO t1 VALUES (2,11,11); |
| 939 |
DROP TABLE t1; |
| 940 |
SET FOREIGN_KEY_CHECKS=DEFAULT; |
| 941 |
|
| 942 |
--echo # |
| 943 |
--echo # MDEV-32018 Allow the setting of Auto_increment on FK referenced columns |
| 944 |
--echo # |
| 945 |
|
| 946 |
CREATE TABLE t1 ( |
| 947 |
id int unsigned NOT NULL PRIMARY KEY |
| 948 |
); |
| 949 |
|
| 950 |
CREATE TABLE t2 ( |
| 951 |
id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, |
| 952 |
t1_id int unsigned DEFAULT NULL, |
| 953 |
CONSTRAINT FK_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) |
| 954 |
); |
| 955 |
|
| 956 |
ALTER TABLE t1 MODIFY id INT unsigned AUTO_INCREMENT; |
| 957 |
|
| 958 |
DROP TABLE t1,t2; |
| 959 |
|
| 960 |
--echo # |
| 961 |
--echo # MDEV-31441 BLOB corruption on UPDATE of PRIMARY KEY with FOREIGN KEY |
| 962 |
--echo # |
| 963 |
|
| 964 |
CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT) ENGINE=InnoDB; |
| 965 |
CREATE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1(pk)) |
| 966 |
ENGINE=InnoDB; |
| 967 |
|
| 968 |
SET @blob = REPEAT('A', @@innodb_page_size / 2); |
| 969 |
INSERT INTO t1 SET pk=1, t=@blob; |
| 970 |
INSERT INTO t2 SET pk=1; |
| 971 |
--connection con1 |
| 972 |
BEGIN; |
| 973 |
DELETE FROM t2; |
| 974 |
--connection default |
| 975 |
# The following will be blocked by a FOREIGN KEY check on pk=1 in t2. |
| 976 |
--send |
| 977 |
UPDATE t1 SET pk=12; |
| 978 |
--connection con1 |
| 979 |
let $wait_condition= |
| 980 |
SELECT count(*) > 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE state='Updating'; |
| 981 |
--source include/wait_condition.inc |
| 982 |
COMMIT; |
| 983 |
--disconnect con1 |
| 984 |
--connection default |
| 985 |
--reap |
| 986 |
UPDATE t1 SET pk=1; |
| 987 |
SELECT pk,t=@blob FROM t1; |
| 988 |
DROP TABLE t2, t1; |
| 989 |
|
| 990 |
--echo # End of 10.4 tests |
| 991 |
|
| 992 |
--echo # |
| 993 |
--echo # MDEV-20729 Fix REFERENCES constraint in column definition |
| 994 |
--echo # |
| 995 |
set default_storage_engine= innodb; |
| 996 |
create table t1 (x int primary key, y int unique); |
| 997 |
create table t2 (x int references t1(x), y int constraint fk references t1(y)); |
| 998 |
show create table t2; |
| 999 |
create table t3 (z int); |
| 1000 |
alter table t3 add x int references t1(x), add y int constraint fk2 references t1(y); |
| 1001 |
show create table t3; |
| 1002 |
drop tables t3, t2, t1; |
| 1003 |
|
| 1004 |
create table t1 (id int primary key); |
| 1005 |
--error ER_CANT_CREATE_TABLE |
| 1006 |
create table t2 (id2 int references t1); |
| 1007 |
create table t2 (id int references t1); |
| 1008 |
show create table t2; |
| 1009 |
drop tables t2, t1; |
| 1010 |
|
| 1011 |
set default_storage_engine= default; |
| 1012 |
|
| 1013 |
--echo # |
| 1014 |
--echo # MDEV-21690 LeakSanitizer: detected memory leaks in mem_heap_create_block_func |
| 1015 |
--echo # |
| 1016 |
SET FOREIGN_KEY_CHECKS=1; |
| 1017 |
CREATE TABLE t1 (a TEXT, b TEXT) ENGINE=InnoDB; |
| 1018 |
--error ER_CANT_CREATE_TABLE |
| 1019 |
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b); |
| 1020 |
SET FOREIGN_KEY_CHECKS=DEFAULT; |
| 1021 |
|
| 1022 |
# Cleanup |
| 1023 |
DROP TABLE t1; |
| 1024 |
|
| 1025 |
--echo # |
| 1026 |
--echo # MDEV-22602 Disable UPDATE CASCADE for SQL constraints |
| 1027 |
--echo # |
| 1028 |
--echo # TODO: enable them after MDEV-16417 is finished |
| 1029 |
create or replace table t1 (a int primary key) engine=innodb; |
| 1030 |
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED |
| 1031 |
create or replace table t2 (a int, constraint foo check(a > 0), foreign key(a) references t1(a) on update cascade) engine=innodb; |
| 1032 |
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED |
| 1033 |
create or replace table t2 (a int, check(a > 0), foreign key(a) references t1(a) on update cascade) engine=innodb; |
| 1034 |
|
| 1035 |
create or replace table t1 (f1 int, f2 date, f3 date, key(f1,f3,f2)) engine=innodb; |
| 1036 |
--error ER_KEY_CANT_HAVE_WITHOUT_OVERLAPS |
| 1037 |
create or replace table t2 ( |
| 1038 |
a int, s date, e date, |
| 1039 |
period for p (s, e), |
| 1040 |
primary key (a, p without overlaps), |
| 1041 |
foreign key (a, e, s) references t1 (f1, f3, f2) on delete cascade on update cascade) engine=innodb; |
| 1042 |
|
| 1043 |
# FK on long unique is already disabled |
| 1044 |
create or replace table t1 (a varchar(4096) unique) engine=innodb; |
| 1045 |
--error ER_CANT_CREATE_TABLE |
| 1046 |
create or replace table t2 (pk int primary key, a varchar(4096) unique, foreign key(a) references t1(a) on update cascade) engine=innodb; |
| 1047 |
|
| 1048 |
drop table t1; |
| 1049 |
|
| 1050 |
--echo # |
| 1051 |
--echo # MDEV-26824 Can't add foreign key with empty referenced columns list |
| 1052 |
--echo # |
| 1053 |
create table t2(a int primary key) engine=innodb; |
| 1054 |
create table t1(a int primary key, b int) engine=innodb; |
| 1055 |
--error ER_WRONG_FK_DEF |
| 1056 |
alter table t2 add foreign key(a) references t1(a, b); |
| 1057 |
create or replace table t1(a tinyint primary key) engine innodb; |
| 1058 |
--error ER_CANT_CREATE_TABLE |
| 1059 |
alter table t2 add foreign key(a) references t1; |
| 1060 |
create or replace table t1(b int primary key) engine innodb; |
| 1061 |
--error ER_CANT_CREATE_TABLE |
| 1062 |
alter table t2 add foreign key(a) references t1; |
| 1063 |
create or replace table t1(a int primary key, b int) engine innodb; |
| 1064 |
alter table t2 add foreign key(a) references t1; |
| 1065 |
show create table t2; |
| 1066 |
drop tables t2, t1; |
| 1067 |
|
| 1068 |
|
| 1069 |
--echo # |
| 1070 |
--echo # MDEV-29182 Assertion fld->field_no < table->n_v_def failed on cascade |
| 1071 |
--echo # |
| 1072 |
CREATE TABLE t1(a INT PRIMARY KEY, b VARCHAR(3), c INT AS (LENGTH(b)) VIRTUAL, |
| 1073 |
INDEX(c)) ENGINE=InnoDB; |
| 1074 |
CREATE TABLE t2(a INT REFERENCES t1(a) ON UPDATE CASCADE, |
| 1075 |
b INT GENERATED ALWAYS AS(a) VIRTUAL, INDEX(b)) ENGINE=InnoDB; |
| 1076 |
INSERT INTO t1 SET a=1,b='fu'; |
| 1077 |
INSERT INTO t2 SET a=1; |
| 1078 |
UPDATE t1 SET a=2,b='bar'; |
| 1079 |
SELECT * FROM t1; |
| 1080 |
SELECT * FROM t2; |
| 1081 |
DROP TABLE t2,t1; |
| 1082 |
|
| 1083 |
--echo # End of 10.5 tests |
| 1084 |
|
| 1085 |
--echo # |
| 1086 |
--echo # MDEV-26554 Table-rebuilding DDL on parent table causes crash |
| 1087 |
--echo # for INSERT into child table |
| 1088 |
--echo # |
| 1089 |
|
| 1090 |
CREATE TABLE parent(a INT PRIMARY KEY) ENGINE=InnoDB; |
| 1091 |
CREATE TABLE child(a INT PRIMARY KEY REFERENCES parent(a)) ENGINE=InnoDB; |
| 1092 |
connect (con1, localhost, root,,); |
| 1093 |
BEGIN; |
| 1094 |
--error ER_NO_REFERENCED_ROW_2 |
| 1095 |
INSERT INTO child SET a=1; |
| 1096 |
connection default; |
| 1097 |
--error ER_TRUNCATE_ILLEGAL_FK |
| 1098 |
TRUNCATE TABLE parent; |
| 1099 |
--error ER_ROW_IS_REFERENCED_2 |
| 1100 |
DROP TABLE parent; |
| 1101 |
SET innodb_lock_wait_timeout=0; |
| 1102 |
--error ER_LOCK_WAIT_TIMEOUT |
| 1103 |
RENAME TABLE parent TO transparent; |
| 1104 |
--error ER_LOCK_WAIT_TIMEOUT |
| 1105 |
ALTER TABLE parent FORCE, ALGORITHM=COPY; |
| 1106 |
--error ER_LOCK_WAIT_TIMEOUT |
| 1107 |
ALTER TABLE parent FORCE, ALGORITHM=INPLACE; |
| 1108 |
SET innodb_lock_wait_timeout=0, foreign_key_checks=0; |
| 1109 |
--error ER_LOCK_WAIT_TIMEOUT |
| 1110 |
TRUNCATE TABLE parent; |
| 1111 |
--error ER_LOCK_WAIT_TIMEOUT |
| 1112 |
DROP TABLE parent; |
| 1113 |
--error ER_LOCK_WAIT_TIMEOUT |
| 1114 |
ALTER TABLE parent FORCE, ALGORITHM=COPY; |
| 1115 |
--error ER_LOCK_WAIT_TIMEOUT |
| 1116 |
ALTER TABLE parent FORCE, ALGORITHM=INPLACE; |
| 1117 |
--error ER_LOCK_WAIT_TIMEOUT |
| 1118 |
ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT; |
| 1119 |
connection con1; |
| 1120 |
COMMIT; |
| 1121 |
connection default; |
| 1122 |
# Restore the timeout to avoid occasional races with purge. |
| 1123 |
SET innodb_lock_wait_timeout=DEFAULT; |
| 1124 |
TRUNCATE TABLE parent; |
| 1125 |
ALTER TABLE parent FORCE, ALGORITHM=COPY; |
| 1126 |
ALTER TABLE parent FORCE, ALGORITHM=INPLACE; |
| 1127 |
ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT; |
| 1128 |
SET foreign_key_checks=ON; |
| 1129 |
--error ER_TRUNCATE_ILLEGAL_FK |
| 1130 |
TRUNCATE TABLE parent; |
| 1131 |
ALTER TABLE parent FORCE, ALGORITHM=COPY; |
| 1132 |
ALTER TABLE parent FORCE, ALGORITHM=INPLACE; |
| 1133 |
RENAME TABLE parent TO transparent; |
| 1134 |
DROP TABLE child, transparent; |
| 1135 |
|
| 1136 |
--echo # |
| 1137 |
--echo # MDEV-26217 Failing assertion: list.count > 0 in ut_list_remove |
| 1138 |
--echo # or Assertion `lock->trx == this' failed in dberr_t trx_t::drop_table |
| 1139 |
--echo # |
| 1140 |
|
| 1141 |
CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB; |
| 1142 |
INSERT INTO t1 VALUES (1); |
| 1143 |
|
| 1144 |
CREATE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY(pk) REFERENCES t1(pk)) |
| 1145 |
ENGINE=InnoDB; |
| 1146 |
|
| 1147 |
--connection con1 |
| 1148 |
SET FOREIGN_KEY_CHECKS=OFF; |
| 1149 |
--send |
| 1150 |
CREATE OR REPLACE TABLE t1 (b INT) ENGINE=InnoDB; |
| 1151 |
|
| 1152 |
--connection default |
| 1153 |
--error 0,ER_NO_REFERENCED_ROW_2,ER_LOCK_DEADLOCK |
| 1154 |
INSERT INTO t2 VALUES (1); |
| 1155 |
|
| 1156 |
--connection con1 |
| 1157 |
--error 0,ER_CANT_CREATE_TABLE |
| 1158 |
--reap |
| 1159 |
|
| 1160 |
# Cleanup |
| 1161 |
--connection default |
| 1162 |
--disable_warnings |
| 1163 |
DROP TABLE IF EXISTS t2, t1; |
| 1164 |
--enable_warnings |
| 1165 |
|
| 1166 |
--echo # |
| 1167 |
--echo # MDEV-30531 Corrupt index(es) on busy table when using FOREIGN KEY |
| 1168 |
--echo # |
| 1169 |
|
| 1170 |
CREATE TABLE collections ( |
| 1171 |
id int(11) unsigned NOT NULL AUTO_INCREMENT, |
| 1172 |
collectionhash varchar(255) NOT NULL DEFAULT '0', |
| 1173 |
PRIMARY KEY (id), |
| 1174 |
UNIQUE KEY ix_collection_collectionhash (collectionhash) |
| 1175 |
) ENGINE=InnoDB; |
| 1176 |
CREATE TABLE binaries ( |
| 1177 |
id bigint(20) unsigned NOT NULL AUTO_INCREMENT, |
| 1178 |
collections_id int(11) unsigned NOT NULL DEFAULT 0, |
| 1179 |
binaryhash binary(16) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', |
| 1180 |
PRIMARY KEY (id), |
| 1181 |
UNIQUE KEY ix_binary_binaryhash (binaryhash), |
| 1182 |
CONSTRAINT FK_collections FOREIGN KEY (collections_id) REFERENCES collections (id) ON DELETE CASCADE |
| 1183 |
) ENGINE=InnoDB; |
| 1184 |
|
| 1185 |
INSERT INTO collections (id) VALUES (NULL); |
| 1186 |
|
| 1187 |
--connection con1 |
| 1188 |
INSERT INTO binaries (id,collections_id) VALUES (NULL,1); |
| 1189 |
--send |
| 1190 |
REPLACE INTO collections (id) VALUES (NULL); |
| 1191 |
|
| 1192 |
--connection default |
| 1193 |
--error 0,ER_LOCK_DEADLOCK,ER_NO_REFERENCED_ROW_2 |
| 1194 |
REPLACE INTO binaries (id) VALUES (NULL); |
| 1195 |
|
| 1196 |
SET GLOBAL innodb_max_purge_lag_wait=0; |
| 1197 |
CHECK TABLE binaries, collections EXTENDED; |
| 1198 |
|
| 1199 |
--disconnect con1 |
| 1200 |
|
| 1201 |
# Cleanup |
| 1202 |
DROP TABLE binaries, collections; |
| 1203 |
|
| 1204 |
CREATE SCHEMA `#mysql50##mysql50#d-b`; |
| 1205 |
CREATE TABLE `#mysql50##mysql50#d-b`.t1 (a INT PRIMARY KEY, b INT UNIQUE) engine=InnoDB; |
| 1206 |
USE `#mysql50##mysql50#d-b`; |
| 1207 |
CREATE TABLE t2 (a INT PRIMARY KEY, b INT UNIQUE REFERENCES t1(b)) ENGINE=InnoDB; |
| 1208 |
SET STATEMENT foreign_key_checks=0 FOR |
| 1209 |
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a); |
| 1210 |
SHOW CREATE TABLE t2; |
| 1211 |
INSERT INTO t1 SET a=1; |
| 1212 |
INSERT INTO t2 SET a=1; |
| 1213 |
--error ER_ROW_IS_REFERENCED_2 |
| 1214 |
DELETE FROM t1; |
| 1215 |
DELETE FROM t2; |
| 1216 |
DELETE FROM t1; |
| 1217 |
DROP DATABASE `#mysql50##mysql50#d-b`; |
| 1218 |
USE test; |
| 1219 |
|
| 1220 |
--echo # |
| 1221 |
--echo # MDEV-35962 CREATE INDEX fails to heal a FOREIGN KEY constraint |
| 1222 |
--echo # |
| 1223 |
|
| 1224 |
--error ER_CANT_CREATE_TABLE |
| 1225 |
CREATE TABLE t2 (b INT, FOREIGN KEY (b) REFERENCES t1(a)) ENGINE=InnoDB; |
| 1226 |
SET STATEMENT foreign_key_checks=0 FOR |
| 1227 |
CREATE TABLE t2 (b INT, FOREIGN KEY (b) REFERENCES t1(a)) ENGINE=InnoDB; |
| 1228 |
--error ER_CANT_CREATE_TABLE |
| 1229 |
CREATE TABLE t1 (a INT) ENGINE=InnoDB; |
| 1230 |
SET STATEMENT foreign_key_checks=0 FOR |
| 1231 |
CREATE TABLE t1 (a INT) ENGINE=InnoDB; |
| 1232 |
INSERT INTO t1 VALUES (1); |
| 1233 |
--error ER_NO_REFERENCED_ROW_2 |
| 1234 |
INSERT INTO t2 VALUES (1); |
| 1235 |
ALTER TABLE t1 ADD KEY(a), ALGORITHM=NOCOPY; |
| 1236 |
INSERT INTO t2 VALUES (1); |
| 1237 |
--error ER_DROP_INDEX_FK |
| 1238 |
DROP INDEX b ON t2; |
| 1239 |
SET STATEMENT foreign_key_checks=0 FOR |
| 1240 |
DROP INDEX b ON t2; |
| 1241 |
DELETE FROM t2; |
| 1242 |
--error ER_ROW_IS_REFERENCED_2 |
| 1243 |
DELETE FROM t1; |
| 1244 |
ALTER TABLE t2 ADD KEY(b), ALGORITHM=NOCOPY; |
| 1245 |
DELETE FROM t1; |
| 1246 |
DROP TABLE t2, t1; |
| 1247 |
|
| 1248 |
--echo # |
| 1249 |
--echo # MDEV-33167 ASAN errors after failing to load foreign key |
| 1250 |
--echo # relation for the table |
| 1251 |
--echo # |
| 1252 |
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."); |
| 1253 |
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR |
| 1254 |
CREATE TABLE t1(f1 VARCHAR(8), |
| 1255 |
FOREIGN KEY(f1) REFERENCES test.t3(f1))ENGINE=InnoDB; |
| 1256 |
|
| 1257 |
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR |
| 1258 |
CREATE TABLE t2(f1 VARCHAR(8), |
| 1259 |
FOREIGN KEY(f1) REFERENCES test.t3(f1)) |
| 1260 |
ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; |
| 1261 |
|
| 1262 |
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR |
| 1263 |
CREATE TABLE t3(f1 VARCHAR(8) PRIMARY KEY) |
| 1264 |
ENGINE=InnoDB DEFAULT CHARSET=latin1; |
| 1265 |
|
| 1266 |
set GLOBAL innodb_fast_shutdown=0; |
| 1267 |
--let $shutdown_timeout= |
| 1268 |
--source include/restart_mysqld.inc |
| 1269 |
# Error encountered while loading the foreign key |
| 1270 |
# constraint for t3. t1 wasn't loaded into memory yet |
| 1271 |
# t2 failed to find index for foreign key relation |
| 1272 |
ALTER TABLE t2 FORCE; |
| 1273 |
DROP TABLE t2, t1, t3; |
| 1274 |
|
| 1275 |
--echo # |
| 1276 |
--echo # MDEV-19194 ASAN use-after-poison in |
| 1277 |
--echo # fk_prepare_copy_alter_table upon dropping FK |
| 1278 |
--echo # |
| 1279 |
CREATE TABLE t1(f1 INT NOT NULL, KEY(f1))ENGINE=InnoDB; |
| 1280 |
CREATE TABLE t2(f1 INT NOT NULL, |
| 1281 |
FOREIGN KEY `f1`(f1) REFERENCES t1(f1))ENGINE=InnoDB; |
| 1282 |
--error ER_CANT_DROP_FIELD_OR_KEY |
| 1283 |
ALTER TABLE t2 DROP FOREIGN KEY f1, DROP FOREIGN KEY f1, ALGORITHM=COPY; |
| 1284 |
--error ER_CANT_DROP_FIELD_OR_KEY |
| 1285 |
ALTER IGNORE TABLE t2 DROP FOREIGN KEY f0, DROP FOREIGN KEY f0, ALGORITHM=COPY; |
| 1286 |
--error ER_CANT_DROP_FIELD_OR_KEY |
| 1287 |
ALTER IGNORE TABLE t2 DROP FOREIGN KEY f0, ALGORITHM=COPY; |
| 1288 |
--error ER_CANT_DROP_FIELD_OR_KEY |
| 1289 |
ALTER IGNORE TABLE t2 DROP FOREIGN KEY f1, DROP FOREIGN KEY f1, ALGORITHM=COPY; |
| 1290 |
ALTER IGNORE TABLE t2 DROP FOREIGN KEY f1, DROP FOREIGN KEY IF EXISTS f1, ALGORITHM=COPY; |
| 1291 |
--error ER_CANT_DROP_FIELD_OR_KEY |
| 1292 |
ALTER TABLE t2 DROP FOREIGN KEY f1, ALGORITHM=COPY; |
| 1293 |
ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS f1, ALGORITHM=COPY; |
| 1294 |
--error ER_CANT_DROP_FIELD_OR_KEY |
| 1295 |
ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS f1, DROP FOREIGN KEY f1, ALGORITHM=COPY; |
| 1296 |
ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS f1, DROP FOREIGN KEY IF EXISTS f1, ALGORITHM=COPY; |
| 1297 |
DROP TABLE t2, t1; |
| 1298 |
|
| 1299 |
--echo # End of 10.6 tests |
| 1300 |
|
| 1301 |
CREATE TABLE t1 |
| 1302 |
( |
| 1303 |
f1 VARCHAR(32)BINARY NOT NULL, |
| 1304 |
f2 VARCHAR(32)BINARY NOT NULL, |
| 1305 |
PRIMARY KEY (f1), |
| 1306 |
INDEX(f2) |
| 1307 |
) ENGINE=InnoDB; |
| 1308 |
INSERT INTO t1 VALUES('MySQL', 'InnoDB'), ('MariaDB', 'NDB'); |
| 1309 |
|
| 1310 |
CREATE TABLE t2 |
| 1311 |
( |
| 1312 |
f1 VARCHAR(32)BINARY NOT NULL, |
| 1313 |
f2 VARCHAR(255)BINARY NOT NULL, |
| 1314 |
f3 int, PRIMARY KEY (f1), INDEX(f1), INDEX(f2) |
| 1315 |
) ENGINE=InnoDB; |
| 1316 |
INSERT INTO t2 VALUES('MySQL', 'MySQL', 1), |
| 1317 |
('MariaDB', 'MariaDB', 1); |
| 1318 |
ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t2 (f2); |
| 1319 |
# MDEV-33927 TODO: change the warning message |
| 1320 |
--error ER_FK_CANNOT_DELETE_PARENT |
| 1321 |
ALTER TABLE t2 ADD FOREIGN KEY (f2) REFERENCES t2 (f2), |
| 1322 |
ADD UNIQUE INDEX(f3); |
| 1323 |
drop table t1, t2; |
| 1324 |
|
| 1325 |
--echo # End of 10.11 tests |