Skip navigation links

Additional languages
19.3.1.6 Replication with Floating-Point Values »
Section Navigation      [Toggle]

19.3.1.5. Replication of Invoked Features

Replication of invoked features such as scheduled events, user-defined functions (UDFs), stored routines (including both stored procedures and stored functions), and triggers was re-implemented in MySQL 5.1.18 to provide the following characteristics:

  • The effects of the feature are always replicated.

  • The following statements are replicated using statement-based replication:

    • CREATE EVENT

    • ALTER EVENT

    • DROP EVENT

    • CREATE PROCEDURE

    • DROP PROCEDURE

    • CREATE FUNCTION

    • DROP FUNCTION

    • CREATE TRIGGER

    • DROP TRIGGER

    However, the effects of features created, modified, or dropped using these statements are replicated using row-based replication.

  • In the case of CREATE EVENT and ALTER EVENT:

    • The status of the event is set to SLAVESIDE_DISABLED on the slave regardless of the state specified (this does not apply to DROP EVENT).

    • The master on which the event was created is identified on the slave by its server ID. The ORIGINATOR column in INFORMATION_SCHEMA.EVENTS and the originator column in mysql.event were added to these tables in MySQL 5.1.18 to store this information. (See Section 27.20, “The INFORMATION_SCHEMA EVENTS Table”, and Section 12.5.5.16, “SHOW EVENTS”.)

  • The feature implementation resides on the slave in a renewable state so that if the master fails, the slave can be used as the master without loss of event processing.

To determine whether there are any scheduled events on a MySQL server that were created on a different server (that was acting as a replication master), use SHOW EVENTS, like this: 

SHOW EVENTS 
    WHERE STATUS = 'SLAVESIDE_DISABLED';

Alternatively, you might wish to query the INFORMATION_SCHEMA.EVENTS table as shown here: 

SELECT EVENT_SCHEMA, EVENT_NAME, ORIGINATOR
    FROM INFORMATION_SCHEMA.EVENTS
    WHERE STATUS = 'SLAVESIDE_DISABLED';

When promoting a replication slave having such events to a replication master, use the following query to enable the events: 

UPDATE mysql.event 
    SET STATUS = 'ENABLED' 
    WHERE STATUS = 'SLAVESIDE_DISABLED';

If more than one master was involved in creating events on this slave, and you wish to enable events that were created only on a given master having the server ID master_id, use the following query instead: 

UPDATE mysql.event 
    SET STATUS = 'ENABLED' 
    WHERE ORIGINATOR = master_id 
    AND STATUS = 'SLAVESIDE_DISABLED';

Important

Before executing either of the previous two UPDATE statements, you should disable the Event Scheduler on the slave (using SET GLOBAL EVENT_SCHEDULER = OFF;), run the UPDATE, restart the server, then re-enable the Event Scheduler afterwards (using SET GLOBAL EVENT_SCHEDULER = ON;).

If you later demote the new master back to being a replication slave, you must disable manually all events enabled by the UPDATE statement. You can do this by storing in a separate table the event names from the SELECT statement shown previously, or using an UPDATE statement to rename the events with a common prefix to identify them, as shown in this example: 

UPDATE mysql.event
    SET name = CONCAT('replicated_', name)
    WHERE status = 'SLAVESIDE_DISABLED';

When demoting this server back to being a replication slave, you can then rename and disable the events like this: 

UPDATE mysql.event
    SET name = REPLACE(name, 'replicated_', ''), 
        status = 'SLAVESIDE_DISABLED'
    WHERE INSTR(name, 'replicated_') = 1;