MySQL stored programs, functions, and issues related to replication

1.MySQL 5.1 stored programs and functions work on replication?

Yes, the standard behavior performed in stored programs and functions is copied from the primary MySQL server to the from server.

2. Can the stored programs and functions created on the primary server be replicated to the server?

Yes, the stored programs and functions that are executed through General DDL statements, whose creation on the primary server is replicated to the server, will have a target of two servers. Alter and DROP statements for stored programs and functions are also replicated.

3. How does behavior occur in replicated stored programs and functions?

MySQL records each DML event that occurs in the stored program and function, and replicates these individual behaviors to the server. Actual calls to execute stored programs and functions are not replicated.

4. Do you have any particular security requirements for using stored procedures, functions, and replication?

Yes, because a statement that has permissions from the server to perform any read of the binary log of the autonomic server, the specified security constraint exists because of the stored programs and functions used with replication. If the replication or binary log is largely active (for the purpose of point-in-time recovery), then the MySQL DBA has two security options to choose from:

Any user who wants to create a stored program must be given super privileges.

As a choice, a DBA can set the log_bin_trust_routine_creators system variable to 1, and it will allow people with standard create routine permissions to create a stored program and function.

5. What are the restrictions on the behavior of copying stored programs and functions?

An indeterminate (random) or time base row that is embedded in the stored program cannot be copied appropriately. Random results, just because of their nature, are predictable and cannot be cloned. Therefore, copying to random behavior from the server will not mirror those that are generated on the primary server. Note that declaring a stored program or function as deterministic or setting the system variable to 0 in Log_bin_trust_routine_creators will allow the immediate value operation to be invoked.

In addition, the time base behavior cannot be reproduced from the server because the time-based behavior that is timed by the binary log used for replication in the stored program is not possible to reproduce because the binary log only records DML events and does not include timing constraints.

Finally, an error occurs in a non-interactive table in a large DML behavior (such as a mass insert) that may undergo replication, and the primary server can be partially updated from the DML behavior in the replicated version of the Non-interactive table. However, because of the error that occurred, there is no update from the server. The DML behavior of the function, the workspace will be executed with ignore keywords so that the error updates are ignored on the primary server, and the wrong updates are not replicated to the server.