Binary logging can be row-based, statement-based, or a mix of the two. See Binary Log Formats for more details on the formats. If logging is statement-based, it is possible that a statement will have different effects on the master and on the slave.
Stored routines are particularly prone to this, for two main reasons:
The problems with replication will only occur with statement-based logging. If row-based logging is used, since changes are made to rows based on the master's rows, there is no possibility of the slave and master getting out of sync.
By default, with row-based replication, triggers run on the master, and the effects of their executions are replicated to the slaves. However, starting from MariaDB 10.1.1, it is possible to run triggers on the slaves. See Running triggers on the slave for Row-based events.
The following only applies if statement-based binary logging is enabled. If binary logging is not enabled, or the logging format is either row-based or mixed, these limitations do not apply.
DETERMINISTIC
, NO SQL
or READS SQL DATA
, or else an error will occur. 1
- by default it is set to 0
. A deterministic function:
DELIMITER // CREATE FUNCTION trust_me(x INT) RETURNS INT DETERMINISTIC READS SQL DATA BEGIN RETURN (x); END // DELIMITER ;
A non-deterministic function, since it uses the UUID_SHORT function:
DELIMITER // CREATE FUNCTION dont_trust_me() RETURNS INT BEGIN RETURN UUID_SHORT(); END // DELIMITER ;
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/binary-logging-of-stored-routines/