MySQL – Federated Tables and Triggers

federated-tableMySQLremotetrigger

This is the scenario.

Have two MySQL servers (S1, S2) on different machines, with a database on each (DB1, DB2).

I have a table (T2) on DB2 that needs to "fetch" rows from another table (T1) on DB1.

I have created a temporary table (base on DB1 and federated on DB2), so when I insert a row on T1 which complies with some requirements I copy to my DB1 temporary table.

That's OK.

With the federated table on DB2 I get the row I need for T2.

I presumed that with a trigger on the federated temporary table of DB2 it's possible to insert that row to T2, but the trigger never fires.

Any suggestions?

Best Answer

You should not put any faith in triggers when it comes to federated tables. You are better off creating a temporary trigger on the real MyISAM table on the source side.

Picture this:

On Server S1 - T1 is MyISAM table - T2 is FEDERATED table to T1 over on S2

Do the following:

  • Create the Trigger on T1 to insert a row into T2
  • Insert Data into T1
  • Drop the Trigger

ALTERNATIVE

You would be way better off using MySQL Replication as follows:

  • S1 is the Master
  • S2 is the Slave
  • Set replicate-do-table=db1.T1 in my.cnf on S2

This would automatically do all DML against that table provided you replicate to the same named table.

Related Question