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
-
Activity
Field | Original Value | New Value |
---|---|---|
Link | This issue relates to MDEV-12483 [ MDEV-12483 ] |
Link |
This issue relates to |
Fix Version/s | 10.5 [ 23123 ] |
Link |
This issue relates to |
Link |
This issue blocks |
Priority | Major [ 3 ] | Critical [ 2 ] |
Link |
This issue relates to |
Link |
This issue blocks |
Link | This issue causes MDEV-22457 [ MDEV-22457 ] |
Assignee | Sergei Golubchik [ serg ] | Marko Mäkelä [ marko ] |
Link | This issue causes MENT-773 [ MENT-773 ] |
Affects Version/s | 10.2 [ 14601 ] |
Fix Version/s | 10.2 [ 14601 ] |
Link |
This issue blocks |
Fix Version/s | 10.3 [ 22126 ] | |
Fix Version/s | 10.4 [ 22408 ] | |
Affects Version/s | 10.3 [ 22126 ] | |
Affects Version/s | 10.4 [ 22408 ] |
Status | Open [ 1 ] | In Progress [ 3 ] |
Status | In Progress [ 3 ] | Stalled [ 10000 ] |
Assignee | Marko Mäkelä [ marko ] | Sergei Golubchik [ serg ] |
Link | This issue relates to MDEV-22788 [ MDEV-22788 ] |
Link |
This issue relates to |
Fix Version/s | 10.2 [ 14601 ] | |
Fix Version/s | 10.3 [ 22126 ] | |
Fix Version/s | 10.4 [ 22408 ] |
Assignee | Sergei Golubchik [ serg ] | Marko Mäkelä [ marko ] |
Assignee | Marko Mäkelä [ marko ] | Sergei Golubchik [ serg ] |
Link |
This issue relates to |
Link | This issue relates to MDEV-18421 [ MDEV-18421 ] |
Link |
This issue blocks |
Assignee | Sergei Golubchik [ serg ] | Daniel Bartholomew [ dbart ] |
Assignee | Daniel Bartholomew [ dbart ] | Sergei Golubchik [ serg ] |
Priority | Critical [ 2 ] | Major [ 3 ] |
Link |
This issue is blocked by |
Link |
This issue relates to |
issue.field.resolutiondate | 2021-06-09 15:43:06.0 | 2021-06-09 15:43:06.984 |
Fix Version/s | 10.6.2 [ 25800 ] | |
Fix Version/s | 10.5 [ 23123 ] | |
Assignee | Sergei Golubchik [ serg ] | Marko Mäkelä [ marko ] |
Resolution | Fixed [ 1 ] | |
Status | Stalled [ 10000 ] | Closed [ 6 ] |
Labels | ServiceNow |
Labels | ServiceNow | 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z |
Labels | 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z |
Link |
This issue relates to |
Workflow | MariaDB v3 [ 101477 ] | MariaDB v4 [ 157028 ] |
Link |
This issue relates to |
Link |
This issue relates to |
Link |
This issue relates to |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36704 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36704 ] |
Zendesk Related Tickets | 201658 196904 109080 184471 | |
Zendesk active tickets | 201658 |