Identify that a trigger in the slave has been invoked by a replicated statement

Question

How can I detect into a trigger in my slave that it has been invoked by a replicated statement in MySQL?

I've tried with USER() function, but it returns null when the trigger is activated by a replicated statement. Shouldn't it returns replication user (repl)?

Example:

CREATE TRIGGER `test`.`t1_BEFORE_INSERT` BEFORE INSERT ON `t1` FOR EACH ROW
BEGIN
    IF USER() LIKE 'repl@%'
    THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error';
    END IF;
END

I want the behaviour of the trigger to be different depending of it's activated by a replicated statement or not. I've configured statement based replication and I've tested that triggers run in my slave.

Answer 1

Try checking @@server_id. This is a global variable that must be unique for each MySQL instance in the replica-set.

Another option might be to change the trigger code on the replica. I don't usually like to do this, because I'd prefer that all schema objects are identical between the master and replica.

PS: You already know this, but for the benefit of other readers: note that replicated events do not execute triggers if you use row-based replication. This means you get mixed results if you used mixed-mode replication, because events will be logged in row-based format if the SQL is not safe for replication.

---

Re your comment:

Regarding testing the user, there is no user session in the replication thread. The replication thread is effectively runs without any limits with respect to the SQL privilege system (it can execute any change in the binary log stream), so there's no need for that thread to be associated with a user.

So it's not surprising that USER() returns NULL.

You could try testing for that:

IF USER() IS NULL THEN ...

By the way, there's actually no information in your question above that says that the trigger exists only on the replica.