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.

1 files changed, 1 insertions, 0 deletions

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;