Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.18, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.4, 11.5(EOL), 11.6(EOL), 11.7(EOL), 11.8
Description
When a group of tables have circular foreign key relationships, dropping those tables concurrently (with foreign_key_checks=OFF) can result in the DROP TABLE statements becoming blocked indefinitely. Both lock_wait_timeout and innodb_lock_wait_timeout get ignored in this state. Even using DROP TABLE ... NOWAIT makes no difference, the NOWAIT is effectively ignored and the DROP TABLE still blocks.
I can only repro this in MariaDB 10.6.18+ / May 2024 releases onwards, so I assume it is caused by MDEV-32899. Arguably this is a duplicate of MDEV-34253 but the circumstances are a bit different here, so I thought it might warrant a separate report.
The repro is inconsistent / non-deterministic, but running the pathological case below repeatedly in a loop seems to cause it quickly.
Setup:
SET foreign_key_checks = OFF; |
CREATE TABLE tbl0 ( |
id0 int unsigned not null, |
id1 int unsigned not null, |
id2 int unsigned not null, |
primary key (id0), |
foreign key fk01 (id1) references tbl1 (id1), |
foreign key fk02 (id2) references tbl2 (id2) |
) ENGINE=InnoDB;
|
CREATE TABLE tbl1 ( |
id1 int unsigned not null, |
id2 int unsigned not null, |
id3 int unsigned not null, |
primary key (id1), |
foreign key fk12 (id2) references tbl2 (id2), |
foreign key fk13 (id3) references tbl3 (id3) |
) ENGINE=InnoDB;
|
CREATE TABLE tbl2 ( |
id2 int unsigned not null, |
id3 int unsigned not null, |
id4 int unsigned not null, |
primary key (id2), |
foreign key fk23 (id3) references tbl3 (id3), |
foreign key fk24 (id4) references tbl4 (id4) |
) ENGINE=InnoDB;
|
CREATE TABLE tbl3 ( |
id3 int unsigned not null, |
id4 int unsigned not null, |
id5 int unsigned not null, |
primary key (id3), |
foreign key fk34 (id4) references tbl4 (id4), |
foreign key fk35 (id5) references tbl5 (id5) |
) ENGINE=InnoDB;
|
CREATE TABLE tbl4 ( |
id4 int unsigned not null, |
id5 int unsigned not null, |
id0 int unsigned not null, |
primary key (id4), |
foreign key fk45 (id5) references tbl5 (id5), |
foreign key fk40 (id0) references tbl0 (id0) |
) ENGINE=InnoDB;
|
CREATE TABLE tbl5 ( |
id5 int unsigned not null, |
id0 int unsigned not null, |
id1 int unsigned not null, |
primary key (id5), |
foreign key fk50 (id0) references tbl0 (id0), |
foreign key fk51 (id1) references tbl1 (id1) |
) ENGINE=InnoDB;
|
Then using 6 separate connections, concurrently drop the tables:
SET foreign_key_checks = OFF, lock_wait_timeout = 5, innodb_lock_wait_timeout = 5; |
DROP TABLE tbl{$n}; -- optionally add NOWAIT to demonstrate that doesn't help... |
Attachments
Issue Links
- is caused by
-
-
- Closed
-
- relates to
-
-
- Confirmed
-