October 1, 2014

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 :)

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. 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
    https://edge.launchpad.net/is-replication-status

    Cheers

    Giuseppe

  2. peter says:

    Guiseppe,

    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.

  3. 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.

  4. Isotopp says:

    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)

  5. James Cohen says:

    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”

  6. peter says:

    Isotopp,

    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)
    VALUES (NOW(), CONNECTION_ID(), CURRENT_USER(), ‘UPDATE’, OLD.id);

    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.

  7. peter says:

    James,

    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.

  8. Solution:
    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!

    Shlomi
    ————-

    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);

    DELIMITER $$

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

    INSERT INTO replication_status SELECT
    CASE COUNT(DISTINCT(uuid))
    WHEN ‘1’ THEN ‘master’
    WHEN ‘2’ THEN ‘slave’
    END
    FROM uuid_test;
    END $$

    DELIMITER ;

    call deduce_whoami();

  9. 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

    Regards
    Shlomi

  10. peter says:

    Shlomi,

    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:)

  11. Robert Hodges says:

    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: https://forge.continuent.org/jira/browse/TREP-130)

    Cheers, Robert

Speak Your Mind

*