WL#3727: Specification of Invoked Objects

Define a specification and tests for invoked objects as implemented in WL#3629.

File: Failover:
============================================================================

Replication Failover
--------------------
One of the greatest advantages of MySQL replication is the ability to failover 
in the event of a server crash. More specifically, if you need to take your 
master server offline you can promote one of your slaves as a master and 
thereby minimize the interruption to your users.

Promoting a Slave to a Master
-----------------------------

When your master fails beyond repair, you can quickly replace it with your 
slave and reestablish service to your databases and applications. The process 
for promoting a slave to a master involves taking the master offline (if not 
already), flushing the logs and safely shutting down the slave, then restarting 
the slave to run as the master. A simplified process is shown below.

1. Lock the tables on your master.
   [ If the master has failed beyond repair, it can be assumed that it
     cannot be operated. Hence this step does not make sense. /Matz]
2. Record the location of the binlog on the master for point in time recovery 
(if needed).
3. Flush the logs on the slave.
4. Shutdown the master.
5. Shutdown the slave you want to promote.
6. Restart the slave specifying the startup options for the master.

[ The procedure should probably be something along these lines:
  1. Make sure that no SQL thread is applying events by waiting for them to
     apply the relay log, or stop the slave explicitly.
  2. Identify what slave should be promoted to master by figuring out which
     slave is most current (I have a script for that.)
  3. For each of the other slaves, calculate the position on the new master
     that the position on the slave corresponds to (need to figure out 
     exactly how).
  4. Redirect each of the slaves to the new master.
  5. Start the slaves.

Note that if you add the --log-slave-updates option to all slaves, there is no
need to shut down any server at all. /Matz ]

Notes
-----

Your configuration may require slightly differing steps in the process to match 
your environment. Things to consider include how the applications connect to 
the server, where the data is store (NAS or other detached storage), and the 
mode of replication among your master and slave(s).

Remember to keep the server_id the same as it was when the promoted slave was a 
slave!

[What do you mean? The server_id should not change for a server regardless of
what role it has /Matz]

If you have applications that use embedded hostnames or IP addresses in their 
connections to the master, you have two choices; 1) you can change the slave’s 
hostname and IP to that of the master and restart the server, or 2) you can 
redirect your clients to the promoted slave.

Failover and Invoked Objects
----------------------------

For most applications, the failover sequence described above will get you a 
viable master and return your database system to service. If you use invoked 
objects, specifically events, the promotion of the slave to a master involves 
some additional steps.

If you are unsure if you have any events that are replicated, you can issue the 
following command on the slave (you need to have privileges to run the show 
commands)::

  SHOW EVENTS WHERE STATUS = 'SLAVESIDE_DISABLED';

This query will list all of the events on the slave that have been replicated 
from the master. You will also see a column named ORIGINATOR that lists the 
server_id of the originating master. Together with the STATUS column, you can 
quickly determine which events need to be activated on the newly promoted slave.

To turn the events on, you can create and run the following stored procedure::

  CREATE PROCEDURE enable_master_events(IN master_id INT)
  BEGIN
    UPDATE mysql.event SET STATUS = 'ENABLED' 
    WHERE ORIGINATOR = master_id AND STATUS = 'SLAVESIDE_DISABLED';
  END

This procedure requires you to provide the server_id of the master you are 
placing. The procedure then updates the status of the events in the mysql.event 
table to ENABLED for those events that are currently set to SLAVESIDE_DISABLED. 
Once this procedure is run, the promoted slave can take over for the master.

You should turn the event scheduler off before calling this procedure then 
restart your server and renable the event scheduler. For example:

1. SET GLOBAL EVENT_SCHEDULER = OFF;
2. CALL enable_master_events(1);

3. SET GLOBAL EVENT_SCHEDULER = ON;

Once the server has been restarted and the event scheduler enabled, the events 
replicated from the master will be turned on with a status of ENABLED.

Note: If you have events that are replicated from the master that you want run 
on the slave and have taken steps to do so (by changing the status from 
SLAVESIDE_DISABLED to ENABLED), you should make a note of all of these special 
events prior to running this stored procedure. For these events, you will have 
to manually enable them when you return a promoted slave to a slave if you use 
the stored procedure described in the next section. 

Returning a Promoted Slave to a Slave
-------------------------------------

The process for returning a promoted slave to a slave involves ensuring the 
data on your promoted slave is consistent and copied to the master. 
Furthermore, you should ensure the promoted slave is restarted once again as a 
slave to the restored master. The process involves taking the promoted slave 
offline, flushing the logs and safely shutting down the slave, restarting the 
master, then restarting the slave and connecting to the master. A simplified 
process is shown below.

1. Lock the tables on your promoted slave.
2. Record the location of the binlog on the promoted slave for point in time 
recovery (if needed).
3. Shutdown the slave.
4. Copy the data from the promoted slave to the master.
5. Restart the master.
6. Restart the slave specifying the startup options for the master.

[ Again, there is normally no need to shut down the servers /Matz ]

Notes
-----

Your configuration may require slightly differing steps in the process to match 
your environment. Things to consider include how the applications connect to 
the server, where the data is store (NAS or other detached storage), and the 
mode of replication among your master and slave(s).

Promoted Slaves and Invoked Objects
-----------------------------------

If you have replicated events that you have enabled using the stored procedure 
described above, you need to disable them on the promoted slave before 
returning the slave to service.

To turn the events off on the slave (disable them), you can create and run the 
following stored procedure::

  CREATE PROCEDURE disable_master_events(IN master_id INT)
  BEGIN
    UPDATE mysql.event SET STATUS = 'SLAVESIDE_DISABLED' 
    WHERE ORIGINATOR = master_id AND STATUS = 'ENABLED';
  END

This procedure requires you to provide the server_id of the master you are 
placing. The procedure then updates the status of the events in the mysql.event 
table to SLAVESIDE_DISABLED for those events that are currently set to ENABLED. 
Once this procedure is run, the promoted slave can return to being a slave.

You should turn the event scheduler off before calling this procedure then 
restart your server and re-enable the event scheduler. For example:

1. SET GLOBAL EVENT_SCHEDULER = OFF;
2. CALL disable_master_events(1);

3. SET GLOBAL EVENT_SCHEDULER = ON;

Once the server has been restarted and the event scheduler enabled, the events 
replicated from the master will be turned off and returned to a status of 
SLAVESIDE_DISABLED.

File: Invoked_Objects
============================================================================

Replication and Invoked Objects
-------------------------------

Invoked objects are those features in MySQL that can be created by users or 
developers which can be called or invoked either manually or automatically 
(e.g., events). These features include triggers, events, stored procedures, and 
functions. All of these objects are replicated in a normal master/slave setup.

The following sections describe how each is replicated and executed on the 
master and the slave. Note: RBR refers to row-based replication and SBR refers 
to statement-based replication. 

Events
------

Events are replicated to ensure a successful failover of the slave to replace 
the master. However, replicating events presents a problem. Since events are 
objects that can execute automatically, it would be incorrect to allow them to 
execute on both the master and the slave. 

This is especially true for events that can generate or alter data. Therefore, 
events created on the master are replicated to the slave where they are 
disabled with a special status of SLAVESIDE_DISABLED and a new field is added 
named originator that records the server_id of the originating server. This 
allows the failover of a slave to a master and the return of the promoted slave 
to a slave.

Thus, when the CREATE and ALTER commands are executed on the slave, they 
disable the event on the slave setting the status field to SLAVESIDE_DISABLED. 

Note: You should never change the server_id of a server. Each server must have 
its own unique server_id. Changing the server_id can cause problems during 
failover and replication.

RBR: Events are replicated from the master are not executed on the slave. 
However, any commands in the body of the event which produce data are 
replicated to the slave in form of the row-level results.

SBR: Events replicated from the master are not executed on the slave. However, 
any commands in the body of the event which produce data are replicated to the 
slave in form of commands that are executed on the slave.

Triggers
--------

Triggers are replicated from the master and executed on the slave. The form of 
execution differs depending on the format of the replication (row- or statement-
based). The following describes the differences in replication depending on the 
mode.

RBR: Triggers are not fired on slave. Instead, any data changes resulting from 
firing an event on the master are logged and replicated on the slave. However, 
the trigger creation commands CREATE, ALTER, and DROP are replicated by 
executing the command on the slave.

SBR: The trigger creation commands CREATE, ALTER, and DROP are replicated so 
that the slave has all of the triggers that exist on the master. When a command 
that invokes a trigger is fired on the master, the same command is executed on 
the slave and likewise fires the trigger on the slave.

Stored Procedure
----------------

Store Procedures are replicated from the master and executed on the slave. The 
form of execution differs depending on the format of the replication (row- or 
statement-based). The following describes the differences in replication 
depending on the mode. In all cases, the CREATE, ALTER, and DROP commands are 
replicated and executed on the slave.

RBR: The stored procedure is executed on the master and the statements that 
make up the body of the procedure are replicated to the slave and executed.

SBR: The stored procedure is executed on the master and the statements in the 
body of the stored procedure are replicated to the slave and in turn executed 
(called) on the slave.

Stored Function
---------------

Store Functions are replicated from the master and executed on the slave. The 
form of execution differs depending on the format of the replication (row- or 
statement-based). The following describes the differences in replication 
depending on the mode. In all cases, the CREATE, ALTER, and DROP commands are 
replicated and executed on the slave.

RBR: The stored function is executed on the master and the statements that make 
up the body of the procedure are replicated to the slave and executed.

SBR: The stored function is executed on the master and the call to the stored 
function is replicated to the slave and in turn executed (called) on the slave.

UDF/Library Function
---------------------

The CREATE and DROP commands for UDF Library Functions are executed on the 
master and replicated to the slave and then executed. Since UDF Library 
Functions have no SQL body, execution is via the statements that call the 
function. The form of execution differs depending on the format of the 
replication (row- or statement-based). The following describes the differences 
in replication depending on the mode.

RBR: Normally a statement using such function is not replicated but affected 
rows are. The row binlog entries contain results of a function. The function is 
never called on slave.

SBR: It is expected that the same functions are defined on master and slave, 
otherwise replication may break. A statement calling a UDF Library Function is 
executed both on master and slave and the function is called on both servers.

UDF/Native Functions
--------------------

UDF/Native functions are not replicated. However, execution of the function may 
occur on the slave in SBR or RBR.

Quick Reference
---------------

The following table is a quick reference for how invoked objects are replicated 
in MySQL. The columns indicate the actions that occur on either the master or 
slave. The data includes descriptions of how the objects are created and 
destroyed, how the master logs the invocation, and how the slave executes the 
invocation from the relay log. This table applies to MySQL version 5.1.15 and 
greater.

In the table below, B indicates the body of object is logged/executed and C 
indicates the call is logged/executed.

-----------------------------------------------------------------------
|           | Master      | Slave        | Master     | Slave         |
|           | Logging     | Execution    | Logging of | Execution of  |
|           | of Create   | of Create    | Invocation  Invocation     |
-----------------------------------------------------------------------
| Event     | Y           | Y (disabled) | B          | B             |
-----------------------------------------------------------------------
| Trigger   | Y           | Y            | B          | B             |
-----------------------------------------------------------------------
| Stored    | Y           | Y            | RBR: B     | RBR: B        |
| Procedure |             |              } SBR: C     | SBR: C        |
-----------------------------------------------------------------------
| Stored    | Y           | Y            | RBR: B     | RBR: B        |
| Function  |             |              | SBR: C     | SBR: C        |
-----------------------------------------------------------------------
| UDF       | Y           | Y            | B          | B             |
| (library) |             |              |            |               |
-----------------------------------------------------------------------
| UDF       | N           | N            | N (only    | N (only       |
| (native)  |             |              | results)   | results)      |
-----------------------------------------------------------------------

Notes
There are three types of User-Defined Functions (UDFs); 1) Stored functions 
that are defined by the user and contain a SQL body with executable statements, 
2) UDFs that are compiled in a loadable library, and 3) native or built-in 
functions that are added to the base server code. Native functions pose an 
additional limitation where both the master and slave must contain the native 
function code.