Thanks Giving Challenge: How to detect replication context

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

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.

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”

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