Federated tables and triggers

Question

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?

Answer 1

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.

• jila: Thanks, Im going to try the table replication alternative.
• RolandoMySQLDBA: I have a typo. The option replicate-do-table is for S2 not S1. I'll correct it now.

Answer 2

You appear to be using the phrase "temporary table" to describe something that is not, in fact a true "temporary table."

I have created a temporary table (base on DB1 and federated on DB2)

This is impossible, because a temporary table cannot be federated in MySQL. I am going to assume that you are using the word "temporary" to mean some kind of work table that you are using for temporarily storing the results of some type of intermediate workflow.

So, this answer assumes the word "temporary" is removed from the question.

The server S1 has a base table and server S2 has a federated table connected to that table. It seems as if you are asking why a trigger on S2's federated table doesn't react to changes made to the base table on S1 (the target of the federated connection).

A trigger on a federated table will only fire in response to queries run on S2 that insert, update, or delete from the federated table on the server (S2) where the federated table is defined. It will not fire for queries run against the base table on S1.

• When a base table on S1 is the target of a federated connection, the server at S1 does not know that its table has been federated.
• The federated storage engine uses an ordinary client connection from S2 to S1 to issue queries against the table on S1. You will see this connection in SHOW PROCESSLIST on S1.
• No stateful information is maintained on S2 about the contents of the base table on S1, and the federated engine retains no memory about the contents of the remote table. Even from one query to the next, nothing about the table is retained on S2.
• For all of these reasons (and probably others) there is no mechanism for S2 to be alerted to changes on S1.

If you need for actions on S1 to cause changes on S2, you'll have to federate at least one table in the other direction, where a base table on S2 appears as a federated table on S1 so that triggers firing elsewhere on S1 can modify the tables on S2 as needed via the federated connection.

Note, however, that although this will work, it may not be a good idea in practice, depending on the level of system reliability and availability that you need, compared with the importance of the need for synchronization -- because you now have two servers that require each other to be up and available -- otherwise the queries firing the triggers will result in errors occurring if the remote table isn't available for any reason, and the remote server's lack of availability will cause unexpected errors in your application, which have to be handled gracefully.

The federated storage engine is one of my favorite things in MySQL, but understanding how it works under the hood is critical for identifying appropriate applications for it and for understanding why it sometimes doesn't do what you might expect.

• jila: Thanks, Im going to try something with table replication istead of federated tables.