MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:kpettersson Date:July 27 2007 2:56pm
Subject:bk commit into 5.0 tree (thek:1.2479) BUG#29929
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of thek. When thek does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-07-27 16:56:29+02:00, thek@adventure.(none) +5 -0
  Bug #29929 LOCK TABLES does not pre-lock tables used in triggers of the locked tables
  
  When a table was explicitly locked with LOCK TABLES no associated
  tables from any related trigger on the subject table were locked.
  As a result of this the user could experience unexpected locking
  behavior and statement failures similar to "failed: 1100: Table'xx'
  was not locked with LOCK TABLES".
  
  This patch fixes this problem by making sure triggers are
  pre-loaded on any statement if the subject table was explicitly
  locked with LOCK TABLES.

  mysql-test/r/sp-prelocking.result@stripped, 2007-07-27 16:56:27+02:00, thek@adventure.(none) +30 -0
    Added test case

  mysql-test/t/sp-prelocking.test@stripped, 2007-07-27 16:56:28+02:00, thek@adventure.(none) +31 -0
    Added test case

  sql/sql_lex.cc@stripped, 2007-07-27 16:56:28+02:00, thek@adventure.(none) +132 -3
    - Moved some conditional logic out of the table iteration.
    - Added event map values for LOCK TABLE command.

  sql/table.cc@stripped, 2007-07-27 16:56:28+02:00, thek@adventure.(none) +0 -129
    - Refactored set_trg_event_tpye into the two simpler functions set_trg_event_map
      and set_trg_event_map as methods for manipulating the table event map.
      The original function was only called from st_lex::set_trg_event_type_for_tables
      so it was possible to move the event map creation logic to this function as
      a loop optimization.

  sql/table.h@stripped, 2007-07-27 16:56:28+02:00, thek@adventure.(none) +0 -1
    - Refactored set_trg_event_tpye into the two simpler functions set_trg_event_map
      and set_trg_event_map as methods for manipulating the table event map.
      The original function was only called from st_lex::set_trg_event_type_for_tables
      so it was possible to move the event map creation logic to this function as
      a loop optimization.

diff -Nrup a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result
--- a/mysql-test/r/sp-prelocking.result	2007-07-18 15:09:01 +02:00
+++ b/mysql-test/r/sp-prelocking.result	2007-07-27 16:56:27 +02:00
@@ -289,4 +289,34 @@ create table t1 select f_bug22427() as i
 ERROR 42S01: Table 't1' already exists
 drop table t1;
 drop function f_bug22427;
+#
+# Bug #29929 LOCK TABLES does not pre-lock tables used in triggers of the locked tables
+#
+DROP table IF EXISTS t1,t2;
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c2 INT);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
+BEGIN
+UPDATE t2 SET c2= c2 + 1;
+END//
+# Take a table lock on t1.
+# This should pre-lock t2 through the trigger.
+LOCK TABLE t1 WRITE;
+INSERT INTO t1 VALUES (3);
+UNLOCK TABLES;
+LOCK TABLE t1 READ;
+INSERT INTO t2 values(4);
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+UNLOCK TABLES;
+SELECT * FROM t1;
+c1
+1
+3
+SELECT * FROM t2;
+c2
+3
+DROP TRIGGER t1_ai;
+DROP TABLE t1, t2;
 End of 5.0 tests
diff -Nrup a/mysql-test/t/sp-prelocking.test b/mysql-test/t/sp-prelocking.test
--- a/mysql-test/t/sp-prelocking.test	2007-07-18 15:09:01 +02:00
+++ b/mysql-test/t/sp-prelocking.test	2007-07-27 16:56:28 +02:00
@@ -356,4 +356,35 @@ create table t1 select f_bug22427() as i
 drop table t1;
 drop function f_bug22427;
 
+--echo #
+--echo # Bug #29929 LOCK TABLES does not pre-lock tables used in triggers of the locked tables
+--echo #
+--disable_warnings
+DROP table IF EXISTS t1,t2;
+--enable_warnings
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c2 INT);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+DELIMITER //;
+CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
+BEGIN
+UPDATE t2 SET c2= c2 + 1;
+END//
+DELIMITER ;//
+--echo # Take a table lock on t1.
+--echo # This should pre-lock t2 through the trigger.
+LOCK TABLE t1 WRITE;
+INSERT INTO t1 VALUES (3);
+UNLOCK TABLES;
+LOCK TABLE t1 READ;
+--error ER_TABLE_NOT_LOCKED
+INSERT INTO t2 values(4);
+UNLOCK TABLES;
+SELECT * FROM t1;
+SELECT * FROM t2;
+DROP TRIGGER t1_ai;
+DROP TABLE t1, t2;
+
 --echo End of 5.0 tests
+
diff -Nrup a/sql/sql_lex.cc b/sql/sql_lex.cc
--- a/sql/sql_lex.cc	2007-07-12 20:26:38 +02:00
+++ b/sql/sql_lex.cc	2007-07-27 16:56:28 +02:00
@@ -2035,12 +2035,131 @@ void st_select_lex_unit::set_limit(SELEC
 
 
 /**
-  Update the parsed tree with information about triggers that
-  may be fired when executing this statement.
+  @brief 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.
+
+  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 st_lex::set_trg_event_type_for_tables()
 {
+  enum trg_event_type trg_event;
+
+  uint8 new_trg_event_map= 0;
+
+  /*
+    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.
+  */
+
+  switch (sql_command) {
+  case SQLCOM_LOCK_TABLES:
+  /*
+    On a LOCK TABLE, all triggers must be pre-loaded for this TABLE_LIST
+    when opening an associated TABLE.
+  */
+    new_trg_event_map= static_cast<uint8>
+                        (1 << static_cast<int>(TRG_EVENT_INSERT)) |
+                      static_cast<uint8>
+                        (1 << static_cast<int>(TRG_EVENT_UPDATE)) |
+                      static_cast<uint8>
+                        (1 << static_cast<int>(TRG_EVENT_DELETE));
+    break;
+  /*
+    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:
+    new_trg_event_map|= static_cast<uint8>
+                          (1 << static_cast<int>(TRG_EVENT_INSERT));
+    break;
+  /* Basic update and multi-update */
+  case SQLCOM_UPDATE:                           /* fall through */
+  case SQLCOM_UPDATE_MULTI:
+    new_trg_event_map|= static_cast<uint8>
+                          (1 << static_cast<int>(TRG_EVENT_UPDATE));
+    break;
+  /* Basic delete and multi-delete */
+  case SQLCOM_DELETE:                           /* fall through */
+  case SQLCOM_DELETE_MULTI:
+    new_trg_event_map|= static_cast<uint8>
+                          (1 << static_cast<int>(TRG_EVENT_DELETE));
+    break;
+  default:
+    break;
+  }
+
+  switch (duplicates) {
+  case DUP_UPDATE:
+    new_trg_event_map|= static_cast<uint8>
+                          (1 << static_cast<int>(TRG_EVENT_UPDATE));
+    break;
+  case DUP_REPLACE:
+    new_trg_event_map|= static_cast<uint8>
+                          (1 << static_cast<int>(TRG_EVENT_DELETE));
+    break;
+  case DUP_ERROR:
+  default:
+    break;
+  }
+
+
   /*
     Do not iterate over sub-selects, only the tables in the outermost
     SELECT_LEX can be modified, if any.
@@ -2049,7 +2168,17 @@ void st_lex::set_trg_event_type_for_tabl
 
   while (tables)
   {
-    tables->set_trg_event_type(this);
+    /*
+      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>(tables->lock_type) >=
+        static_cast<int>(TL_WRITE_ALLOW_WRITE))
+      tables->trg_event_map= new_trg_event_map;
     tables= tables->next_local;
   }
 }
diff -Nrup a/sql/table.cc b/sql/table.cc
--- a/sql/table.cc	2007-07-19 14:13:01 +02:00
+++ b/sql/table.cc	2007-07-27 16:56:28 +02:00
@@ -1776,135 +1776,6 @@ void st_table::reset_item_list(List<Item
   }
 }
 
-
-/**
-  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
 
diff -Nrup a/sql/table.h b/sql/table.h
--- a/sql/table.h	2007-07-12 20:26:39 +02:00
+++ b/sql/table.h	2007-07-27 16:56:28 +02:00
@@ -770,7 +770,6 @@ struct TABLE_LIST
   void reinit_before_use(THD *thd);
   Item_subselect *containing_subselect();
 
-  void set_trg_event_type(const st_lex *lex);
 private:
   bool prep_check_option(THD *thd, uint8 check_opt_type);
   bool prep_where(THD *thd, Item **conds, bool no_where_clause);
Thread
bk commit into 5.0 tree (thek:1.2479) BUG#29929kpettersson27 Jul