Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
Description
InnoDB should clean n_foreign_key_checks usage and assume that SQL is already holding
appropriate MDL. But an Insert into the child table doesn't seem to acquire MDL on the parent. When vice-versa is happening that there is MDL on the child when insert is happening
for the parent table.
The following test case could repeat the issue:
--source include/have_innodb.inc
|
create table t1(f1 int not null, primary key(f1))engine=innodb;
|
create table t2(f1 int not null primary key,
|
f2 int not null, foreign key(f2) references t1(f1))engine=innodb;
|
set DEBUG_SYNC='ib_after_row_insert_step SIGNAL alter WAIT_FOR dml';
|
--send insert into t1 values(1)
|
connect(con1,localhost,root,,,);
|
set DEBUG_SYNC="now WAIT_FOR alter";
|
alter table t2 force;
|
set DEBUG_SYNC="now SIGNAL dml";
|
connection default;
|
reap;
|
drop table t2, t1;
|
disconnect con1;
|
In above test case, ALTER TABLE t2 FORCE waits for MDL to open the table.
Another test case:
--source include/have_innodb.inc
|
create table t1(f1 int not null, primary key(f1))engine=innodb;
|
create table t2(f1 int not null primary key,
|
f2 int not null, foreign key(f2) references t1(f1))engine=innodb;
|
set DEBUG_SYNC='ib_after_row_insert_step SIGNAL alter WAIT_FOR dml';
|
--send insert into t2 values(1, 2)
|
connect(con1,localhost,root,,,);
|
set DEBUG_SYNC="now WAIT_FOR alter";
|
alter table t1 force;
|
set DEBUG_SYNC="now SIGNAL dml";
|
connection default;
|
reap;
|
drop table t2, t1;
|
disconnect con1;
|
In this case, alter waits at commit_inplace_alter_table(). That too for innodb table lock.
#6 0x00005555565df591 in lock_wait_suspend_thread (thr=0x7fffb8030768)
|
at /home/thiru/mariarepo/10.5/10.5-work/storage/innobase/lock/lock0wait.cc:351
|
#7 0x00005555566bb378 in row_mysql_handle_errors (new_err=0x7ffff01c3844,
|
trx=0x7ffff0cc3268, thr=0x7fffb8030768, savept=0x0)
|
at /home/thiru/mariarepo/10.5/10.5-work/storage/innobase/row/row0mysql.cc:740
|
#8 0x00005555565cd2f7 in lock_table_for_trx (table=0x7fffc41317e8,
|
trx=0x7ffff0cc3268, mode=LOCK_X)
|
at /home/thiru/mariarepo/10.5/10.5-work/storage/innobase/lock/lock0lock.cc:4070
|
#9 0x00005555566ad12d in row_merge_lock_table (trx=0x7ffff0cc3268,
|
table=0x7fffc41317e8, mode=LOCK_X)
|
at /home/thiru/mariarepo/10.5/10.5-work/storage/innobase/row/row0merge.cc:3698
|
#10 0x0000555556568d42 in ha_innobase::commit_inplace_alter_table (this=
|
0x7fffb8020c08, altered_table=0x7ffff01c4fb0,
|
ha_alter_info=0x7ffff01c4f20, commit=true)
|
at /home/thiru/mariarepo/10.5/10.5-work/storage/innobase/handler/handler0alter.cc:10699
|
#11 0x000055555611bf0a in handler::ha_commit_inplace_alter_table (
|
this=0x7fffb8020c08, altered_table=0x7ffff01c4fb0,
|
ha_alter_info=0x7ffff01c4f20, commit=true
|
First case is acquiring MDL on child table (un-necessary locking)
Second case should do MDL on parent table.
Attachments
Issue Links
- blocks
-
MDEV-21283 InnoDB: MySQL is trying to drop database…open handles to table …`#sql-ib1148`
- Closed
-
MDEV-25500 Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed in uint handler::get_dup_key(int)
- Closed
- is blocked by
-
MDEV-21602 CREATE TABLE…PRIMARY KEY…SELECT workaround causes DROP TABLE to ignore locks
- Closed
- relates to
-
MDEV-12483 Add foreign keys support for partitioned tables
- Stalled
-
MDEV-18421 Server crash or assertion `foreign->foreign_table != __null' failed in init_fts_doc_id_for_ref
- Stalled
-
MDEV-20612 Improve InnoDB lock_sys scalability
- Closed
-
MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys
- Closed
-
MDEV-23484 Rollback unnecessarily acquires dict_operation_lock for every row
- Closed
-
MDEV-25506 Atomic DDL: .frm file is removed and orphan InnoDB tablespace is left behind upon crash recovery
- Closed
-
MDEV-26554 Table-rebuilding DDL on parent table causes crash for INSERT into child table
- Closed
-
MDEV-33104 Assertion `table.get_ref_count() <= 1' failed in dberr_t trx_t::drop_table
- Closed
-
MDEV-16678 Use MDL for innodb background threads instead of dict_operation_lock
- Closed
-
MDEV-22788 SUMMARY: AddressSanitizer: heap-use-after-free storage/innobase/include/dict0dict.ic:1026 in dict_index_get_nth_field
- Confirmed
-
MDEV-24225 Long semaphore wait in dict0dict.cc line 943 and semaphore wait >600 sec kill
- Closed