1

Newbie questions. I'm working on a MySQL project that will use only row-level binary logging. There will be no replication. Since logging is row-level, does it even matter if a function is deterministic? From the manual, my understanding is that it does not.

Added 6/9/20:

I've added an example which I think would be non-deterministic; please tell me if I'm wrong. The result will always be the same for a given state of the data, returning the highest date value in a set of rows, but would not always return the same value for the same input of a given patient id, since that changes over time. Is that right?

CREATE FUNCTION `last_enc_date`(patient INT) RETURNS date
BEGIN
# Returns the date of the most recent encounter (of any kind) for patient
DECLARE lastvisit DATE;
SELECT MAX(DATE)
    INTO lastvisit 
    FROM encounters 
    WHERE personid = patient;
RETURN lastvisit;
END
1
  • 1
    Your addition should probably be a separate question, but: that function is certainly non-deterministic. Whether misdeclaring it is a big deal or not depends on how frequently the column in question is updated and what isolation level is in use. For example, if last_enc_date is updated nightly by a batch job, workloads running during the day won't be sensitive to that at all. – mustaccio Jun 10 '20 at 1:57
1

Your understanding is correct. RBR is safe even with non-deterministic functions.

1
  • Thank you both, mustaccio and Gordon. I think both points are useful, i.e. that a non-deterministic function will be safe for restoration or replication with a RBR binary log, but wrongly declaring a function as ND might cause problems. I've added an example to my original question, and if you could clarify whether that would be D or ND I would appreciate it. – Mike Blyth Jun 10 '20 at 1:05
1

The question is, does it matter for what? The fact that non-deterministic functions are considered unsafe for statement-level replication is just one side effect of them being non-deterministic; there are other effects that apply regardless of replication: they are related to the function call optimization. Specifically, a function declared as deterministic may not be called as often during query execution, thus improving query performance. For example, a deterministic function in a where clause will be called only once (unless it references a table column); a non-deterministic one will be called for every row. There are more potential effects explained in documentation.

In other words, misdeclaring a deterministic function as non-deterministic might hurt query performance; misdeclaring a non-deterministic function as deterministic might lead to incorrect query results.

6
  • The context of the question makes most of this answer completely irrelevant. In terms of replication safety for non-deterministic function, RBR doesn't suffer from non-determinism because for each row change, it stores the original row as it was, and the row it is being replaced with. There is no function to interpret regardless of determinism. – Gordan Bobić Jun 9 '20 at 14:46
  • It seems to me the context of the question makes your comment completely irrelevant, since, as the OP says, there is no replication involved. – mustaccio Jun 9 '20 at 14:54
  • You seem to have gone off on a chain of assumptions without any actual basis for any of them. The simplest explanation for the binary logging without replication is point-in-time restores/rollbacks. The OP's concern seems to be regarding data correctness. – Gordan Bobić Jun 9 '20 at 18:21
  • And so did you, no? – mustaccio Jun 9 '20 at 18:43
  • Thank you both, mustaccio and Gordon. I think both points are useful, i.e. that a non-deterministic function will be safe for restoration or replication with a RBR binary log, but wrongly declaring a function as ND might cause problems. I've added an example to my original question, and if you could clarify whether that would be D or ND I would appreciate it. – Mike Blyth Jun 10 '20 at 1:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.