Federated Tables
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.
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:
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.
posted by Bob Field at 5:40 PM
3 Comments:
You should check if it still loads the whole query result in memory, because this can be a killer
Hi Bob,
nice to see you're checking out the federated storage enginge.
I just wanted to comment on your method of recreating the table. Although you can execute SHOW CREATE TABLE from a php script, you can also generate to exact table structure directly in MySQL using the information schema.
I admit, it's a little mork work, but you will never have to worry about any nasty occurrences of the words ENGINE, or CONNECTION in the output of the SHOW CREATE TABLE statement. And you can store it in a view or stored procedure and thus always access it from within the database.
One of the limitations I noticed is that the Federated engine checks keys in ways that the other engines do not. For example, if you have a key then the federated format requires that the field specify NOT NULL (even if the key is not unique)
Post a Comment
<< Home