diff options
-rw-r--r-- | mysql-test/suite/innodb/r/foreign-keys.result | 28 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/foreign-keys.test | 21 | ||||
-rw-r--r-- | sql/sql_base.cc | 9 |
3 files changed, 57 insertions, 1 deletions
diff --git a/mysql-test/suite/innodb/r/foreign-keys.result b/mysql-test/suite/innodb/r/foreign-keys.result index 3778dd082c7..d76f01f7095 100644 --- a/mysql-test/suite/innodb/r/foreign-keys.result +++ b/mysql-test/suite/innodb/r/foreign-keys.result @@ -144,4 +144,32 @@ show status like '%opened_tab%'; Variable_name Value Opened_table_definitions 1 Opened_tables 1 +flush tables; +flush status; +lock tables t1 write; +show status like '%opened_tab%'; +Variable_name Value +Opened_table_definitions 2 +Opened_tables 2 +insert t1 values (4,4); +show status like '%opened_tab%'; +Variable_name Value +Opened_table_definitions 2 +Opened_tables 2 +unlock tables; +create function foo() returns int +begin +insert t1 values (5,5); +return 5; +end| +flush tables; +flush status; +select foo(); +foo() +5 +show status like '%opened_tab%'; +Variable_name Value +Opened_table_definitions 2 +Opened_tables 2 +drop function foo; 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 b3293e88676..9f1622d74cd 100644 --- a/mysql-test/suite/innodb/t/foreign-keys.test +++ b/mysql-test/suite/innodb/t/foreign-keys.test @@ -162,7 +162,6 @@ 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); @@ -180,4 +179,24 @@ flush status; # neither are parent tables update t2 set t1_pk=11 where t1_pk+1>10; show status like '%opened_tab%'; +# under LOCK TABLES +flush tables; +flush status; +lock tables t1 write; +show status like '%opened_tab%'; +insert t1 values (4,4); +show status like '%opened_tab%'; +unlock tables; +delimiter |; +create function foo() returns int +begin + insert t1 values (5,5); + return 5; +end| +delimiter ;| +flush tables; +flush status; +select foo(); +show status like '%opened_tab%'; +drop function foo; drop table t2, t1; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 1dbed8c55d1..c092faa986b 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5037,10 +5037,19 @@ bool Lock_tables_prelocking_strategy:: handle_table(THD *thd, Query_tables_list *prelocking_ctx, TABLE_LIST *table_list, bool *need_prelocking) { + TABLE_LIST **last= prelocking_ctx->query_tables_last; + if (DML_prelocking_strategy::handle_table(thd, prelocking_ctx, table_list, need_prelocking)) return TRUE; + /* + normally we don't need to open FK-prelocked tables for RESTRICT, + MDL is enough. But under LOCK TABLES we have to open everything + */ + for (TABLE_LIST *tl= *last; tl; tl= tl->next_global) + tl->open_strategy= TABLE_LIST::OPEN_NORMAL; + /* We rely on a caller to check that table is going to be changed. */ DBUG_ASSERT(table_list->lock_type >= TL_WRITE_ALLOW_WRITE); |