MySQL Bugs: #42367: CREATE/DROP/ALTER EVENT does not work with --replicate-rewrite-db

Bug #42367	CREATE/DROP/ALTER EVENT does not work with --replicate-rewrite-db
Submitted:	27 Jan 2009 10:56	Modified:	3 Mar 2009 21:37
Reporter:	Johan Idrén	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.30	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
Events aren't rewritten according to --replicate-rewrite-db, leading to unexpected behavior.

How to repeat:
Setup replication master/slave pair. On slave use --replicate-rewrite-db="test->testrw"

On master
CREATE DATABASE TEST;
DELIMITER $$

DROP EVENT IF EXISTS `test`.`e_muppet`$$
CREATE EVENT `test`.`e_muppet`
   ON SCHEDULE
      EVERY 1 DAY
      STARTS curdate() + INTERVAL '8:07' HOUR_MINUTE
   DO
          SELECT 1$$
 
DELIMITER ;

On slave:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: msandbox
                  Master_Port: 22145
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1108
               Relay_Log_File: mysql_sandbox22146-relay-bin.000005
                Relay_Log_Pos: 1041
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1049
                   Last_Error: Error 'Unknown database 'test'' on query. Default database: 'testrw'. Query: 'CREATE EVENT `test`.`e_muppet`
   ON SCHEDULE
      EVERY 1 DAY
      STARTS curdate() + INTERVAL '8:07' HOUR_MINUTE
   DO
          SELECT 1'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 896
              Relay_Log_Space: 1566
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1049
               Last_SQL_Error: Error 'Unknown database 'test'' on query. Default database: 'testrw'. Query: 'CREATE EVENT `test`.`e_muppet`
   ON SCHEDULE
      EVERY 1 DAY
      STARTS curdate() + INTERVAL '8:07' HOUR_MINUTE
   DO
          SELECT 1'
1 row in set (0.00 sec)

Suggested fix:
Rewrite events on slave according to replicate-rewrite-db

Setting default database before creating the event makes the event to be successfully created on the rewritten database on the slave. Example:

1. Slave is started with --replicate-rewrite-db="bug_42367->bug_42367_slave"

2. On Master:
use bug_42367; CREATE EVENT e2 ON SCHEDULE EVERY 1 DAY STARTS curdate() + INTERVAL '8:07' HOUR_MINUTE DO SELECT 1;

3. On Slave:
show events from bug_42367_slave;
Db	Name	Definer	Time zone ...
bug_42367_slave	e2	@	SYSTEM ...

This does not seem like a bug.

Going through the manual, documentation on "--replicate-rewrite-db" shows:

"Tells the slave to translate the default database (that is, the one selected by USE) to to_name if it was from_name on the master. Only statements involving tables are affected (not statements such as CREATE DATABASE, DROP DATABASE, and ALTER DATABASE), and only if from_name is the default database on the master. (...)" 

Reference: http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html

Since the rewrite db is only supposed to work with default database selected (using the USE command), the presented case above, in the "how to repeat" section, fails. 

However, if default database is correctly set on the master before "create event" is issued, then the operation succeeds, meaning that the event is successfully created in the correct database on the slave.

If you think I missed something in my analysis (check also comments before this one), please point it out and reopen the bug.

I agree. I was mistaken, not a bug.
The behavior is the same as any other operation using --replicate-rewrite-db