diff options
| author | Sergei Golubchik <serg@mariadb.org> | 2020-05-05 14:55:05 +0200 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2020-05-06 20:24:48 +0200 |
| commit | 0fcc3abf4a0b64932dbd5fb2a3a8d466c9a5f4a9 (patch) | |
| tree | 23e8c9f7566f83c2cfe5b3cb18e8d2ef8f5ec2db | |
| parent | 10aaa77509869153b998898d46943ec0949b5784 (diff) | |
| download | mariadb-git-0fcc3abf4a0b64932dbd5fb2a3a8d466c9a5f4a9.tar.gz | |
MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys
only MDL-prelock but do not open FK child tables for read-only (RESTRICT)
FK actions.
Tables still needs to be opened for CASCADE actions, see 9180e8666b8
| -rw-r--r-- | mysql-test/suite/innodb/r/foreign-keys.result | 21 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/foreign-keys.test | 23 | ||||
| -rw-r--r-- | sql/table.h | 3 |
3 files changed, 47 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/foreign-keys.result b/mysql-test/suite/innodb/r/foreign-keys.result index 10e0b1f3d7b..3778dd082c7 100644 --- a/mysql-test/suite/innodb/r/foreign-keys.result +++ b/mysql-test/suite/innodb/r/foreign-keys.result @@ -124,3 +124,24 @@ SET debug_sync='reset'; SHOW OPEN TABLES FROM test; Database Table In_use Name_locked DROP TABLE t1, t2; +create table t1 (pk int primary key, data int) engine=innodb; +insert t1 values (1,1),(2,2),(3,3); +create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb; +insert t2 values (1),(2); +insert t2 values (10); +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_pk`) REFERENCES `t1` (`pk`)) +flush tables; +flush status; +update t1 set data=10 where pk+1>10; +show status like '%opened_tab%'; +Variable_name Value +Opened_table_definitions 1 +Opened_tables 1 +flush tables; +flush status; +update t2 set t1_pk=11 where t1_pk+1>10; +show status like '%opened_tab%'; +Variable_name Value +Opened_table_definitions 1 +Opened_tables 1 +drop table t2, t1; diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test index a19fe3876f3..b3293e88676 100644 --- a/mysql-test/suite/innodb/t/foreign-keys.test +++ b/mysql-test/suite/innodb/t/foreign-keys.test @@ -158,3 +158,26 @@ connection default; SET debug_sync='reset'; SHOW OPEN TABLES FROM test; DROP TABLE t1, t2; + +# +# MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys +# +source include/have_innodb.inc; + +create table t1 (pk int primary key, data int) engine=innodb; +insert t1 values (1,1),(2,2),(3,3); +create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb; +insert t2 values (1),(2); +error ER_NO_REFERENCED_ROW_2; +insert t2 values (10); +flush tables; +flush status; +# with ON UPDATE RESTRICT child tables are not opened +update t1 set data=10 where pk+1>10; +show status like '%opened_tab%'; +flush tables; +flush status; +# neither are parent tables +update t2 set t1_pk=11 where t1_pk+1>10; +show status like '%opened_tab%'; +drop table t2, t1; diff --git a/sql/table.h b/sql/table.h index 44803b5aacd..29b4cfdbcf3 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1813,6 +1813,9 @@ struct TABLE_LIST open_type= routine ? OT_TEMPORARY_OR_BASE : OT_BASE_ONLY; belong_to_view= belong_to_view_arg; trg_event_map= trg_event_map_arg; + /* MDL is enough for read-only FK checks, we don't need the table */ + if (prelocking_placeholder == FK && lock_type < TL_WRITE_ALLOW_WRITE) + open_strategy= OPEN_STUB; **last_ptr= this; prev_global= *last_ptr; |