WL#3629: Replication of Invocation and Invoked Features
Affects: Server-5.1 — Status: Complete — Priority: Medium
Replication of invocations and invoked features such as Events, User-Defined Functions (UDFs), and Stored Procedures must be redesigned. Specifically, replication of these features requires modification to the code and a unified design for handling the creation, modification, and deletion of the features and the effects of the invocations themselves. The basic principles are: • The effects of the features should always be replicated. • Replicate the CREATE, ALTER, and DROP statements (using SBR) but set the event to SLAVESIDE_DISABLED on the slave regardless of the state specified (except for DROP). • The feature implementation must reside 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. This worklog can satisfy, in whole or in part, the following bug reports: • BUG#16421: Events: Replication of effects doesn't work • BUG#17857: Events: Binary logging of events does not work (duplicate of 16421) • BUG#17671: UDF: CREATE FUNCTION binlogging problems • BUG#20384: Events: CREATE EVENT should be blocked on slave Suggested plan to move forward ------------------------------ 1. Document triggers, SP, SF, UDF, Events, views in a table. How they are replicated, when they are disabled on slave or not, is the trigger executed on the slave or not? Before pushing WL#3629, check what Kostja, Lars thinks about this design. 2. Think about definition of what is a server id. What things are controlled today by the server id? Should it be allowed to change it on slave when it becomes new master. I think not. 3. Can we use server id or do we need hostname, ip. I think originating server id in the event table. 4. Write up recommended procedure for doing fail-over, i.e. how the slave should be setup as the new master. 5. Slaveside_disabled. document that it means auto-disabled. Documentation ------------- See also WL#3727. RELATED BUGS: BUG#16421, BUG#17671, BUG#20384
In studying this problem, I asked myself what behavior I would expect if I were setting up replication for a server that used invoked features. By way of example, I considered several cases of using invoked features. I will examine each of these cases and present a use case for the behavior of replication under the stated conditions. While most of the examples are events, the use cases can apply to UDFs and Stored Procedures. In all cases, I assume the master is executing the invoked features successfully. Case A: Features the Generate Data that is Saved in the Database The data in the database is partially generated by some condition or external event. An example of this is shown below. The example shows an event that stores the row count of another table along with a timestamp. This event could be used to monitor the growth of a table over time. While somewhat trivial, the event shows that it is possible for users to store data created at specific time intervals. CREATE FUNCTION Get_Count() RETURNS INT DETERMINISTIC BEGIN DECLARE a INT; SELECT COUNT(*) INTO a FROM t2; RETURN a; END CREATE EVENT my_Event ON SCHEDULE EVERY 5 MINUTE STARTS NOW() DO INSERT INTO t1 VALUES (Now(), Get_Count()); Replication of the data generated by the event is needed in order to keep the master and slave tables in synch. However, replication of the execution of this event is not necessary or desired. In this case, the data inserted into table t1 can be replicated using row-based replication (RBR). Since it is the data that is important, the execution of the event is not necessary. Furthermore, as stated in the MySQL documentation, the use of time data in events can result in different values being generated on the slave. If the latency of replication (e.g., under heavy load) is 3 seconds, it is possible for the slave to execute an event 3 seconds later than the master. This would clearly generate different values for the timestamps. This use case applies to UDFs and Stored Procedures. However, there are three kinds of UDFs; 1) UDFs that are created using the normal CREATE FUNCTION syntax that include an implementation clause, 2) UDFs that are compiled in a loadable module (library) and instantiated using the CREATE FUNCTION with the SONAME option, and 3) UDFs that are compiled in the server code (called a native function). UDFs that meet the first type can be replicated as described above. UDFs that meet the second type can be replicated, but if the loadable library is not on the slave, replication will stop. It may be possible to implement a mechanism that the user can override this behavior, but the default should be to stop replication and produce an error. UDFs that meet the third type cannot be replicated and are the responsibility of the user to ensure both master and slave contain the native functions. Case B: The events trigger some performance and/or maintenance routines This is a case of using events to trigger stored procedures to handle routine maintenance of a database. For example, if you had a table used for lookups you could gain a considerable performance increase by using the HEAP storage engine. However, you may also have updates to this table that you want to make permanent. So instead of trusting the hardware not to fail or saving the data using triggers which fire for every insert or delete, you could periodically write the data to disk using an event (there are other methods to do this, but let’s stay with the event concept). This can save some processing time by doing the save as a single pass. It also gives the user the ability to schedule the event when the server is less busy. CREATE PROCEDURE Save_Lookup_Table () BEGIN START TRANSACTION; DROP TABLE IF EXISTS m1_saved; CREATE TABLE m1_saved AS SELECT * FROM m1; ALTER TABLE m1_saved ENGINE=MYISAM; COMMIT; END CREATE EVENT my_Event ON SCHEDULE EVERY 5 MINUTE DO CALL Save_Lookup_Table() Replication of this operation can be achieved using statement-based replication (SBR). If SBR is used, the queries inside the stored procedure will be written to the binary log and consequently copied to the relay log on the slave and then executed. It is interesting to note that while the stored procedure should also be replicated to the slave, it should not be disabled on the slave. If you consider that the stored procedure requires a user to invoke it, having it enabled on the slave is not a concern. Once again, the argument can be made that one should not want the event to execute on the slave. This would create a copy of the m1_saved table on the slave that may be out of synch (too many or too few rows) with the master. This case applies to UDFs (types 1 and 2) and Stored Procedures. Case C: I Want My Slave(s) to Respond to Queries (SELECT) Returning the Results of Data Generated by the Features This is where a user creates a slave not as a backup but rather as a read-only server in a multi-server environment. Users who use this technique are primarily interested in increasing query efficiency for a large number of connections. Thus replication is for distributed processing rather than data protection (it should be noted that it is possible to do both). Replication in this scenario, provided the effects of the features executed on the master are replicated, has little bearing on whether the events need to be enabled. In fact, I cannot think of a reason a user would want or need the events enabled on the slave in this scenario. Case D: The Master Crashes and I Want to Change My Slave to the New Master This situation is why we replicate! It’s all about the data. The slave should have a recoverable copy of all of the data on the master (recoverable since it is possible for the slave to fall behind the master during heavy use). This copy should include everything necessary to make a slave into a master should the need arise. I believe most enterprise customers will use replication for this purpose. While it is possible to have several slaves each replicating a portion (e.g., several databases) of the data on the master, it is more likely users will setup replication for hot swapping or backup capabilities. Switching a slave to a master is a simple process. If we consider disabling events on the slave, an additional step will have to be added to enable the features on the slave. Since this operation (converting a slave to a master) is a rare occurrence rather than a daily activity, the need for automation isn’t a high priority. Therefore it would be acceptable to create an easy way to ensure the replicated features on the slave can be enabled when needed. However, if the user has created a great number of features where some are disabled by choice, it may be difficult for the user to determine which should be enabled if the master is no longer accessible. There are several ways that this could be enhanced. The server could keep track of the features that are replicated (and thereby disabled). When the user converts a slave to a master, she can issue a query against the appropriate table and use that information to enable all of the disabled replicated features. Another method would be to add another state to the Status column for features. A SLAVESIDE_DISABLED state could be added thereby making it very easy for the user to enable all of the replicated features. This new state indicates the feature is disabled and was replicated from the master. Note: it can be argued that this third state is an internal-use only state and that it should not be settable by the user. However, it can also be argued that through the due course of troubleshooting and testing users may need to be able to set an event as SLAVESIDE_DISABLED. Thus, the sql_yacc.yy file (and associated token declarations) will also have to be changed to accommodate the changes to the CREATE EVENT and ALTER EVENT commands. In addition, A small change to RBR behavior was added. Replication of event timing changes are not replicated to the master. Replication of event timing changes was being binlogged whenever the master updated the timing of an event. This caused (in RBR only) a row update for the mysql.event table to be written to the binary log. Since the master had a status of 'ENABLED' for the row, the processing of the binlog on the slave was overwriting the status of 'SLAVESIDE_DISABLED' on the slave. The latest patch disables RBR of the next event in the Event_queue_element::update_timing_fields() method. The code used to do this is: if (thd->current_stmt_binlog_row_based) thd->clear_current_stmt_binlog_row_based(); This allows the master to update the timing field values and not write those to the binlog.
The above monologue was necessary to explain the premise for the following design. The following changes shall be made to the server: • The effects of features shall be replicated using RBR. Note: this may not require code changes to ensure this. • The possible status values for features (located in several source files) shall be modified to include the value SLAVESIDE_DISABLED. This may require changing several code files as the ENABLED enumeration is duplicated across the source code (i.e., see event_data_objects.* and sql_show.cc). This will be not be written to the binlog, rather it will be handled by the server code. • All features created on the master and replicated to the slave shall be set to SLAVESIDE_DISABLED and shall not execute on the slave. This change can be effected by adding code to the SQL thread on the slave. • The CREATE, ALTER, and DROP commands shall be replicated using SBR. Minor changes may be necessary to implement this. Note: The SLAVESIDE_DISABLED enum will require modifying the mysql.event table as follows: ALTER TABLE mysql.event MODIFY COLUMN status ENUM('ENABLED', 'SLAVESIDE_DISABLED', 'DISABLED') NOT NULL DEFAULT 'ENABLED'; If this is not accomplished, the server will issue errors on startup: [ERROR] (event) Expected field status at position 12 to have type enum ('ENABLED','SLAVESIDE_DISABLED','DISABLED'), found enum('ENABLED','DISABLED') [ERROR] SCHEDULER: Table mysql.event is damaged. Can not open
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.