MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24

as well as

MDEV-19500 Update with join stopped worked if there is a call to a procedure in a trigger
MDEV-19521 Update Table Fails with Trigger and Stored Function
MDEV-19497 Replication stops because table not found
MDEV-19527 UPDATE + JOIN + TRIGGERS = table doesn't exists error

Reimplement the fix for (5d510fdbf00)

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

instead of calling open_tables() twice, put multi-update
prepare code inside open_tables() loop.

Add a test for a MDL backoff-and-retry loop inside open_tables()
across multi-update prepare code.

9 files changed, 231 insertions, 97 deletions

diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index e38d8737355..05fc619c50c 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -1038,6 +1038,24 @@ triggered
 triggered
 drop table t1,t2, t3;
 drop user foo;
+create table t1 (a int, b int);
+create table t2 (c int, d int);
+insert t1 values (1,2),(3,4);
+insert t2 values (5,6),(7,8);
+create table t0 (x int);
+insert t0 values (11), (22);
+create trigger tr1 before update on t2 for each row insert t0 values (new.c);
+lock table t0 write;
+update t1 join t2 on (a=c+4) set b=d;
+drop table t1, t2, t0;
+create table t1 (a int, b varchar(50), c varchar(50));
+insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3');
+create function f1() returns varchar(50) return 'result';
+create trigger tr before update on t1 for each row set new.c = (select f1());
+create table t2 select a, b from t1;
+update t1 join t2 using (a) set t1.b = t2.b;
+drop table t1, t2;
+drop function f1;
 #
 # end of 5.5 tests
 #
diff --git a/mysql-test/r/multi_update_debug.result b/mysql-test/r/multi_update_debug.result
new file mode 100644
index 00000000000..8bcd813e5b7
--- /dev/null
+++ b/mysql-test/r/multi_update_debug.result
@@ -0,0 +1,13 @@
+create table t1 (a int, b int);
+create table t2 (c int, d int);
+insert t1 values (1,2),(3,4);
+insert t2 values (5,6),(7,8);
+create table t0 (x int);
+insert t0 values (11), (22);
+create trigger tr1 before update on t1 for each row insert t0 values (new.b);
+set debug_sync='open_tables_after_open_and_process_table WAIT_FOR cont';
+update t1 join t2 on (a=c+4) set b=d;
+set debug_sync='mdl_acquire_lock_wait SIGNAL cont';
+lock table t1 write, t0 write;
+drop table t1, t2, t0;
+set debug_sync='reset';
diff --git a/mysql-test/r/multi_update_innodb.result b/mysql-test/r/multi_update_innodb.result
index 1d727f00e3e..5da8cf46f5a 100644
--- a/mysql-test/r/multi_update_innodb.result
+++ b/mysql-test/r/multi_update_innodb.result
@@ -67,6 +67,20 @@ SELECT * FROM t2;
 col_int_key	pk_1	pk_2	col_int
 1	2	3	4
 DROP TABLE t1,t2;
+create table t1 (id serial, size int(11)) engine=innodb;
+create table t2 (id serial, size int, account_id int) engine=innodb;
+create table t3 (id serial, size int, article_id int) engine=innodb;
+create table t4 (id serial, file_id int, article_id int) engine=innodb;
+insert t1 values(null, 400);
+insert t2 values(null, 0, 1), (null, 1, 1);
+insert t3 values(null, 100, 1);
+insert t4 values(null, 1, 2);
+create trigger file_update_article before update on t3 for each row
+update t2 set t2.size = new.size where t2.id = new.article_id;
+create trigger article_update_account before update on t2 for each row
+update t1 set t1.size = t1.size + new.size where t1.id = new.account_id;
+update t3 join t4 on t4.file_id =t3.id and t4.article_id=2 set t3.size=t3.size + 2;
+drop table t1, t2, t3, t4;
 #
 # end of 5.5 tests
 #
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 14c5574f61c..26943c347c0 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -1081,6 +1081,35 @@ select * from t2;
 drop table t1,t2, t3;
 drop user foo;
 
+#
+# Another test on not-opening tables unnecessary
+#
+create table t1 (a int, b int);
+create table t2 (c int, d int);
+insert t1 values (1,2),(3,4);
+insert t2 values (5,6),(7,8);
+create table t0 (x int);
+insert t0 values (11), (22);
+create trigger tr1 before update on t2 for each row insert t0 values (new.c);
+connect con1, localhost, root;
+lock table t0 write;
+connection default;
+update t1 join t2 on (a=c+4) set b=d;
+disconnect con1;
+drop table t1, t2, t0;
+
+#
+# MDEV-19521 Update Table Fails with Trigger and Stored Function
+#
+create table t1 (a int, b varchar(50), c varchar(50));
+insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3');
+create function f1() returns varchar(50) return 'result';
+create trigger tr before update on t1 for each row set new.c = (select f1());
+create table t2 select a, b from t1;
+update t1 join t2 using (a) set t1.b = t2.b;
+drop table t1, t2;
+drop function f1;
+
 --echo #
 --echo # end of 5.5 tests
 --echo #
diff --git a/mysql-test/t/multi_update_debug.test b/mysql-test/t/multi_update_debug.test
new file mode 100644
index 00000000000..2da376e1b87
--- /dev/null
+++ b/mysql-test/t/multi_update_debug.test
@@ -0,0 +1,27 @@
+#
+# test MDL backoff-and-retry during multi-update
+#
+source include/have_debug_sync.inc;
+create table t1 (a int, b int);
+create table t2 (c int, d int);
+insert t1 values (1,2),(3,4);
+insert t2 values (5,6),(7,8);
+create table t0 (x int);
+insert t0 values (11), (22);
+create trigger tr1 before update on t1 for each row insert t0 values (new.b);
+
+set debug_sync='open_tables_after_open_and_process_table WAIT_FOR cont';
+send update t1 join t2 on (a=c+4) set b=d;
+
+connect con1, localhost, root;
+let $wait_condition= select count(*) from information_schema.processlist where state = ' debug sync point: open_tables_after_open_and_process_table'
+source include/wait_condition.inc;
+set debug_sync='mdl_acquire_lock_wait SIGNAL cont';
+lock table t1 write, t0 write;
+let $wait_condition= select count(*) from information_schema.processlist where state = 'Waiting for table metadata lock'
+source include/wait_condition.inc;
+disconnect con1;
+connection default;
+reap;
+drop table t1, t2, t0;
+set debug_sync='reset';
diff --git a/mysql-test/t/multi_update_innodb.test b/mysql-test/t/multi_update_innodb.test
index 5807af4f489..9042f267c66 100644
--- a/mysql-test/t/multi_update_innodb.test
+++ b/mysql-test/t/multi_update_innodb.test
@@ -76,6 +76,24 @@ SELECT * FROM t2;
 
 DROP TABLE t1,t2;
 
+#
+# MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24
+#
+create table t1 (id serial, size int(11)) engine=innodb;
+create table t2 (id serial, size int, account_id int) engine=innodb;
+create table t3 (id serial, size int, article_id int) engine=innodb;
+create table t4 (id serial, file_id int, article_id int) engine=innodb;
+insert t1 values(null, 400);
+insert t2 values(null, 0, 1), (null, 1, 1);
+insert t3 values(null, 100, 1);
+insert t4 values(null, 1, 2);
+create trigger file_update_article before update on t3 for each row
+  update t2 set t2.size = new.size where t2.id = new.article_id;
+create trigger article_update_account before update on t2 for each row
+  update t1 set t1.size = t1.size + new.size where t1.id = new.account_id;
+update t3 join t4 on t4.file_id =t3.id and t4.article_id=2 set t3.size=t3.size + 2;
+drop table t1, t2, t3, t4;
+
 --echo #
 --echo # end of 5.5 tests
 --echo #
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index a98cfbbd5c0..27ee8957b25 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5055,6 +5055,7 @@ restart:
   sroutine_to_open= &thd->lex->sroutines_list.first;
   *counter= 0;
   thd_proc_info(thd, "Opening tables");
+  prelocking_strategy->reset(thd);
 
   /*
     If we are executing LOCK TABLES statement or a DDL statement
@@ -5218,6 +5219,8 @@ restart:
         }
       }
     }
+    if ((error= prelocking_strategy->handle_end(thd)))
+      goto err;
   }
 
   /*
diff --git a/sql/sql_base.h b/sql/sql_base.h
index ea92e880db7..439052a28f5 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -426,6 +426,7 @@ class Prelocking_strategy
 public:
   virtual ~Prelocking_strategy() { }
 
+  virtual void reset(THD *thd) { };
   virtual bool handle_routine(THD *thd, Query_tables_list *prelocking_ctx,
                               Sroutine_hash_entry *rt, sp_head *sp,
                               bool *need_prelocking) = 0;
@@ -433,6 +434,7 @@ public:
                             TABLE_LIST *table_list, bool *need_prelocking) = 0;
   virtual bool handle_view(THD *thd, Query_tables_list *prelocking_ctx,
                            TABLE_LIST *table_list, bool *need_prelocking)= 0;
+  virtual bool handle_end(THD *thd) { return 0; };
 };
 
 
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index ac066507042..e01fe0926dd 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1188,103 +1188,81 @@ bool unsafe_key_update(List<TABLE_LIST> leaves, table_map tables_for_update)
 }
 
 
-/*
-  make update specific preparation and checks after opening tables
+class Multiupdate_prelocking_strategy : public DML_prelocking_strategy
+{
+  bool done;
+  bool has_prelocking_list;
+public:
+  void reset(THD *thd);
+  bool handle_end(THD *thd);
+};
+
+void Multiupdate_prelocking_strategy::reset(THD *thd)
+{
+  done= false;
+  has_prelocking_list= thd->lex->requires_prelocking();
+}
 
-  SYNOPSIS
-    mysql_multi_update_prepare()
-    thd         thread handler
+/**
+  Determine what tables could be updated in the multi-update
 
-  RETURN
-    FALSE OK
-    TRUE  Error
+  For these tables we'll need to open triggers and continue prelocking
+  until all is open.
 */
-
-int mysql_multi_update_prepare(THD *thd)
+bool Multiupdate_prelocking_strategy::handle_end(THD *thd)
 {
+  DBUG_ENTER("Multiupdate_prelocking_strategy::handle_end");
+  if (done)
+    DBUG_RETURN(0);
+
   LEX *lex= thd->lex;
-  TABLE_LIST *table_list= lex->query_tables;
-  TABLE_LIST *tl;
-  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();
+  SELECT_LEX *select_lex= &lex->select_lex;
+  TABLE_LIST *table_list= lex->query_tables, *tl;
 
-  /*
-    if this multi-update was converted from usual update, here is table
-    counter else junk will be assigned here, but then replaced with real
-    count in open_tables()
-  */
-  uint  table_count= lex->table_count;
-  const bool using_lock_tables= thd->locked_tables_mode != LTM_NONE;
-  bool original_multiupdate= (thd->lex->sql_command == SQLCOM_UPDATE_MULTI);
-  DBUG_ENTER("mysql_multi_update_prepare");
+  done= true;
 
-  /* following need for prepared statements, to run next time multi-update */
-  thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
+  if (mysql_handle_derived(lex, DT_INIT) ||
+      mysql_handle_derived(lex, DT_MERGE_FOR_INSERT) ||
+      mysql_handle_derived(lex, DT_PREPARE))
+    DBUG_RETURN(1);
 
   /*
-    Open tables and create derived ones, but do not lock and fill them yet.
-
-    During prepare phase acquire only S metadata locks instead of SW locks to
-    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,
-                                           &prelocking_strategy)) ||
-      mysql_handle_derived(lex, DT_INIT))
-    DBUG_RETURN(TRUE);
-  /*
     setup_tables() need for VIEWs. JOIN::prepare() will call setup_tables()
     second time, but this call will do nothing (there are check for second
     call in setup_tables()).
   */
 
-  //We need to merge for insert prior to prepare.
-  if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT))
-    DBUG_RETURN(TRUE);
-  if (mysql_handle_derived(lex, DT_PREPARE))
-    DBUG_RETURN(TRUE);
-
-  if (setup_tables_and_check_access(thd, &lex->select_lex.context,
-                                    &lex->select_lex.top_join_list,
-                                    table_list, lex->select_lex.leaf_tables,
-                                    FALSE, UPDATE_ACL, SELECT_ACL, FALSE))
-    DBUG_RETURN(TRUE);
+  if (setup_tables_and_check_access(thd, &select_lex->context,
+        &select_lex->top_join_list, table_list, select_lex->leaf_tables,
+        FALSE, UPDATE_ACL, SELECT_ACL, FALSE))
+    DBUG_RETURN(1);
 
-  if (lex->select_lex.handle_derived(thd->lex, DT_MERGE))  
-    DBUG_RETURN(TRUE);
+  if (select_lex->handle_derived(thd->lex, DT_MERGE))
+    DBUG_RETURN(1);
 
+  List<Item> *fields= &lex->select_lex.item_list;
   if (setup_fields_with_no_wrap(thd, 0, *fields, MARK_COLUMNS_WRITE, 0, 0))
-    DBUG_RETURN(TRUE);
+    DBUG_RETURN(1);
 
   for (tl= table_list; tl ; tl= tl->next_local)
-  {
     if (tl->view)
     {
-      update_view= 1;
-      break;
+      if (check_fields(thd, *fields))
+        DBUG_RETURN(1);
+      else
+        break;
     }
-  }
 
-  if (update_view && check_fields(thd, *fields))
-    DBUG_RETURN(TRUE);
+  table_map tables_for_update= thd->table_map_for_update= get_table_map(fields);
 
-  thd->table_map_for_update= tables_for_update= get_table_map(fields);
-
-  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);
+  if (unsafe_key_update(select_lex->leaf_tables, tables_for_update))
+    DBUG_RETURN(1);
 
   /*
     Setup timestamp handling and locking mode
   */
-  List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables);
+  List_iterator<TABLE_LIST> ti(select_lex->leaf_tables);
+  const bool using_lock_tables= thd->locked_tables_mode != LTM_NONE;
   while ((tl= ti++))
   {
     TABLE *table= tl->table;
@@ -1299,7 +1277,7 @@ int mysql_multi_update_prepare(THD *thd)
       if (!tl->single_table_updatable() || check_key_in_view(thd, tl))
       {
         my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE");
-        DBUG_RETURN(TRUE);
+        DBUG_RETURN(1);
       }
 
       DBUG_PRINT("info",("setting table `%s` for update", tl->alias));
@@ -1310,8 +1288,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);
+      if (extend_table_list(thd, tl, this, has_prelocking_list))
+        DBUG_RETURN(1);
     }
     else
     {
@@ -1333,15 +1311,62 @@ int mysql_multi_update_prepare(THD *thd)
     }
   }
 
-  uint addon_table_count= 0;
-  if (*new_tables)
+  /* check single table update for view compound from several tables */
+  for (tl= table_list; tl; tl= tl->next_local)
   {
-    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))
+    TABLE_LIST *for_update= 0;
+    if (tl->is_merged_derived() &&
+        tl->check_single_table(&for_update, tables_for_update, tl))
+    {
+      my_error(ER_VIEW_MULTIUPDATE, MYF(0), tl->view_db.str, tl->view_name.str);
+      DBUG_RETURN(1);
+    }
+  }
+
+  DBUG_RETURN(0);
+}
+
+/*
+  make update specific preparation and checks after opening tables
+
+  SYNOPSIS
+    mysql_multi_update_prepare()
+    thd         thread handler
+
+  RETURN
+    FALSE OK
+    TRUE  Error
+*/
+
+int mysql_multi_update_prepare(THD *thd)
+{
+  LEX *lex= thd->lex;
+  TABLE_LIST *table_list= lex->query_tables;
+  TABLE_LIST *tl;
+  Multiupdate_prelocking_strategy prelocking_strategy;
+  uint table_count= lex->table_count;
+  DBUG_ENTER("mysql_multi_update_prepare");
+
+  /*
+    Open tables and create derived ones, but do not lock and fill them yet.
+
+    During prepare phase acquire only S metadata locks instead of SW locks to
+    keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE
+    and global read lock.
+  */
+  if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI)
+  {
+    if (open_tables(thd, &table_list, &table_count,
+        thd->stmt_arena->is_stmt_prepare() ? MYSQL_OPEN_FORCE_SHARED_MDL : 0,
+        &prelocking_strategy))
+      DBUG_RETURN(TRUE);
+  }
+  else
+  {
+    /* following need for prepared statements, to run next time multi-update */
+    thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
+    prelocking_strategy.reset(thd);
+    if (prelocking_strategy.handle_end(thd))
       DBUG_RETURN(TRUE);
   }
 
@@ -1358,24 +1383,9 @@ int mysql_multi_update_prepare(THD *thd)
     }
   }
 
-  /* check single table update for view compound from several tables */
-  for (tl= table_list; tl; tl= tl->next_local)
-  {
-    if (tl->is_merged_derived())
-    {
-      TABLE_LIST *for_update= 0;
-      if (tl->check_single_table(&for_update, tables_for_update, tl))
-      {
-	my_error(ER_VIEW_MULTIUPDATE, MYF(0),
-		 tl->view_db.str, tl->view_name.str);
-	DBUG_RETURN(-1);
-      }
-    }
-  }
-
   /* now lock and fill tables */
   if (!thd->stmt_arena->is_stmt_prepare() &&
-      lock_tables(thd, table_list, table_count + addon_table_count, 0))
+      lock_tables(thd, table_list, table_count, 0))
   {
     DBUG_RETURN(TRUE);
   }
@@ -1387,7 +1397,7 @@ int mysql_multi_update_prepare(THD *thd)
   */
   lex->select_lex.exclude_from_table_unique_test= TRUE;
   /* We only need SELECT privilege for columns in the values list */
-  ti.rewind();
+  List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables);
   while ((tl= ti++))
   {
     TABLE *table= tl->table;