Binary log and stored program considerations

When Binlog records stored programs (stored procedures, stored functions, triggers, events), there may be a problem: statement replication mode: 1, a statement on master and slave will affect different records. 2, slave end of the SQL thread in the execution of statement, with all the permissions (without permission to check) may be a stored procedure on the master and slave side of the implementation of inconsistent results: for example:

Delimiter $$
Create FUNCTION Magic () return
char ($)
SQL security invoker
BEGIN
DECLARE Result char (6);
If @ @server_id <> 1 Then
Select what into result from secret.agents limit 1;
return result;
else return
' I am magic! ';
End If;
end$$

Similarly, if you do not change replication mode, consider using the SQL Security Definner keyword to strengthen the line of defense. 3, the stored program modified data is uncertain, this situation is not conducive to replication, may be the engine master and slave inconsistent.

If we replicate in row mode, this problem does not occur, and it only records row record information for the changed table. A stored procedure Call statement is not recorded, and the result of a stored function is recorded rather than the statement that invokes the function. For a trigger, it also records changes to the row records it makes.

The following rules apply only to stored functions, not to other stored programs 1, to create and modify a stored function, you must have super, create routine, alter routine permissions. 2. The stored function you create must explicitly indicate that the modification it makes is deterministic or that it does not modify the data. You need to have a few keywords in the statement: deterministic, NO sql,reads SQL DATA. Otherwise, you will be prompted with the following:

ERROR 1418 (HY000): This function has none of the deterministic, NO SQL,
or reads SQL DATA in its declaration and binary L Ogging is enabled (for your *might* want to use the
less safe log_bin_trust_function_creators
variable)

Here's what you can do:

CREATE FUNCTION F1 (i INT)
RETURNS int
deterministic
reads SQL DATA
BEGIN return
i;
End;

The following uses the UUID function, so it is not deterministic

CREATE FUNCTION F2 ()
RETURNS CHAR () CHARACTER SET UTF8
BEGIN return
UUID ();
End;

The intrinsic evaluation of a function is based on the creator of "good faith", and MySQL does not examine the statement that writes deterministic but produces uncertainty.

If we do not use deterministric, we must use the row replication mode or the mixed mode to ensure the consistency of master-slave replication.

By default, only the Super permission defines the stored function, setting the variable: log-bin-trust-function-creators option to allow other users to define their own function.

These discussions are equally applicable to trigger, but trigger does not have deterministic keywords.

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1071546