A fix and a test case for Bug#26141 mixing table types in trigger

causes full table lock on innodb table.
Also fixes Bug#28502 Triggers that update another innodb table 
will block on X lock unnecessarily (duplciate).
Code review fixes.

Both bugs' synopses are misleading: InnoDB table is
not X locked. The statements, however, cannot proceed concurrently, 
but this happens due to lock conflicts for tables used in triggers,
not for the InnoDB table. 

If a user had an InnoDB table, and two triggers, AFTER UPDATE and 
AFTER INSERT, competing for different resources (e.g. two distinct
MyISAM tables), then these two triggers would not be able to execute
concurrently. Moreover, INSERTS/UPDATES of the InnoDB table would
not be able to run concurrently. 
The problem had other side-effects (see respective bug reports).

This behavior was a consequence of a shortcoming of the pre-locking
algorithm, which would not distinguish between different DML operations
(e.g. INSERT and DELETE) and pre-lock all the tables
that are used by any trigger defined on the subject table.

The idea of the fix is to extend the pre-locking algorithm to keep track,
for each table, what DML operation it is used for and not
load triggers that are known to never be fired.


mysql-test/r/trigger-trans.result:
  Update results (Bug#26141)
mysql-test/r/trigger.result:
  Update results (Bug#28502)
mysql-test/t/trigger-trans.test:
  Add a test case for Bug#26141 mixing table types in trigger causes 
  full table lock on innodb table.
mysql-test/t/trigger.test:
  Add a test case for Bug#28502 Triggers that update another innodb 
  table will block echo on X lock unnecessarily. Add more test 
  coverage for triggers.
sql/item.h:
  enum trg_event_type is needed in table.h
sql/sp.cc:
  Take into account table_list->trg_event_map when determining
  what tables to pre-lock. 
  
  After this change, if we attempt to fire a 
  trigger for which we had not pre-locked any tables, error
  'Table was not locked with LOCK TABLES' will be printed.
  This, however, should never happen, provided the pre-locking
  algorithm has no programming bugs.
  
  Previously a trigger key in the sroutines hash was based on the name 
  of the table the trigger belongs to. This was possible because we would
  always add to the pre-locking list all the triggers defined for a table when
  handling this table.
  Now the key is based on the name of the trigger, owing
  to the fact that a trigger name must be unique in the database it
  belongs to.
sql/sp_head.cc:
  Generate sroutines hash key in init_spname(). This is a convenient
  place since there we have all the necessary information and can
  avoid an extra alloc.
  
  Maintain and merge trg_event_map when adding and merging elements
  of the pre-locking list.
sql/sp_head.h:
  Add ,m_sroutines_key member, used when inserting the sphead for a
  trigger into the cache of routines used by a statement.
  Previously the key was based on the table name the trigger belonged
  to, since for a given table we would add to the sroutines list
  all the triggers defined on it.
sql/sql_lex.cc:
  Introduce a new lex step: set_trg_event_type_for_tables().
  It is called when we have finished parsing but before opening
  and locking tables. Now this step is used to evaluate for each
  TABLE_LIST instance which INSERT/UPDATE/DELETE operation, if any,
  it is used in.
  In future this method could be extended to aggregate other information
  that is hard to aggregate during parsing.
sql/sql_lex.h:
  Add declaration for set_trg_event_type_for_tables().
sql/sql_parse.cc:
  Call set_trg_event_type_for_tables() after MYSQLparse(). Remove tabs.
sql/sql_prepare.cc:
  Call set_trg_event_type_for_tables() after  MYSQLparse().
sql/sql_trigger.cc:
  Call set_trg_event_type_for_tables() after MYSQLparse().
sql/sql_trigger.h:
  Remove an obsolete member.
sql/sql_view.cc:
  Call set_trg_event_type_for_tables() after MYSQLparse().
sql/sql_yacc.yy:
  Move assignment of sp_head::m_type before calling sp_head::init_spname(), 
  one is now used inside another.
sql/table.cc:
  Implement TABLE_LIST::set_trg_event_map() - a method that calculates
  wh triggers may be fired on this table when executing a statement.
sql/table.h:
  Add missing declarations.
  Move declaration of trg_event_type from item.h (it will be needed for 
  trg_event_map bitmap when we start using Bitmap template instead
  of uint8).

1 files changed, 129 insertions, 0 deletions

diff --git a/sql/table.cc b/sql/table.cc
index 899d0ab2ed0..9c3e7618aa0 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -1776,6 +1776,135 @@ void st_table::reset_item_list(List<Item> *item_list) const
   }
 }
 
+
+/**
+  Set the initial purpose of this TABLE_LIST object in the list of
+  used tables. We need to track this information on table-by-
+  table basis, since when this table becomes an element of the
+  pre-locked list, it's impossible to identify which SQL
+  sub-statement it has been originally used in.
+
+  E.g.:
+
+  User request:                 SELECT * FROM t1 WHERE f1();
+  FUNCTION f1():                DELETE FROM t2; RETURN 1;
+  BEFORE DELETE trigger on t2:  INSERT INTO t3 VALUES (old.a);
+
+  For this user request, the pre-locked list will contain t1, t2, t3
+  table elements, each needed for different DML.
+
+  This method is called immediately after parsing for tables
+  of the table list of the top-level select lex.
+
+  The trigger event map is updated to reflect INSERT, UPDATE, DELETE,
+  REPLACE, LOAD DATA, CREATE TABLE .. SELECT, CREATE TABLE ..
+  REPLACE SELECT statements, and additionally ON DUPLICATE KEY UPDATE
+  clause.
+*/
+
+void
+TABLE_LIST::set_trg_event_type(const st_lex *lex)
+{
+  enum trg_event_type trg_event;
+
+  /*
+    Some auxiliary operations
+    (e.g. GRANT processing) create TABLE_LIST instances outside
+    the parser. Additionally, some commands (e.g. OPTIMIZE) change
+    the lock type for a table only after parsing is done. Luckily,
+    these do not fire triggers and do not need to pre-load them.
+    For these TABLE_LISTs set_trg_event_type is never called, and
+    trg_event_map is always empty. That means that the pre-locking
+    algorithm will ignore triggers defined on these tables, if
+    any, and the execution will either fail with an assert in
+    sql_trigger.cc or with an error that a used table was not
+    pre-locked, in case of a production build.
+
+    TODO: this usage pattern creates unnecessary module dependencies
+    and should be rewritten to go through the parser.
+    Table list instances created outside the parser in most cases
+    refer to mysql.* system tables. It is not allowed to have
+    a trigger on a system table, but keeping track of
+    initialization provides extra safety in case this limitation
+    is circumvented.
+  */
+
+  /*
+    This is a fast check to filter out statements that do
+    not change data, or tables  on the right side, in case of
+    INSERT .. SELECT, CREATE TABLE .. SELECT and so on.
+    Here we also filter out OPTIMIZE statement and non-updateable
+    views, for which lock_type is TL_UNLOCK or TL_READ after
+    parsing.
+  */
+  if (static_cast<int>(lock_type) < static_cast<int>(TL_WRITE_ALLOW_WRITE))
+    return;
+
+  switch (lex->sql_command) {
+  /*
+    Basic INSERT. If there is an additional ON DUPLIATE KEY UPDATE
+    clause, it will be handled later in this method.
+  */
+  case SQLCOM_INSERT:                           /* fall through */
+  case SQLCOM_INSERT_SELECT:
+  /*
+    LOAD DATA ... INFILE is expected to fire BEFORE/AFTER INSERT
+    triggers.
+    If the statement also has REPLACE clause, it will be
+    handled later in this method.
+  */
+  case SQLCOM_LOAD:                             /* fall through */
+  /*
+    REPLACE is semantically equivalent to INSERT. In case
+    of a primary or unique key conflict, it deletes the old
+    record and inserts a new one. So we also may need to
+    fire ON DELETE triggers. This functionality is handled
+    later in this method.
+  */
+  case SQLCOM_REPLACE:                          /* fall through */
+  case SQLCOM_REPLACE_SELECT:
+  /*
+    CREATE TABLE ... SELECT defaults to INSERT if the table or
+    view already exists. REPLACE option of CREATE TABLE ...
+    REPLACE SELECT is handled later in this method.
+  */
+  case SQLCOM_CREATE_TABLE:
+    trg_event= TRG_EVENT_INSERT;
+    break;
+  /* Basic update and multi-update */
+  case SQLCOM_UPDATE:                           /* fall through */
+  case SQLCOM_UPDATE_MULTI:
+    trg_event= TRG_EVENT_UPDATE;
+    break;
+  /* Basic delete and multi-delete */
+  case SQLCOM_DELETE:                           /* fall through */
+  case SQLCOM_DELETE_MULTI:
+    trg_event= TRG_EVENT_DELETE;
+    break;
+  default:
+    /*
+      OK to return, since value of 'duplicates' is irrelevant
+      for non-updating commands.
+    */
+    return;
+  }
+  trg_event_map|= static_cast<uint8>(1 << static_cast<int>(trg_event));
+
+  switch (lex->duplicates) {
+  case DUP_UPDATE:
+    trg_event= TRG_EVENT_UPDATE;
+    break;
+  case DUP_REPLACE:
+    trg_event= TRG_EVENT_DELETE;
+    break;
+  case DUP_ERROR:
+  default:
+    return;
+  }
+  trg_event_map|= static_cast<uint8>(1 << static_cast<int>(trg_event));
+}
+
+
 /*
   calculate md5 of query