List:Commits« Previous MessageNext Message »
From:Dmitry Shulga Date:May 27 2011 11:31am
Subject:bzr push into mysql-5.1 branch (Dmitry.Shulga:3631 to 3632) Bug#12546938
View as plain text  
 3632 Dmitry Shulga	2011-05-27
      Fixed bug#12546938 (formerly known as 61005) - CREATE IF NOT EXIST EVENT
      will create multiple running events.
      
      A CREATE IF NOT EXIST on an event that existed and was enabled caused
      multiple instances of the event to run. Disabling the event didn't  help.
      If the event was  dropped, the event stopped running, but when created
      again, multiple instances of the event were still running. The only way
      to get out of this situation was  to restart the server.
      
      The problem was that Event_db_repository::create_event() didn't return
      enough information to discriminate between situation when event didn't
      exist and was created and when event did exist and was not created
      (but a warning was emitted). As result in the latter case event
      was added to in-memory queue of events second time. And this led to
      unwarranted multiple executions of the same event.
      
      The solution is to add out-parameter to Event_db_repository::create_event()
      method which will signal that event was not created because it already
      exists and so it should not be added to the in-memory queue.
     @ mysql-test/r/events_bugs.result
        Added results for test for Bug#12546938.
     @ mysql-test/t/events_bugs.test
        Added test for Bug#12546938.
     @ sql/event_db_repository.cc
        Event_db_repository::create_event was modified: set newly added out-parameter
        event_already_exists to true value if event wasn't created because event
        already existed and IF NOT EXIST clause was present.
     @ sql/event_db_repository.h
        Added out-parameter 'event_already_exists' to create_event() method.
     @ sql/events.cc
        Events::create_event was modified: insert new element into
        event queue only if event was actually created.

    modified:
      mysql-test/r/events_bugs.result
      mysql-test/t/events_bugs.test
      sql/event_db_repository.cc
      sql/event_db_repository.h
      sql/events.cc
 3631 Dmitry Lenev	2011-05-26
      Fix for bug #11762012 - "54553: INNODB ASSERTS IN 
      HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK".
      
      Attempt to update an InnoDB temporary table under LOCK TABLES
      led to assertion failure in both debug and production builds
      if this temporary table was explicitly locked for READ. The 
      same scenario works fine for MyISAM temporary tables.
      
      The assertion failure was caused by discrepancy between lock 
      that was requested on the rows of temporary table at LOCK TABLES
      time and by update operation. Since SQL-layer requested a 
      read-lock at LOCK TABLES time InnoDB engine assumed that upcoming
      statements which are going to be executed under LOCK TABLES will 
      only read table and therefore should acquire only S-lock.
      An update operation broken this assumption by requesting X-lock.
      
      Possible approaches to fixing this problem are:
      
      1) Skip locking of temporary tables as locking doesn't make any
         sense for connection-local objects.
      2) Prohibit changing of temporary table locked by LOCK TABLES ... 
         READ.
      
      Unfortunately both of these approaches have drawbacks which make 
      them unviable for stable versions of server.
      
      So this patch takes another approach and changes code in such way
      that LOCK TABLES for a temporary table will always request write
      lock. In 5.1 version of this patch switch from read lock to write
      lock is done inside of InnoDBs handler methods as doing it on 
      SQL-layer causes compatibility troubles with FLUSH TABLES WITH
      READ LOCK.
     @ mysql-test/suite/innodb/r/innodb_mysql.result
        Added test for bug #11762012 - "54553: INNODB ASSERTS IN 
        HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK".
     @ mysql-test/suite/innodb/t/innodb_mysql.test
        Added test for bug #11762012 - "54553: INNODB ASSERTS IN 
        HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK".
     @ mysql-test/suite/innodb_plugin/r/innodb_mysql.result
        Added test for bug #11762012 - "54553: INNODB ASSERTS IN 
        HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK".
     @ mysql-test/suite/innodb_plugin/t/innodb_mysql.test
        Added test for bug #11762012 - "54553: INNODB ASSERTS IN 
        HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK".
     @ storage/innobase/handler/ha_innodb.cc
        Assume that a temporary table locked by LOCK TABLES can be updated
        even if it was only locked for read and therefore an X-lock should 
        be always requested for such tables.
     @ storage/innodb_plugin/handler/ha_innodb.cc
        Assume that a temporary table locked by LOCK TABLES can be updated
        even if it was only locked for read and therefore an X-lock should 
        be always requested for such tables.

    modified:
      mysql-test/suite/innodb/r/innodb_mysql.result
      mysql-test/suite/innodb/t/innodb_mysql.test
      mysql-test/suite/innodb_plugin/r/innodb_mysql.result
      mysql-test/suite/innodb_plugin/t/innodb_mysql.test
      storage/innobase/handler/ha_innodb.cc
      storage/innodb_plugin/handler/ha_innodb.cc
=== modified file 'mysql-test/r/events_bugs.result'
--- a/mysql-test/r/events_bugs.result	2011-03-28 15:31:37 +0000
+++ b/mysql-test/r/events_bugs.result	2011-05-27 09:23:08 +0000
@@ -535,6 +535,7 @@ DROP EVENT e3;
 DROP EVENT e2;
 DROP EVENT e1;
 SET TIME_ZONE=@save_time_zone;
+SET TIMESTAMP=DEFAULT;
 drop event if exists new_event;
 CREATE EVENT new_event ON SCHEDULE EVERY 0 SECOND DO SELECT 1;
 ERROR HY000: INTERVAL is either not positive or too big
@@ -756,6 +757,45 @@ SHOW EVENTS;
 Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
 DROP DATABASE event_test1;
 DROP DATABASE event_test12;
+#
+# Bug#12546938 (formerly known as bug#61005):
+# CREATE IF NOT EXIST EVENT WILL CREATE MULTIPLE RUNNING EVENTS
+#
+USE events_test;
+SET GLOBAL event_scheduler = ON;
+DROP TABLE IF EXISTS table_bug12546938;
+DROP EVENT IF EXISTS event_Bug12546938;
+CREATE TABLE table_bug12546938 (i INT);
+# Create an event which will be executed with a small delay
+# and won't be automatically dropped after that.
+CREATE EVENT event_Bug12546938
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE
+ENABLE DO
+BEGIN 
+INSERT INTO table_bug12546938 VALUES(1);
+END
+|
+# Now try to create the same event using CREATE EVENT IF NOT EXISTS.
+# A warning should be emitted. A new event should not be created nor
+# the old event should be re-executed.
+CREATE EVENT IF NOT EXISTS event_bug12546938
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE
+ENABLE DO
+BEGIN
+INSERT INTO table_bug12546938 VALUES (1);
+END
+|
+Warnings:
+Note	1537	Event 'event_bug12546938' already exists
+# Wait until at least one instance of event is executed.
+# Check that only one instance of our event was executed.
+SELECT COUNT(*) FROM table_bug12546938;
+COUNT(*)
+1
+# Clean-up.
+DROP EVENT IF EXISTS event_Bug12546938;
+DROP TABLE table_bug12546938;
+SET GLOBAL EVENT_SCHEDULER = OFF;
 DROP DATABASE events_test;
 SET GLOBAL event_scheduler= 'ON';
 SET @@global.concurrent_insert= @concurrent_insert;

=== modified file 'mysql-test/t/events_bugs.test'
--- a/mysql-test/t/events_bugs.test	2011-03-28 15:31:37 +0000
+++ b/mysql-test/t/events_bugs.test	2011-05-27 09:23:08 +0000
@@ -857,6 +857,7 @@ DROP EVENT e2;
 DROP EVENT e1;
 
 SET TIME_ZONE=@save_time_zone;
+SET TIMESTAMP=DEFAULT;
 
 #
 # START - BUG#28666 CREATE EVENT ... EVERY 0 SECOND let server crash
@@ -1235,6 +1236,55 @@ SHOW EVENTS;
 DROP DATABASE event_test1;
 DROP DATABASE event_test12;
 
+--echo #
+--echo # Bug#12546938 (formerly known as bug#61005):
+--echo # CREATE IF NOT EXIST EVENT WILL CREATE MULTIPLE RUNNING EVENTS
+--echo #
+USE events_test;
+SET GLOBAL event_scheduler = ON;
+
+--disable_warnings
+DROP TABLE IF EXISTS table_bug12546938;
+DROP EVENT IF EXISTS event_Bug12546938;
+--enable_warnings
+CREATE TABLE table_bug12546938 (i INT); 
+
+delimiter |;
+
+--echo # Create an event which will be executed with a small delay
+--echo # and won't be automatically dropped after that.
+CREATE EVENT event_Bug12546938
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE
+ENABLE DO
+BEGIN 
+  INSERT INTO table_bug12546938 VALUES(1);
+END
+|
+
+--echo # Now try to create the same event using CREATE EVENT IF NOT EXISTS.
+--echo # A warning should be emitted. A new event should not be created nor
+--echo # the old event should be re-executed.
+CREATE EVENT IF NOT EXISTS event_bug12546938
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE
+ENABLE DO
+BEGIN
+  INSERT INTO table_bug12546938 VALUES (1);
+END
+|
+
+delimiter ;|
+
+--echo # Wait until at least one instance of event is executed.
+let $wait_condition= SELECT COUNT(*) FROM table_bug12546938;
+--source include/wait_condition.inc
+
+--echo # Check that only one instance of our event was executed.
+SELECT COUNT(*) FROM table_bug12546938;
+
+--echo # Clean-up.
+DROP EVENT IF EXISTS event_Bug12546938;
+DROP TABLE table_bug12546938;
+SET GLOBAL EVENT_SCHEDULER = OFF;
 
 ###########################################################################
 #

=== modified file 'sql/event_db_repository.cc'
--- a/sql/event_db_repository.cc	2011-05-04 12:59:24 +0000
+++ b/sql/event_db_repository.cc	2011-05-27 09:23:08 +0000
@@ -604,18 +604,21 @@ Event_db_repository::open_event_table(TH
   only creates a record on disk.
   @pre The thread handle has no open tables.
 
-  @param[in,out] thd           THD
-  @param[in]     parse_data    Parsed event definition
-  @param[in]     create_if_not TRUE if IF NOT EXISTS clause was provided
-                               to CREATE EVENT statement
-
+  @param[in,out] thd                   THD
+  @param[in]     parse_data            Parsed event definition
+  @param[in]     create_if_not         TRUE if IF NOT EXISTS clause was provided
+                                       to CREATE EVENT statement
+  @param[out]    event_already_exists  When method is completed successfully
+                                       set to true if event already exists else
+                                       set to false
   @retval FALSE  success
   @retval TRUE   error
 */
 
 bool
 Event_db_repository::create_event(THD *thd, Event_parse_data *parse_data,
-                                  my_bool create_if_not)
+                                  bool create_if_not,
+                                  bool *event_already_exists)
 {
   int ret= 1;
   TABLE *table= NULL;
@@ -641,6 +644,7 @@ Event_db_repository::create_event(THD *t
   {
     if (create_if_not)
     {
+      *event_already_exists= true;
       push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
                           ER_EVENT_ALREADY_EXISTS, ER(ER_EVENT_ALREADY_EXISTS),
                           parse_data->name.str);
@@ -648,8 +652,10 @@ Event_db_repository::create_event(THD *t
     }
     else
       my_error(ER_EVENT_ALREADY_EXISTS, MYF(0), parse_data->name.str);
+
     goto end;
-  }
+  } else
+    *event_already_exists= false;
 
   DBUG_PRINT("info", ("non-existent, go forward"));
 

=== modified file 'sql/event_db_repository.h'
--- a/sql/event_db_repository.h	2007-08-15 15:08:44 +0000
+++ b/sql/event_db_repository.h	2011-05-27 09:23:08 +0000
@@ -73,7 +73,8 @@ public:
   Event_db_repository(){}
 
   bool
-  create_event(THD *thd, Event_parse_data *parse_data, my_bool create_if_not);
+  create_event(THD *thd, Event_parse_data *parse_data, bool create_if_not,
+               bool *event_already_exists);
 
   bool
   update_event(THD *thd, Event_parse_data *parse_data, LEX_STRING *new_dbname,

=== modified file 'sql/events.cc'
--- a/sql/events.cc	2011-05-16 20:04:01 +0000
+++ b/sql/events.cc	2011-05-27 09:23:08 +0000
@@ -370,6 +370,7 @@ create_query_string(THD *thd, String *bu
   return 0;
 }
 
+
 /**
   Create a new event.
 
@@ -390,8 +391,8 @@ bool
 Events::create_event(THD *thd, Event_parse_data *parse_data,
                      bool if_not_exists)
 {
-  int ret;
-  bool save_binlog_row_based;
+  bool ret;
+  bool save_binlog_row_based, event_already_exists;
   DBUG_ENTER("Events::create_event");
 
   /*
@@ -440,28 +441,32 @@ Events::create_event(THD *thd, Event_par
   pthread_mutex_lock(&LOCK_event_metadata);
 
   /* On error conditions my_error() is called so no need to handle here */
-  if (!(ret= db_repository->create_event(thd, parse_data, if_not_exists)))
+  if (!(ret= db_repository->create_event(thd, parse_data, if_not_exists,
+                                         &event_already_exists)))
   {
     Event_queue_element *new_element;
     bool dropped= 0;
 
-    if (!(new_element= new Event_queue_element()))
-      ret= TRUE;                                // OOM
-    else if ((ret= db_repository->load_named_event(thd, parse_data->dbname,
-                                                   parse_data->name,
-                                                   new_element)))
-    {
-      if (!db_repository->drop_event(thd, parse_data->dbname, parse_data->name,
-                                     TRUE))
-        dropped= 1;
-      delete new_element;
-    }
-    else
+    if (!event_already_exists)
     {
-      /* TODO: do not ignore the out parameter and a possible OOM error! */
-      bool created;
-      if (event_queue)
-        event_queue->create_event(thd, new_element, &created);
+      if (!(new_element= new Event_queue_element()))
+        ret= TRUE;                                // OOM
+      else if ((ret= db_repository->load_named_event(thd, parse_data->dbname,
+                                                     parse_data->name,
+                                                     new_element)))
+      {
+        if (!db_repository->drop_event(thd, parse_data->dbname, parse_data->name,
+                                       TRUE))
+          dropped= 1;
+        delete new_element;
+      }
+      else
+      {
+        /* TODO: do not ignore the out parameter and a possible OOM error! */
+        bool created;
+        if (event_queue)
+          event_queue->create_event(thd, new_element, &created);
+      }
     }
     /*
       binlog the create event unless it's been successfully dropped
@@ -475,13 +480,14 @@ Events::create_event(THD *thd, Event_par
       {
         sql_print_error("Event Error: An error occurred while creating query string, "
                         "before writing it into binary log.");
-        /* Restore the state of binlog format */
-        thd->current_stmt_binlog_row_based= save_binlog_row_based;
-        DBUG_RETURN(TRUE);
+        ret= true;
       }
-      /* If the definer is not set or set to CURRENT_USER, the value of CURRENT_USER 
-         will be written into the binary log as the definer for the SQL thread. */
-      ret= write_bin_log(thd, TRUE, log_query.c_ptr(), log_query.length());
+      else
+        /*
+          If the definer is not set or set to CURRENT_USER, the value of CURRENT_USER
+          will be written into the binary log as the definer for the SQL thread.
+        */
+        ret= write_bin_log(thd, TRUE, log_query.c_ptr(), log_query.length());
     }
   }
   pthread_mutex_unlock(&LOCK_event_metadata);


Attachment: [text/bzr-bundle] bzr/dmitry.shulga@oracle.com-20110527092308-uigde024php475gj.bundle
Thread
bzr push into mysql-5.1 branch (Dmitry.Shulga:3631 to 3632) Bug#12546938Dmitry Shulga27 May