When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication. Two sets of function characteristics apply here:
* The DETERMINISTIC and NOT DETERMINISTIC characteristics indicate whether a function always produces the same result for given inputs. The default is NOT DETERMINISTIC if neither characteristic is given, so you must specify DETERMINISTIC explicitly to declare that a function is deterministic.
Use of the NOW() function (or its synonyms) or RAND() does not necessarily make a function non-deterministic. For NOW(), the binary log includes the timestamp and replicates correctly. RAND() also replicates correctly as long as it is invoked only once within a function. (You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.)
SYSDATE() is not affected by the timestamps in the binary log, so it causes stored routines to be non-deterministic if statement-based logging is used. This does not occur if the server is started with the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW().
* The CONTAINS SQL, NO SQL, READS SQL DATA, and MODIFIES SQL DATA characteristics provide information about whether the function reads or writes data. Either NO SQL or READS SQL DATA indicates that a function does not change data, but you must specify one of these explicitly because the default is CONTAINS SQL if no characteristic is given.
Partager