MDEV-18507 can't update temporary table when joined with table with triggers on read-only

triggers are opened and tables used in triggers are prelocked in
open_tables(). But multi-update can detect what tables will actually
be updated only later, after all main tables are opened.

Meaning, if a table is used in multi-update, but is not actually updated,
its on-update treggers will be opened and tables will be prelocked,
even if it's unnecessary. This can cause more tables to be
write-locked than needed, causing read_only errors, privilege errors
and lock waits.

Fix: don't open/prelock triggers unless table->updating is true.
In multi-update after setting table->updating=true, do a second
open_tables() for newly added tables, if any.

7 files changed, 82 insertions, 7 deletions

diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 09df98c741e..e04637a3a54 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -1017,4 +1017,25 @@ execute stmt1;
 deallocate prepare stmt1;
 drop view v3,v2,v1;
 drop table t1,t2,t3;
+create table t1 (id int not null, v1 varchar(10) not null);
+insert into t1 values (1,1),(2,2);
+create table t2 (log varchar(10) not null);
+create trigger t1_after_update after update on t1
+for each row insert into t2 values ('triggered');
+create user foo;
+grant select, insert, update, delete, create, drop, reload, index, alter, show databases, create temporary tables, lock tables, execute, create view, show view, create routine, alter routine, trigger on *.* to 'foo'@'%';
+set global read_only=1;
+create temporary table temp_t1 (id int not null, update_me varchar(10));
+insert into temp_t1 values (1,1),(2,2),(3,3);
+update temp_t1 left join t1 on temp_t1.id = t1.id set temp_t1.update_me = 'hello';
+set global read_only = 0;
+create table t3 (id int not null);
+insert t3 values (2);
+update t1 left join t3 on t1.id = t3.id set t1.v1 = 'hello';
+select * from t2;
+log
+triggered
+triggered
+drop table t1,t2, t3;
+drop user foo;
 end of 5.5 tests
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 8184d8ded1a..964108b9b99 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -1055,5 +1055,36 @@ deallocate prepare stmt1;
 
 drop view v3,v2,v1;
 drop table t1,t2,t3;
+
+#
+# MDEV-18507 can't update temporary table when joined with table with triggers on read-only
+#
+create table t1 (id int not null, v1 varchar(10) not null);
+insert into t1 values (1,1),(2,2);
+create table t2 (log varchar(10) not null);
+create trigger t1_after_update after update on t1
+	for each row insert into t2 values ('triggered');
+
+create user foo;
+grant select, insert, update, delete, create, drop, reload, index, alter, show databases, create temporary tables, lock tables, execute, create view, show view, create routine, alter routine, trigger on *.* to 'foo'@'%';
+
+set global read_only=1;
+connect a, localhost, foo;
+
+create temporary table temp_t1 (id int not null, update_me varchar(10));
+insert into temp_t1 values (1,1),(2,2),(3,3);
+update temp_t1 left join t1 on temp_t1.id = t1.id set temp_t1.update_me = 'hello';
+
+connection default;
+set global read_only = 0;
+
+create table t3 (id int not null);
+insert t3 values (2);
+update t1 left join t3 on t1.id = t3.id set t1.v1 = 'hello';
+select * from t2;
+
+drop table t1,t2, t3;
+drop user foo;
+
 --echo end of 5.5 tests
 
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index fec7f51eaf0..e820cfd9d45 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -4250,6 +4250,7 @@ sp_head::add_used_tables_to_table_list(THD *thd,
       table->table_name_length= stab->table_name_length;
       table->alias= table->table_name + table->table_name_length + 1;
       table->lock_type= stab->lock_type;
+      table->updating= stab->lock_type >= TL_WRITE_ALLOW_WRITE;
       table->cacheable_table= 1;
       table->prelocking_placeholder= 1;
       table->belong_to_view= belong_to_view;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 9a2da7a8658..c774334e695 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -4484,7 +4484,7 @@ bool extend_table_list(THD *thd, TABLE_LIST *tables,
   LEX *lex= thd->lex;
 
   if (thd->locked_tables_mode <= LTM_LOCK_TABLES &&
-      ! has_prelocking_list &&
+      ! has_prelocking_list && tables->updating &&
       tables->lock_type >= TL_WRITE_ALLOW_WRITE)
   {
     bool need_prelocking= FALSE;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index b23c295a1af..c6da864c0a1 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1208,6 +1208,9 @@ int mysql_multi_update_prepare(THD *thd)
   List<Item> *fields= &lex->select_lex.item_list;
   table_map tables_for_update;
   bool update_view= 0;
+  DML_prelocking_strategy prelocking_strategy;
+  bool has_prelocking_list= thd->lex->requires_prelocking();
+
   /*
     if this multi-update was converted from usual update, here is table
     counter else junk will be assigned here, but then replaced with real
@@ -1228,10 +1231,10 @@ int mysql_multi_update_prepare(THD *thd)
     keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE
     and global read lock.
   */
-  if ((original_multiupdate &&
-       open_tables(thd, &table_list, &table_count,
-                   (thd->stmt_arena->is_stmt_prepare() ?
-                    MYSQL_OPEN_FORCE_SHARED_MDL : 0))) ||
+  if ((original_multiupdate && open_tables(thd, &table_list, &table_count,
+                                           thd->stmt_arena->is_stmt_prepare()
+                                           ? MYSQL_OPEN_FORCE_SHARED_MDL : 0,
+                                           &prelocking_strategy)) ||
       mysql_handle_derived(lex, DT_INIT))
     DBUG_RETURN(TRUE);
   /*
@@ -1278,6 +1281,9 @@ int mysql_multi_update_prepare(THD *thd)
   if (unsafe_key_update(lex->select_lex.leaf_tables, tables_for_update))
     DBUG_RETURN(true);
 
+  TABLE_LIST **new_tables= lex->query_tables_last;
+  DBUG_ASSERT(*new_tables== NULL);
+
   /*
     Setup timestamp handling and locking mode
   */
@@ -1308,6 +1314,8 @@ int mysql_multi_update_prepare(THD *thd)
       tl->updating= 1;
       if (tl->belong_to_view)
         tl->belong_to_view->updating= 1;
+      if (extend_table_list(thd, tl, &prelocking_strategy, has_prelocking_list))
+        DBUG_RETURN(TRUE);
     }
     else
     {
@@ -1328,6 +1336,19 @@ int mysql_multi_update_prepare(THD *thd)
         tl->set_lock_type(thd, read_lock_type_for_table(thd, lex, tl));
     }
   }
+
+  uint addon_table_count= 0;
+  if (*new_tables)
+  {
+    Sroutine_hash_entry **new_routines= thd->lex->sroutines_list.next;
+    DBUG_ASSERT(*new_routines == NULL);
+    if (open_tables(thd, new_tables, &addon_table_count, new_routines,
+                    thd->stmt_arena->is_stmt_prepare()
+                    ? MYSQL_OPEN_FORCE_SHARED_MDL : 0,
+                    &prelocking_strategy))
+      DBUG_RETURN(TRUE);
+  }
+
   for (tl= table_list; tl; tl= tl->next_local)
   {
     /* Check access privileges for table */
@@ -1360,7 +1381,7 @@ int mysql_multi_update_prepare(THD *thd)
 
   /* now lock and fill tables */
   if (!thd->stmt_arena->is_stmt_prepare() &&
-      lock_tables(thd, table_list, table_count, 0))
+      lock_tables(thd, table_list, table_count + addon_table_count, 0))
   {
     DBUG_RETURN(TRUE);
   }
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 5bd82fdd842..8f37c14c2d3 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -1511,6 +1511,7 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table,
         tbl->lock_type= table->lock_type;
         tbl->mdl_request.set_type((tbl->lock_type >= TL_WRITE_ALLOW_WRITE) ?
                                   MDL_SHARED_WRITE : MDL_SHARED_READ);
+        tbl->updating= table->updating;
       }
       /*
         If the view is mergeable, we might want to
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 9fd4cbcc26f..0da8c87ff93 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -13835,7 +13835,7 @@ table_lock:
           {
             thr_lock_type lock_type= (thr_lock_type) $3;
             bool lock_for_write= lock_type >= TL_WRITE_ALLOW_WRITE;
-            ulong table_options= 0;
+            ulong table_options= lock_for_write ? TL_OPTION_UPDATING : 0;
             enum_mdl_type mdl_type= !lock_for_write
                                     ? MDL_SHARED_READ
                                     : lock_type == TL_WRITE_CONCURRENT_INSERT