maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #08155
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
