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#12546938 | Dmitry Shulga | 27 May |
