Thanks Giving Challenge: How to detect replication context

Happy Thanksgiving and little holiday challenge for you.
Say you have a trigger on the slave which you would like to work differently, depending on whenever update is executed via replication thread vs updating table locally ? This can be helpful for example for auditing updates which were done directly instead of coming from the master and some other cases.
Suggest how you would do it by commenting 🙂

Share this post

Comments (12)

  • Reply

    before 5.1, you can user the @@server_id variable.

    create table t1 (id int, c char(10));
    create trigger t1_bi before insert on t1 for each row set new.c=case when @@server_id=1 then ‘master’ else ‘slave’end;
    insert into t1 (id) values (1);
    Query OK, 1 row affected (0.00 sec)

    select * from t1;
    | id | c |
    | 1 | master |

    switching to the slave:
    select * from t1;
    | id | c |
    | 1 | slave |

    In 5.1, you can also parse INFORMATION_SCHEMA.PROCESSLIST, although is much more work.

    select COUNT(*) from PROCESSLIST where USER=’system user’;
    | COUNT(*) |
    | 2 |

    You can also use a plugin that allows you to read SLAVE STATUS in a stored routine



    November 26, 2008 at 7:43 pm
  • peter Reply


    Good. Though this needs to hardcoding IDs which are master and the slave which may not be possible for chained replication – with same task of spotting which updates go directly.

    Regarding SHOW – I really would like for someone at MySQL to spend a week or so to ensure all these commands like SHOW available via select queries.

    November 26, 2008 at 8:19 pm
  • Scott Noyes Reply

    If it’s acceptable that the master and the replication thread on the slave do the same task, then in 5.1 you can put one trigger on the master, a different trigger on the slave, and use ROW binlog_format. The master and the replication thread will do whatever the master’s trigger says; direct updates will do whatever the slave’s trigger says.

    November 26, 2008 at 8:46 pm
  • Isotopp Reply

    The SQL Thread executes as system user, so looking at current_user/session_user will tell you if you are executed by the SQL Thread (on the slave) or a regular thread (on the master)

    November 27, 2008 at 2:16 am
  • James Cohen Reply

    As others have said you could use the contents of the USER() function’s return value to see if it’s the system user.

    Another way would be to rowcount `SHOW PROCESSLIST` or `information_schema`.`PROCESSLIST`
    WHERE Id=CONNECTION_ID() AND User=”system user”

    November 27, 2008 at 6:22 am
  • peter Reply


    This is what I tried (in 5.0) however it does not seems to work well with triggers:

    CREATE TRIGGER topic_update After UPDATE ON topic
    FOR Each Row INSERT INTO topic_log (ts, connection_id, usr, operation,pk)

    mysql> select * from topic_log;
    | ts | connection_id | usr | operation | pk |
    | 2008-11-27 13:17:41 | 253944746 | root@localhost | UPDATE | 4160633 |
    | 2008-11-27 13:17:41 | 253944746 | root@localhost | UPDATE | 5394938 |
    | 2008-11-27 13:17:41 | 253944746 | root@localhost | UPDATE | 4628914 |
    | 2008-11-27 13:17:41 | 253944746 | root@localhost | UPDATE | 5399979 |
    | 2008-11-27 13:17:41 | 253944746 | root@localhost | UPDATE | 4136666 |
    | 2008-11-27 13:17:41 | 253944746 | root@localhost | UPDATE | 4749235 |

    So it shows root@localhost rather than system user which was used in this case.

    November 27, 2008 at 11:20 am
  • peter Reply


    True you can do the join to the processlist table in 5.1 or 5.0-percona though this is a bit overkill to do for single row update statements I think 🙂

    I showed example with broken CURRENT_USER() USER() works just “strange” on 5.0.67 – it will show “empty” for a while for replication thread but as soon as I insert any row directly it switches for root@localhost (the guy who did insert.

    November 27, 2008 at 12:23 pm
  • Shlomi Noach Reply

    Use the UUID function twice. First time, use it as a function which does not replicate well. Second time, allow it to replicate as it were on the master.
    Compare the last part of both results: that’s the host part. If both are equal, we are on the master. If different – we are on the slave.
    The following stored procedure writes ‘master’ or ‘slave’ in the `replication_status` table. It can be called from a trigger, of course.

    Peter – great challange!


    DROP TABLE IF EXISTS uuid_test;
    DROP TABLE IF EXISTS replication_status;
    CREATE TABLE uuid_test (uuid VARCHAR(12) NOT NULL);
    CREATE TABLE replication_status (whoami VARCHAR(12) NOT NULL);


    DROP PROCEDURE IF EXISTS `test`.`deduce_whoami` $$
    CREATE PROCEDURE `test`.`deduce_whoami` ()
    TRUNCATE TABLE uuid_test;
    TRUNCATE TABLE replication_status;
    SELECT UUID() INTO @current_uuid;
    INSERT INTO uuid_test VALUES(SUBSTRING_INDEX(@current_uuid,’-‘,-1)) ;

    INSERT INTO replication_status SELECT
    WHEN ‘1’ THEN ‘master’
    WHEN ‘2’ THEN ‘slave’
    FROM uuid_test;
    END $$


    call deduce_whoami();

    November 29, 2008 at 7:16 am
  • Shlomi Noach Reply

    Peter (comment #6) – Try using USER(). In triggers, CURRENT_USER() will relate to the ‘definer’ of the trigger.
    USER() will relate to the invoker of the trigger


    November 29, 2008 at 10:35 pm
  • peter Reply


    Thanks. I tried that too. The results were very strange. It was returning “” for replication events until I did an update to this table besides replication. In this case it changed to the user which did update even for thread which did updates. But yes this is exactly what I’d expect to work:)

    November 29, 2008 at 11:06 pm
  • Robert Hodges Reply

    Hi Peter,

    This is a very interesting question. I would say cheat and use a different replication product (Tungsten Replicator) that automatically sets a session variable as follows whenever it logs in to apply changes to the slave:

    set @rep_status=’slave’;

    On a non-slave connection this value will be null, whereas the slave connection will have the preceding value. I just added a JIRA to get this added to Tungsten Replicator and will try to get it into our beta-3 build, which is due for later in the month of December. (JIRA reference:

    Cheers, Robert

    December 1, 2008 at 10:52 am

Leave a Reply