#At file:///home/daogangq/mysql/bzrwork/bug44331/5.1-bugteam/ based on revid:epotemkin@stripped
3034 Dao-Gang.Qu@stripped 2009-08-03
Bug #44331 Restore of database with events produces warning in replication
The real problem is that if the EVENT is created without DEFINER clause set explicitly
or the definer is set to CURRENT_USER, the definer is not consistent between master
and slave. Because the DEFINER is set to CURRENT_USER of the current thread.
The CURRENT_USER is mysqld startup user on master, but the CURRENT_USER is empty
on slave for slave SQL thread.
To fix the problem, if definer is not set explicitly, a DEFINER clause
will be added when writing the query into binlog; if 'CURRENT_USER' is
used as the DEFINER, it will be replaced with the value of current user
before writing to binlog.
@ mysql-test/suite/rpl/r/rpl_drop_if_exists.result
Updated the result file after fixing bug#44331
@ mysql-test/suite/rpl/r/rpl_events.result
Test result of Bug#44331
@ mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result
Updated the result file after fixing bug#44331
@ mysql-test/suite/rpl/t/rpl_events.test
Added test to verify if the definer is consistent between master and slave
when the event is created without DEFINER clause set explicitly or the
DEFINER is set to CURRENT_USER
@ sql/events.cc
The "create_query_string" function is added for creating a new query string
and insert the DEFINER clause to it.
Added a new parameter "definer_set_mode" to 'Events::create_event' function for
indicating the mode of the definer is set.
The value of CURRENT_USER will be written into the binary log
as default definer for slave SQL thread.
modified:
mysql-test/suite/rpl/r/rpl_drop_if_exists.result
mysql-test/suite/rpl/r/rpl_events.result
mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result
mysql-test/suite/rpl/t/rpl_events.test
sql/events.cc
sql/events.h
sql/handler.h
sql/sql_parse.cc
sql/sql_yacc.yy
=== modified file 'mysql-test/suite/rpl/r/rpl_drop_if_exists.result'
--- a/mysql-test/suite/rpl/r/rpl_drop_if_exists.result 2009-02-11 17:46:43 +0000
+++ b/mysql-test/suite/rpl/r/rpl_drop_if_exists.result 2009-08-03 03:13:20 +0000
@@ -43,7 +43,7 @@ master-bin.000001 # Query # # use `test`
master-bin.000001 # Query # # DROP DATABASE IF EXISTS db_bug_13684
master-bin.000001 # Query # # CREATE DATABASE db_bug_13684
master-bin.000001 # Query # # use `test`; CREATE TABLE db_bug_13684.t (a int)
-master-bin.000001 # Query # # use `test`; CREATE EVENT db_bug_13684.e
+master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` EVENT db_bug_13684.e
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE db_bug_13684.t SET a = a + 1
@@ -75,7 +75,7 @@ master-bin.000001 # Query # # use `test`
master-bin.000001 # Query # # DROP DATABASE IF EXISTS db_bug_13684
master-bin.000001 # Query # # CREATE DATABASE db_bug_13684
master-bin.000001 # Query # # use `test`; CREATE TABLE db_bug_13684.t (a int)
-master-bin.000001 # Query # # use `test`; CREATE EVENT db_bug_13684.e
+master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` EVENT db_bug_13684.e
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE db_bug_13684.t SET a = a + 1
=== modified file 'mysql-test/suite/rpl/r/rpl_events.result'
--- a/mysql-test/suite/rpl/r/rpl_events.result 2009-01-30 13:44:49 +0000
+++ b/mysql-test/suite/rpl/r/rpl_events.result 2009-08-03 03:13:20 +0000
@@ -191,5 +191,36 @@ select * from t28953;
END;|
ALTER EVENT event1 RENAME TO event2;
DROP EVENT event2;
+CREATE TABLE test.t1(details CHAR(30));
+/* comment befor create */ CREATE /* comment after create */ EVENT /* comment after event */ event44331_1
+ON SCHEDULE AT CURRENT_TIMESTAMP
+ON COMPLETION PRESERVE DISABLE
+DO INSERT INTO test.t1 VALUES('event event44331_1 fired - no definer');
+CREATE /* comment after create */ DEFINER=CURRENT_USER EVENT /* comment after event */ event44331_2
+ON SCHEDULE AT CURRENT_TIMESTAMP
+ON COMPLETION PRESERVE DISABLE
+DO INSERT INTO test.t1 VALUES('event event44331_2 fired - DEFINER=CURRENT_USER');
+CREATE /* comment after create */ DEFINER=CURRENT_USER() /* comment after CURRENT_USER() */ EVENT event44331_3
+ON SCHEDULE AT CURRENT_TIMESTAMP
+ON COMPLETION PRESERVE DISABLE
+DO INSERT INTO test.t1 VALUES('event event44331_3 fired - DEFINER=CURRENT_USER() function');
+#on slave
+select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
+where EVENT_NAME='event44331_1' || EVENT_NAME='event44331_2' || EVENT_NAME='event44331_3';
+EVENT_SCHEMA EVENT_NAME DEFINER
+test event44331_1 root@localhost
+test event44331_2 root@localhost
+test event44331_3 root@localhost
+#on slave
+select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
+where EVENT_NAME='event44331_1' || EVENT_NAME='event44331_2' || EVENT_NAME='event44331_3';
+EVENT_SCHEMA EVENT_NAME DEFINER
+test event44331_1 root@localhost
+test event44331_2 root@localhost
+test event44331_3 root@localhost
SET @@global.event_scheduler= @old_event_scheduler;
DROP TABLE t28953;
+DROP TABLE t1;
+DROP EVENT event44331_1;
+DROP EVENT event44331_2;
+DROP EVENT event44331_3;
=== modified file 'mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result'
--- a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result 2009-05-31 05:44:41 +0000
+++ b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result 2009-08-03 03:13:20 +0000
@@ -690,7 +690,7 @@ test_rpl e1 root@localhost SYSTEM RECURR
USE test_rpl;
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
-test_rpl e1 @ SYSTEM RECURRING NULL 1 # # NULL SLAVESIDE_DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+test_rpl e1 root@localhost SYSTEM RECURRING NULL 1 # # NULL SLAVESIDE_DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
==========MASTER==========
SELECT COUNT(*) FROM t1;
COUNT(*)
@@ -1076,7 +1076,7 @@ master-bin.000001 # Query 1 # use `test_
master-bin.000001 # Query 1 # BEGIN
master-bin.000001 # Query 1 # use `test_rpl`; INSERT INTO t1 VALUES(1, 'test1')
master-bin.000001 # Xid 1 # #
-master-bin.000001 # Query 1 # use `test_rpl`; CREATE EVENT e1 ON SCHEDULE EVERY '1' SECOND COMMENT 'e_second_comment' DO DELETE FROM t1
+master-bin.000001 # Query 1 # use `test_rpl`; CREATE DEFINER=`root`@`localhost` EVENT e1 ON SCHEDULE EVERY '1' SECOND COMMENT 'e_second_comment' DO DELETE FROM t1
master-bin.000001 # Query 1 # use `test_rpl`; ALTER EVENT e1 RENAME TO e2
master-bin.000001 # Query 1 # use `test_rpl`; DROP EVENT e2
master-bin.000001 # Query 1 # BEGIN
=== modified file 'mysql-test/suite/rpl/t/rpl_events.test'
--- a/mysql-test/suite/rpl/t/rpl_events.test 2009-01-30 13:44:49 +0000
+++ b/mysql-test/suite/rpl/t/rpl_events.test 2009-08-03 03:13:20 +0000
@@ -46,7 +46,38 @@ connection master;
DROP EVENT event2;
+#
+# BUG#44331
+# This test verifies if the definer is consistent between master and slave,
+# when the event is created without DEFINER clause set explicitly or the
+# DEFINER is set to CURRENT_USER
+#
+CREATE TABLE test.t1(details CHAR(30));
+
+/* comment befor create */ CREATE /* comment after create */ EVENT /* comment after event */ event44331_1
+ ON SCHEDULE AT CURRENT_TIMESTAMP
+ ON COMPLETION PRESERVE DISABLE
+ DO INSERT INTO test.t1 VALUES('event event44331_1 fired - no definer');
+
+CREATE /* comment after create */ DEFINER=CURRENT_USER EVENT /* comment after event */ event44331_2
+ ON SCHEDULE AT CURRENT_TIMESTAMP
+ ON COMPLETION PRESERVE DISABLE
+ DO INSERT INTO test.t1 VALUES('event event44331_2 fired - DEFINER=CURRENT_USER');
+
+CREATE /* comment after create */ DEFINER=CURRENT_USER() /* comment after CURRENT_USER() */ EVENT event44331_3
+ ON SCHEDULE AT CURRENT_TIMESTAMP
+ ON COMPLETION PRESERVE DISABLE
+ DO INSERT INTO test.t1 VALUES('event event44331_3 fired - DEFINER=CURRENT_USER() function');
+
+--echo #on slave
+select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
+ where EVENT_NAME='event44331_1' || EVENT_NAME='event44331_2' || EVENT_NAME='event44331_3';
+
sync_slave_with_master;
+connection slave;
+--echo #on slave
+select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
+ where EVENT_NAME='event44331_1' || EVENT_NAME='event44331_2' || EVENT_NAME='event44331_3';
# Doing cleanup of the table referred to in the event to guarantee
# that there is no bad timing cauing it to try to access the table.
@@ -54,4 +85,8 @@ sync_slave_with_master;
connection master;
SET @@global.event_scheduler= @old_event_scheduler;
DROP TABLE t28953;
+DROP TABLE t1;
+DROP EVENT event44331_1;
+DROP EVENT event44331_2;
+DROP EVENT event44331_3;
sync_slave_with_master;
=== modified file 'sql/events.cc'
--- a/sql/events.cc 2009-04-09 06:22:06 +0000
+++ b/sql/events.cc 2009-08-03 03:13:20 +0000
@@ -342,12 +342,79 @@ common_1_lev_code:
/**
+ Create a new query string and insert the DEFINER clause to it.
+
+ @param[in] thd Thread handler
+ @param[in] buf Query string
+ @param[in] definer_set_mode The mode of the definer is set
+
+ @return
+ 0 ok
+ 1 error
+*/
+static int
+create_query_string(THD *thd, String *buf, int definer_set_mode)
+{
+ const char* offset0= thd->lex->create_info.offset[0];
+ const char* offset1= thd->lex->create_info.offset[1];
+
+ if (definer_set_mode == 1)
+ {
+ /* Handle no definer case */
+ /* Append the "CREATE" part of the query */
+ if (buf->append(thd->query, offset0 - thd->query - 5))
+ return 1;
+ /* Append definer */
+ append_definer(thd, buf, &(thd->lex->definer->user), &(thd->lex->definer->host));
+ /* Append the left part of thd->query after "CREATE" part */
+ if (buf->append(offset0 - 5))
+ return 1;
+ }
+ else if (definer_set_mode == 2)
+ {
+ if ((offset1 - 1)[0] == ')')
+ {
+ /* Handle definer is set to CURRENT_USER() function case */
+ /* Append the "CREATE" part of the query */
+ if (buf->append(thd->query, offset0 - thd->query - 7))
+ return 1;
+ /* Append definer */
+ append_definer(thd, buf, &(thd->lex->definer->user), &(thd->lex->definer->host));
+ /* Append the left part of thd->query after "DEFINER" part */
+ if (buf->append(offset1))
+ return 1;
+ }
+ else
+ {
+ /* Handle definer is set to CURRENT_USER case */
+ if (buf->append(thd->query, offset0 - thd->query - 7))
+ return 1;
+ /* Append definer */
+ append_definer(thd, buf, &(thd->lex->definer->user), &(thd->lex->definer->host));
+ /* Append the left part of thd->query after "DEFINER" part */
+ if (buf->append(offset1 - 5))
+ return 1;
+ }
+ }
+ else
+ return 1;
+
+ return 0;
+}
+
+
+/**
Create a new event.
@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
+ @param[in] definer_set_mode The mode of the definer is set
+ 0 the definer is set explicitly
+ 1 the definer is not set
+ 2 the definer is set to CURRENT_USER
+
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
@@ -359,7 +426,7 @@ common_1_lev_code:
bool
Events::create_event(THD *thd, Event_parse_data *parse_data,
- bool if_not_exists)
+ bool if_not_exists, int definer_set_mode)
{
int ret;
DBUG_ENTER("Events::create_event");
@@ -439,7 +506,21 @@ Events::create_event(THD *thd, Event_par
{
/* Binlog the create event. */
DBUG_ASSERT(thd->query && thd->query_length);
- write_bin_log(thd, TRUE, thd->query, thd->query_length);
+ String log_query;
+ if (definer_set_mode)
+ {
+ if (create_query_string(thd, &log_query,definer_set_mode))
+ {
+ sql_print_error("Event Error: An error occurred while creating query string, "
+ "before write it into binary log.");
+ DBUG_RETURN(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 definer for slave SQL thread. */
+ write_bin_log(thd, TRUE, log_query.c_ptr(), log_query.length());
+ }
+ else
+ write_bin_log(thd, TRUE, thd->query, thd->query_length);
}
}
pthread_mutex_unlock(&LOCK_event_metadata);
=== modified file 'sql/events.h'
--- a/sql/events.h 2007-08-15 15:08:44 +0000
+++ b/sql/events.h 2009-08-03 03:13:20 +0000
@@ -107,7 +107,8 @@ public:
switch_event_scheduler_state(enum enum_opt_event_scheduler new_state);
static bool
- create_event(THD *thd, Event_parse_data *parse_data, bool if_exists);
+ create_event(THD *thd, Event_parse_data *parse_data,
+ bool if_exists, int definer_set_mode);
static bool
update_event(THD *thd, Event_parse_data *parse_data,
=== modified file 'sql/handler.h'
--- a/sql/handler.h 2009-05-04 10:00:15 +0000
+++ b/sql/handler.h 2009-08-03 03:13:20 +0000
@@ -921,6 +921,8 @@ typedef struct st_ha_create_information
bool varchar; /* 1 if table has a VARCHAR */
enum ha_storage_media storage_media; /* DEFAULT, DISK or MEMORY */
enum ha_choice page_checksum; /* If we have page_checksums */
+ /* Record the current offset address of query */
+ const char* offset[2];
} HA_CREATE_INFO;
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2009-07-16 12:43:17 +0000
+++ b/sql/sql_parse.cc 2009-08-03 03:13:20 +0000
@@ -3708,6 +3708,17 @@ end_with_restore_list:
break;
}
+ /* The definer_set_mode is assigned an initial value 0
+ to indicate that the definer is set explicitly */
+ int definer_set_mode= 0;
+ if (!lex->definer)
+ /* The definer is not set */
+ definer_set_mode= 1;
+ else if ((lex->definer->user.str == thd->security_ctx->priv_user) &&
+ (lex->definer->host.str == thd->security_ctx->priv_host))
+ /* The definer is set to CURRENT_USER */
+ definer_set_mode= 2;
+
res= sp_process_definer(thd);
if (res)
break;
@@ -3717,7 +3728,8 @@ end_with_restore_list:
{
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,
+ if_not_exists, definer_set_mode);
break;
}
case SQLCOM_ALTER_EVENT:
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2009-07-16 12:43:17 +0000
+++ b/sql/sql_yacc.yy 2009-08-03 03:13:20 +0000
@@ -1744,6 +1744,7 @@ create:
Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED;
Lex->create_view_suid= TRUE;
}
+ record_offset
view_or_trigger_or_sp_or_event
{}
| CREATE USER clear_privileges grant_list
@@ -1764,6 +1765,14 @@ create:
}
;
+record_offset:
+ {
+ /* Record the current offset address */
+ Lex_input_stream *lip= YYLIP;
+ Lex->create_info.offset[0]= lip->get_ptr();
+ }
+ ;
+
server_def:
SERVER_SYM
ident_or_text
@@ -13021,6 +13030,9 @@ definer:
DEFINER_SYM EQ user
{
YYTHD->lex->definer= get_current_user(YYTHD, $3);
+ /* Record the current offset address */
+ Lex_input_stream *lip= YYLIP;
+ Lex->create_info.offset[1]= lip->get_ptr();
}
;
Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20090803031320-6yarie448yj9vtyf.bundle
