← Back to team overview

maria-developers team mailing list archive

MDEV-7281 EVENT: CREATE OR REPLACE

 

Hi Sergei,

Thanks for your suggestions that you gave in the first review.

Please review a new patch for MDEV-7281, which addresses your suggestions.



>> diff --git a/mysql-test/r/create_drop_event.result b/mysql-test/ /create_drop_event.result
>
> again, same thing. I don't see a test that the command actually worked.
> please use different event definitions in all three create variants.
> and add show create event after every create to verify that
> create if not exists did *not* modify the event, but create or replace
> did modify it.
>

Done.


--- a/sql/event_db_repository.cc
+++ b/sql/event_db_repository.cc
@@ -684,18 +684,28 @@ Event_db_repository::create_event(THD *thd, Event_parse_data *parse_data,
    DBUG_PRINT("info", ("check existance of an event with the same name"));
    if (!find_named_event(parse_data->dbname, parse_data->name, table))
    {
-    if (create_if_not)
+    if (thd->lex->is_create_or_replace())
+    {
+      if ((ret= table->file->ha_delete_row(table->record[0])))
+      {
+        table->file->print_error(ret, MYF(0));
+        goto end;
+      }
+    }
+    else if (create_if_not)
      {
        *event_already_exists= true;
        push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
                            ER_EVENT_ALREADY_EXISTS, ER(ER_EVENT_ALREADY_EXISTS),
                            parse_data->name.str);
        ret= 0;
+      goto end;
      }
      else
+    {
        my_error(ER_EVENT_ALREADY_EXISTS, MYF(0), parse_data->name.str);
-
-    goto end;
+      goto end;
+    }
    } else
      *event_already_exists= false;

I wonder whether this works as it should. On create-or-replace you
delete the old event row and a new one is supposedly inserted. So, the
table is fine.

But event_already_exists will stay unset, undefined, actually, because
the caller didn't set it either. If you set it to TRUE, then the caller
won't update in-memory even list, so your new event won't be enabled.
If you set it to FALSE, it will try to insert a new event without
deleting the old one and I don't know what will happen (either both will
stay active or the insertion fails).


event_already_exists is now set to "false" on OR REPLACE.

The in-memory list is updated in events.cc,
in the method Events::create_event(...).
This is the new code for this:

  if (thd->lex->create_info.or_replace() && event_queue)
    event_queue->drop_event(thd, parse_data->dbname, parse_data->name);


Do you have a test to verify that after CREATE OR REPLACE EVENT the old
event no longer exists and the new event does exist and is executed as
expected?


Added a test for this.

diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 922f0d7..648fd48 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3995,9 +3997,8 @@ end_with_restore_list:
      switch (lex->sql_command) {
      case SQLCOM_CREATE_EVENT:
      {
-      bool if_not_exists= (lex->create_info.options &
-                           HA_LEX_CREATE_IF_NOT_EXISTS);
-      res= Events::create_event(thd, lex->event_parse_data, if_not_exists);
+      res= Events::create_event(thd, lex->event_parse_data,
+                                lex->is_create_if_not_exists());

This doesn't seem to be particularly logical.
You pass lex->is_create_if_not_exists)() here an argument,
but you check thd->lex->is_create_or_replace() in the
Event_db_repository::create_event anyway. I'd suggest not to pass
lex->is_create_if_not_exists() as an argument, and check both in
Event_db_repository::create_event().

The "if not exists" flag is not passed to Events::create_event() any more.

Thanks.
diff --git a/mysql-test/r/create_drop_binlog.result b/mysql-test/r/create_drop_binlog.result
index 4b1db84..bfe9978 100644
--- a/mysql-test/r/create_drop_binlog.result
+++ b/mysql-test/r/create_drop_binlog.result
@@ -243,3 +243,27 @@ Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 #	#	Gtid	1	#	GTID #-#-#
 #	#	Query	1	#	use `test`; DROP USER IF EXISTS u1@localhost
 RESET MASTER;
+SET timestamp=UNIX_TIMESTAMP('2014-11-01 10:20:30');
+CREATE OR REPLACE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE IF EXISTS t1;
+CREATE OR REPLACE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE IF EXISTS t2;
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+EVENT_NAME	EVENT_DEFINITION
+ev1	DROP TABLE IF EXISTS t2
+DROP EVENT ev1;
+DROP EVENT IF EXISTS ev1;
+Warnings:
+Note	1305	Event ev1 does not exist
+SHOW BINLOG EVENTS;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+#	#	Format_desc	1	#	VER
+#	#	Gtid_list	1	#	[]
+#	#	Binlog_checkpoint	1	#	master-bin.000001
+#	#	Gtid	1	#	GTID #-#-#
+#	#	Query	1	#	use `test`; CREATE OR REPLACE DEFINER=`root`@`localhost` EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE IF EXISTS t1
+#	#	Gtid	1	#	GTID #-#-#
+#	#	Query	1	#	use `test`; CREATE OR REPLACE DEFINER=`root`@`localhost` EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE IF EXISTS t2
+#	#	Gtid	1	#	GTID #-#-#
+#	#	Query	1	#	use `test`; DROP EVENT ev1
+#	#	Gtid	1	#	GTID #-#-#
+#	#	Query	1	#	use `test`; DROP EVENT IF EXISTS ev1
+RESET MASTER;
diff --git a/mysql-test/r/create_drop_event.result b/mysql-test/r/create_drop_event.result
new file mode 100644
index 0000000..0d69e85
--- /dev/null
+++ b/mysql-test/r/create_drop_event.result
@@ -0,0 +1,48 @@
+SET timestamp=UNIX_TIMESTAMP('2014-11-01 10:20:30');
+SET GLOBAL event_scheduler=off;
+CREATE TABLE t1 (a INT);
+CREATE OR REPLACE EVENT IF NOT EXISTS ev1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE db1;
+ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
+CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (10);
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+EVENT_NAME	EVENT_DEFINITION
+ev1	INSERT INTO t1 VALUES (10)
+SET GLOBAL event_scheduler=on;
+SELECT DISTINCT a FROM t1;
+a
+10
+SET GLOBAL event_scheduler=off;
+DELETE FROM t1;
+CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (11);
+ERROR HY000: Event 'ev1' already exists
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+EVENT_NAME	EVENT_DEFINITION
+ev1	INSERT INTO t1 VALUES (10)
+CREATE EVENT IF NOT EXISTS ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (12);
+Warnings:
+Note	1537	Event 'ev1' already exists
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+EVENT_NAME	EVENT_DEFINITION
+ev1	INSERT INTO t1 VALUES (10)
+CREATE OR REPLACE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (13);
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+EVENT_NAME	EVENT_DEFINITION
+ev1	INSERT INTO t1 VALUES (13)
+SET GLOBAL event_scheduler=on;
+SELECT DISTINCT a FROM t1;
+a
+13
+SET GLOBAL event_scheduler=off;
+DELETE FROM t1;
+DROP EVENT IF EXISTS ev1;
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+EVENT_NAME	EVENT_DEFINITION
+DROP EVENT IF EXISTS ev1;
+Warnings:
+Note	1305	Event ev1 does not exist
+DROP EVENT ev1;
+ERROR HY000: Unknown event 'ev1'
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+EVENT_NAME	EVENT_DEFINITION
+DROP TABLE t1;
+SET timestamp=DEFAULT;
diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_event.result b/mysql-test/suite/rpl/r/rpl_create_drop_event.result
new file mode 100644
index 0000000..2f477b1
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_create_drop_event.result
@@ -0,0 +1,24 @@
+include/master-slave.inc
+[connection master]
+SET GLOBAL event_scheduler=off;
+CREATE TABLE t1 (a INT);
+CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (10);
+CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (11);
+ERROR HY000: Event 'ev1' already exists
+CREATE OR REPLACE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (11);
+SELECT EVENT_NAME,STATUS,EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+EVENT_NAME	STATUS	EVENT_DEFINITION
+ev1	ENABLED	INSERT INTO t1 VALUES (11)
+SET GLOBAL event_scheduler=on;
+SET GLOBAL event_scheduler=off;
+SELECT DISTINCT a FROM t1;
+a
+11
+DELETE FROM t1;
+# Syncing slave with master
+SELECT EVENT_NAME,STATUS,EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+EVENT_NAME	STATUS	EVENT_DEFINITION
+ev1	SLAVESIDE_DISABLED	INSERT INTO t1 VALUES (11)
+DROP TABLE t1;
+DROP EVENT ev1;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_create_drop_event.test b/mysql-test/suite/rpl/t/rpl_create_drop_event.test
new file mode 100644
index 0000000..96a7e82
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_create_drop_event.test
@@ -0,0 +1,27 @@
+--source include/master-slave.inc
+
+connection master;
+SET GLOBAL event_scheduler=off;
+
+CREATE TABLE t1 (a INT);
+CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (10);
+--error ER_EVENT_ALREADY_EXISTS
+CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (11);
+CREATE OR REPLACE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (11);
+SELECT EVENT_NAME,STATUS,EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+
+SET GLOBAL event_scheduler=on;
+let $wait_condition= SELECT count(*)>0 FROM t1;
+--source include/wait_condition.inc
+SET GLOBAL event_scheduler=off;
+SELECT DISTINCT a FROM t1;
+DELETE FROM t1;
+
+--echo # Syncing slave with master
+sync_slave_with_master;
+SELECT EVENT_NAME,STATUS,EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+connection master;
+DROP TABLE t1;
+DROP EVENT ev1;
+sync_slave_with_master;
+--source include/rpl_end.inc
diff --git a/mysql-test/t/create_drop_binlog.test b/mysql-test/t/create_drop_binlog.test
index 5bcd783..b757a8b 100644
--- a/mysql-test/t/create_drop_binlog.test
+++ b/mysql-test/t/create_drop_binlog.test
@@ -119,3 +119,15 @@ DROP USER IF EXISTS u1@localhost;
 --replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
 SHOW BINLOG EVENTS;
 RESET MASTER;
+
+
+SET timestamp=UNIX_TIMESTAMP('2014-11-01 10:20:30');
+CREATE OR REPLACE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE IF EXISTS t1;
+CREATE OR REPLACE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE IF EXISTS t2;
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+DROP EVENT ev1;
+DROP EVENT IF EXISTS ev1;
+--replace_column 1 # 2 # 5 #
+--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
+SHOW BINLOG EVENTS;
+RESET MASTER;
diff --git a/mysql-test/t/create_drop_event.test b/mysql-test/t/create_drop_event.test
new file mode 100644
index 0000000..e136798
--- /dev/null
+++ b/mysql-test/t/create_drop_event.test
@@ -0,0 +1,44 @@
+--source include/not_embedded.inc
+
+SET timestamp=UNIX_TIMESTAMP('2014-11-01 10:20:30');
+SET GLOBAL event_scheduler=off;
+
+CREATE TABLE t1 (a INT);
+
+--error ER_WRONG_USAGE
+CREATE OR REPLACE EVENT IF NOT EXISTS ev1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE db1;
+
+CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (10);
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+SET GLOBAL event_scheduler=on;
+let $wait_condition= SELECT count(*)>0 FROM t1;
+--source include/wait_condition.inc
+SELECT DISTINCT a FROM t1;
+SET GLOBAL event_scheduler=off;
+DELETE FROM t1;
+
+--error ER_EVENT_ALREADY_EXISTS
+CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (11);
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+CREATE EVENT IF NOT EXISTS ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (12);
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+CREATE OR REPLACE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (13);
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+
+SET GLOBAL event_scheduler=on;
+let $wait_condition= SELECT count(*)>0 FROM t1;
+--source include/wait_condition.inc
+SELECT DISTINCT a FROM t1;
+SET GLOBAL event_scheduler=off;
+DELETE FROM t1;
+
+DROP EVENT IF EXISTS ev1;
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+DROP EVENT IF EXISTS ev1;
+--error ER_EVENT_DOES_NOT_EXIST
+DROP EVENT ev1;
+SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
+
+DROP TABLE t1;
+
+SET timestamp=DEFAULT;
diff --git a/sql/event_db_repository.cc b/sql/event_db_repository.cc
index 30dffc3..db4ae3a 100644
--- a/sql/event_db_repository.cc
+++ b/sql/event_db_repository.cc
@@ -654,7 +654,6 @@ class Event_db_intact : public Table_check_intact
 
 bool
 Event_db_repository::create_event(THD *thd, Event_parse_data *parse_data,
-                                  bool create_if_not,
                                   bool *event_already_exists)
 {
   int ret= 1;
@@ -685,18 +684,29 @@ class Event_db_intact : public Table_check_intact
   DBUG_PRINT("info", ("check existance of an event with the same name"));
   if (!find_named_event(parse_data->dbname, parse_data->name, table))
   {
-    if (create_if_not)
+    if (thd->lex->create_info.or_replace())
+    {
+      *event_already_exists= false; // Force the caller to update event_queue
+      if ((ret= table->file->ha_delete_row(table->record[0])))
+      {
+        table->file->print_error(ret, MYF(0));
+        goto end;
+      }
+    }
+    else if (thd->lex->create_info.if_not_exists())
     {
       *event_already_exists= true;
       push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
                           ER_EVENT_ALREADY_EXISTS, ER(ER_EVENT_ALREADY_EXISTS),
                           parse_data->name.str);
       ret= 0;
+      goto end;
     }
     else
+    {
       my_error(ER_EVENT_ALREADY_EXISTS, MYF(0), parse_data->name.str);
-
-    goto end;
+      goto end;
+    }
   } else
     *event_already_exists= false;
 
diff --git a/sql/event_db_repository.h b/sql/event_db_repository.h
index a286279..e7b52ba 100644
--- a/sql/event_db_repository.h
+++ b/sql/event_db_repository.h
@@ -74,7 +74,7 @@ class Event_db_repository
   Event_db_repository(){}
 
   bool
-  create_event(THD *thd, Event_parse_data *parse_data, bool create_if_not,
+  create_event(THD *thd, Event_parse_data *parse_data,
                bool *event_already_exists);
   bool
   update_event(THD *thd, Event_parse_data *parse_data, LEX_STRING *new_dbname,
diff --git a/sql/events.cc b/sql/events.cc
index 7268686..d1cbd29 100644
--- a/sql/events.cc
+++ b/sql/events.cc
@@ -273,7 +273,12 @@ bool Events::check_if_system_tables_error()
 {
   buf->length(0);
   /* Append the "CREATE" part of the query */
-  if (buf->append(STRING_WITH_LEN("CREATE ")))
+  if (thd->lex->create_info.or_replace())
+  {
+    if (buf->append(STRING_WITH_LEN("CREATE OR REPLACE ")))
+      return 1;
+  }
+  else if (buf->append(STRING_WITH_LEN("CREATE ")))
     return 1;
   /* Append definer */
   append_definer(thd, buf, &(thd->lex->definer->user), &(thd->lex->definer->host));
@@ -292,8 +297,7 @@ bool Events::check_if_system_tables_error()
 
   @param[in,out]  thd            THD
   @param[in]      parse_data     Event's data from parsing stage
-  @param[in]      if_not_exists  Whether IF NOT EXISTS was
-                                 specified
+
   In case there is an event with the same name (db) and
   IF NOT EXISTS is specified, an warning is put into the stack.
   @sa Events::drop_event for the notes about locking, pre-locking
@@ -304,8 +308,7 @@ bool Events::check_if_system_tables_error()
 */
 
 bool
-Events::create_event(THD *thd, Event_parse_data *parse_data,
-                     bool if_not_exists)
+Events::create_event(THD *thd, Event_parse_data *parse_data)
 {
   bool ret;
   bool event_already_exists;
@@ -347,8 +350,11 @@ bool Events::check_if_system_tables_error()
                        parse_data->dbname.str, parse_data->name.str))
     DBUG_RETURN(TRUE);
 
+  if (thd->lex->create_info.or_replace() && event_queue)
+    event_queue->drop_event(thd, parse_data->dbname, parse_data->name);
+
   /* 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,
                                          &event_already_exists)))
   {
     Event_queue_element *new_element;
diff --git a/sql/events.h b/sql/events.h
index 646fd25..bc2af1f 100644
--- a/sql/events.h
+++ b/sql/events.h
@@ -104,7 +104,7 @@ class Events
   destroy_mutexes();
 
   static bool
-  create_event(THD *thd, Event_parse_data *parse_data, bool if_exists);
+  create_event(THD *thd, Event_parse_data *parse_data);
 
   static bool
   update_event(THD *thd, Event_parse_data *parse_data,
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 64e016f..be57be9 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4439,8 +4439,7 @@ static bool do_execute_sp(THD *thd, sp_head *sp)
     switch (lex->sql_command) {
     case SQLCOM_CREATE_EVENT:
     {
-      bool if_not_exists= lex->create_info.if_not_exists();
-      res= Events::create_event(thd, lex->event_parse_data, if_not_exists);
+      res= Events::create_event(thd, lex->event_parse_data);
       break;
     }
     case SQLCOM_ALTER_EVENT:
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 8025340..f0b723b 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2563,6 +2563,7 @@ create:
           {
             // TODO: remove this when "MDEV-5359 CREATE OR REPLACE..." is done
             if ($1.or_replace() &&
+                Lex->sql_command != SQLCOM_CREATE_EVENT &&
                 Lex->sql_command != SQLCOM_CREATE_VIEW &&
                 Lex->sql_command != SQLCOM_CREATE_FUNCTION &&
                 Lex->sql_command != SQLCOM_CREATE_SPFUNCTION &&
@@ -2657,7 +2658,8 @@ event_tail:
             LEX *lex=Lex;
 
             lex->stmt_definition_begin= $1;
-            lex->create_info.set($3);
+            if (lex->add_create_options_with_check($3))
+              MYSQL_YYABORT;
             if (!(lex->event_parse_data= Event_parse_data::new_instance(thd)))
               MYSQL_YYABORT;
             lex->event_parse_data->identifier= $4;

Follow ups