A number of people have been recently been inquiring about MySQL FEDERATED tables or are having requirements that seem ideally met using FEDERATED tables, so I thought I would perform some testing and better acquaint myself with the behavior of this storage engine. The first thing I noticed was the spot in the documentation that said:The structure of this table must be exactly the same as that of the remote table, except that the ENGINE table option should be FEDERATED and the CONNECTION table option is a connection string that indicates to the FEDERATED engine how to connect to the remote server.
So I wrote a little PHP script that does a SHOW CREATE TABLE on the base table, alters the ENGINE clause, and adds the appropriate CONNECTION clause. After all if it has to be exactly the same, I may as well automate it and eliminate typos as a source of error.
Then I wrote another PHP script which created a series of base tables, and connected to each of these a series of FEDERATED tables with obvious differences. Lo and behold, it works! The only actual requirements I have been able to discern are:The base table has to exist. The FEDERATED engine checks this at table creation time.Columns in the FEDERATED table have to have columns by the same name in the base table.
Column types don't have to match. I haven't tried all the combinations, but I have gotten it to convert dates to strings, numbers to strings, strings to numbers, etc. I have also successfully connected to views.
The question was posed in the forums as to what connections are made and how long they persist. A connection is made briefly, obviously, during table creation since the base table is checked for existence. A persistent connection is made when the table is queried, and hangs about for a period of time controlled by wait_timeout
on the remote server. On my system this is set to 28,800 or exactly 8 hours.
So all in all this engine seems like a fairly flexible and useful tool, and beyond the obvious function of making tables availabe to remote servers, can be used to do some data type-casting as well.