Trouble with triggers and insert .. on duplicate key update

Question

I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For simplicity lets say the table schemas look like

create table main_table(
    pk int unsigned not null primary key,
    value int unsigned
);

create table reporting_table(
    pk int unsigned not null primary key,
    value int unsigned
);

I have two triggers, one AFTER INSERT, and one AFTER UPDATE that look like

create trigger reporting_insert after insert on main_table
    for each row 
        insert into reporting_table (id,value) VALUES
        (NEW.id, NEW.value);

create trigger trigger reporting_update after update on main_table
    for each row 
        update reporting_table  set
            value=NEW.value
        where
            id=NEW.id;

The main table is populated with queries like

insert into main_table values (1,10) on duplicate key update value=value+1;

My problem is the reporting table is not catching this ODKU +1 for the value (and it clearly is updating in the main table).

From the trigger docs:

A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger will activate for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.

From reading that I would think it would follow the latter path executed any Before/After update triggers. Doing a straight UPDATE query on main_table does result on the changes getting propagated to the reporting table.

What am I missing?

Answer 1

This question came back on my radar after a recent comment was posted.

This issue at the time turned out to be this reporting table that was meant to be maintained by triggers was on a slave only.

The insert on duplicate key statements were getting flagged as not safe for statement based replication and getting pushed through in RBR (the stream was in mixed mode).

RBR events do not fire triggers.

Answer 2

I have tried the same what you were trying to do and I got the required result,actually there are some problems in defining the triggers.I made correction and the corrected triggers are following.Use these instead of your triggers and then perform insert or update operation,m sure your problem will be solved.

delimiter ;;
create trigger reporting_insert after insert on main_table
    for each row 
      begin
        insert into reporting_table set
            id=NEW.id,
            value=NEW.value;
end;;
delimiter ;;

create trigger reporting_update after update on main_table
    for each row 
      begin
        insert into reporting_table set
            id=NEW.id,
            value=NEW.value;
end;;

insert into main_table values (1,10) on duplicate key update value=value+1;