#At file:///home/daogangqu/mysql/bzrwork/bug44331/mysql-pe/ based on revid:davi.arnaut@stripped
3561 Dao-Gang.Qu@stripped 2009-08-30 [merge]
Bug #44331 Restore of database with events produces warning in replication
If an EVENT is created without the DEFINER clause set explicitly or with it set
to CURRENT_USER, the master and slaves become inconsistent. This issue stems from
the fact that in both cases, the DEFINER is set to the CURRENT_USER of the current
thread. On the master, the CURRENT_USER is the mysqld's user, while on the slave,
the CURRENT_USER is empty for the SQL Thread which is responsible for executing
the statement.
To fix the problem, we do what follows. If the definer is not set explicitly,
a DEFINER clause is added when writing the query into binlog; if 'CURRENT_USER' is
used as the DEFINER, it is replaced with the value of the current user before
writing to binlog.
@ mysql-test/suite/rpl/r/rpl_backup_extra.result
Updated the result file after fixing bug#44331
@ mysql-test/suite/rpl/r/rpl_backup_multi.result
Updated the result file after fixing bug#44331
@ mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
Updated the result file after fixing bug#44331
@ 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 the DEFINER clause set explicitly or the
DEFINER is set to CURRENT_USER
@ sql/events.cc
The "create_query_string" function is added to create a new query string
for removing executable comments.
@ sql/sql_yacc.yy
The remember_name token was added for recording the offset of EVENT_SYM.
modified:
mysql-test/suite/rpl/r/rpl_backup_extra.result
mysql-test/suite/rpl/r/rpl_backup_multi.result
mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
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/sql_yacc.yy
=== modified file 'mysql-test/suite/rpl/r/rpl_backup_extra.result'
--- a/mysql-test/suite/rpl/r/rpl_backup_extra.result 2009-05-21 13:17:37 +0000
+++ b/mysql-test/suite/rpl/r/rpl_backup_extra.result 2009-08-29 18:24:59 +0000
@@ -136,8 +136,6 @@ PURGE BACKUP LOGS;
RESTORE FROM 'dbs.bak' OVERWRITE;
backup_id
#
-Warnings:
-# # The user specified as a definer (''@'') does not exist
# Verify binlog events from master position before restore.
# Note that restore incident event will be generated. Binary log position
# will not be advanced after restore operation.
=== modified file 'mysql-test/suite/rpl/r/rpl_backup_multi.result'
--- a/mysql-test/suite/rpl/r/rpl_backup_multi.result 2009-05-26 22:21:34 +0000
+++ b/mysql-test/suite/rpl/r/rpl_backup_multi.result 2009-08-29 18:24:59 +0000
@@ -625,8 +625,6 @@ STOP SLAVE;
RESTORE FROM 'db12s.bak' OVERWRITE;
backup_id
#
-Warnings:
-# 1449 The user specified as a definer (''@'') does not exist
SHOW SLAVE STATUS;
Slave_IO_State #
Master_Host 127.0.0.1
=== modified file 'mysql-test/suite/rpl/r/rpl_create_if_not_exists.result'
--- a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result 2009-08-13 02:48:57 +0000
+++ b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result 2009-08-29 08:52:22 +0000
@@ -29,5 +29,5 @@ t1
t2
SHOW EVENTS in mysqltest;
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
-mysqltest e @ SYSTEM ONE TIME # NULL NULL NULL NULL SLAVESIDE_DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+mysqltest e root@localhost SYSTEM ONE TIME # NULL NULL NULL NULL SLAVESIDE_DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
DROP DATABASE IF EXISTS mysqltest;
=== 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-29 08:52:22 +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-02-13 16:30:54 +0000
+++ b/mysql-test/suite/rpl/r/rpl_events.result 2009-08-29 18:24:59 +0000
@@ -191,5 +191,47 @@ select * from t28953;
END;|
ALTER EVENT event1 RENAME TO event2;
DROP EVENT event2;
+CREATE TABLE test.t1(details CHAR(30));
+CREATE EVENT /*!50000 event44331_1 */
+ON SCHEDULE AT CURRENT_TIMESTAMP
+ON COMPLETION PRESERVE DISABLE
+DO INSERT INTO test.t1 VALUES('event event44331_1 fired - no definer');
+CREATE DEFINER=CURRENT_USER /*!50000 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 DEFINER=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');
+CREATE /*!50000 DEFINER='user44331' */ EVENT event44331_4
+ON SCHEDULE AT CURRENT_TIMESTAMP
+ON COMPLETION PRESERVE DISABLE
+DO INSERT INTO test.t1 VALUES('event event44331_4 fired - DEFINER=user1');
+Warnings:
+Note 1449 The user specified as a definer ('user44331'@'%') does not exist
+#on master
+select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
+where EVENT_NAME='event44331_1' || EVENT_NAME='event44331_2' ||
+EVENT_NAME='event44331_3' || EVENT_NAME='event44331_4';
+EVENT_SCHEMA EVENT_NAME DEFINER
+test event44331_1 root@localhost
+test event44331_2 root@localhost
+test event44331_3 root@localhost
+test event44331_4 user44331@%
+#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_NAME='event44331_4';
+EVENT_SCHEMA EVENT_NAME DEFINER
+test event44331_1 root@localhost
+test event44331_2 root@localhost
+test event44331_3 root@localhost
+test event44331_4 user44331@%
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;
+DROP EVENT event44331_4;
=== modified file 'mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result'
--- a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result 2009-08-26 23:32:54 +0000
+++ b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result 2009-08-29 18:24:59 +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(*)
@@ -1078,7 +1078,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-31 15:53:35 +0000
+++ b/mysql-test/suite/rpl/t/rpl_events.test 2009-08-29 18:24:59 +0000
@@ -46,12 +46,52 @@ connection master;
DROP EVENT event2;
-sync_slave_with_master;
+#
+# BUG#44331
+# This test verifies if the definer is consistent between master and slave,
+# when the event is created without the DEFINER clause set explicitly or the
+# DEFINER is set to CURRENT_USER
+#
+CREATE TABLE test.t1(details CHAR(30));
+
+CREATE EVENT /*!50000 event44331_1 */
+ ON SCHEDULE AT CURRENT_TIMESTAMP
+ ON COMPLETION PRESERVE DISABLE
+ DO INSERT INTO test.t1 VALUES('event event44331_1 fired - no definer');
+
+CREATE DEFINER=CURRENT_USER /*!50000 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 DEFINER=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');
-# 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.
+CREATE /*!50000 DEFINER='user44331' */ EVENT event44331_4
+ ON SCHEDULE AT CURRENT_TIMESTAMP
+ ON COMPLETION PRESERVE DISABLE
+ DO INSERT INTO test.t1 VALUES('event event44331_4 fired - DEFINER=user1');
+
+--echo #on master
+select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
+ where EVENT_NAME='event44331_1' || EVENT_NAME='event44331_2' ||
+ EVENT_NAME='event44331_3' || EVENT_NAME='event44331_4';
+
+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' || EVENT_NAME='event44331_4';
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;
+DROP EVENT event44331_4;
sync_slave_with_master;
=== modified file 'sql/events.cc'
--- a/sql/events.cc 2009-07-24 19:09:00 +0000
+++ b/sql/events.cc 2009-08-29 18:24:59 +0000
@@ -341,6 +341,33 @@ common_1_lev_code:
/**
+ Create a new query string for removing executable comments
+ for avoiding leak and keeping consistency of the execution
+ on master and slave.
+
+ @param[in] thd Thread handler
+ @param[in] buf Query string
+
+ @return
+ 0 ok
+ 1 error
+*/
+static int
+create_query_string(THD *thd, String *buf)
+{
+ /* Append the "CREATE" part of the query */
+ if (buf->append(STRING_WITH_LEN("CREATE ")))
+ 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(thd->lex->stmt_definition_begin))
+ return 1;
+
+ return 0;
+}
+
+/**
Create a new event.
@param[in,out] thd THD
@@ -429,7 +456,16 @@ 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 (create_query_string(thd, &log_query))
+ {
+ sql_print_error("Event Error: An error occurred while creating query string, "
+ "before writing 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 the definer for the SQL thread. */
+ write_bin_log(thd, TRUE, log_query.c_ptr(), log_query.length());
}
}
pthread_mutex_unlock(&LOCK_event_metadata);
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2009-08-27 11:18:15 +0000
+++ b/sql/sql_yacc.yy 2009-08-29 18:24:59 +0000
@@ -2027,15 +2027,16 @@ server_option:
;
event_tail:
- EVENT_SYM opt_if_not_exists sp_name
+ remember_name EVENT_SYM opt_if_not_exists sp_name
{
THD *thd= YYTHD;
LEX *lex=Lex;
- lex->create_info.options= $2;
+ lex->stmt_definition_begin= $1;
+ lex->create_info.options= $3;
if (!(lex->event_parse_data= Event_parse_data::new_instance(thd)))
MYSQL_YYABORT;
- lex->event_parse_data->identifier= $3;
+ lex->event_parse_data->identifier= $4;
lex->event_parse_data->on_completion=
Event_parse_data::ON_COMPLETION_DROP;
Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20090829182459-lucubzjj76sf3kf3.bundle
| Thread |
|---|
| • bzr commit into mysql-5.4 branch (Dao-Gang.Qu:3561) Bug#44331 | Dao-Gang.Qu | 29 Aug |
