MySQL Federated Tables: The Missing Manual
Pages: 1, 2, 3, 4, 5

Federating Different Things

So far, I have shown federated tables on a peer-to-peer basis; that is, as a link between two tables in two different MySQL 5.0 servers. That may be the most frequent case, but it does not have to be. There are other objects that you can federate, provided that they look like a MySQL 5.0 table.

Federating Tables On Older MySQL Servers

One thing that looks like a MySQL 5.0 table (in the federated engine's limited view) is a MySQL 4.x or 3.23 table. Because the federated table course of action is currently quite crude, any table on the other side would do, provided that it belongs to a server that can answer to the federated engine basic requests.

If you don't have a 3.23 server hanging around, install one through the MySQL Sandbox:

$ ./express_install /opt/mysql/3.23.58 \
    -d server3 -P 3230 -c log=general.log \
    -c log-slow-queries -c log-long-format

Then, after loading the MySQL world sample database on server3, make three federated tables from server2, as you did for server1. (Don't forget to add indexes for City.CountryCode and Country.Name.)

Now you can enjoy the unique pleasure of issuing a nested query on a couple of MySQL 3.23 tables and getting a meaningful result:

server2 (world) > SELECT * 
    -> FROM City 
    -> WHERE CountryCode = (
    -> SELECT Code FROM Country WHERE Name = 'Afghanistan');
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.01 sec)

The federated engine did you a favor, turning your nested query into something that the old engine understands:

$ tail ~/server3/data/general.log
 1 Connect  server2usr@localhost on world
 1 Query     SHOW TABLE STATUS LIKE 'City'
 2 Connect  server2usr@localhost on world
 2 Query     SHOW TABLE STATUS LIKE 'Country'
 2 Query    SELECT `Code`, `Name`, ...  FROM `Country` WHERE  (`Name` = 'Afghanistan')
 1 Query    SELECT `ID`, `Name`, ... FROM `City` WHERE  (`CountryCode` = 'AFG')

Think about the possibilities. You can federate any existing MySQL table and use this link to suit several purposes. For example, you can consolidate data from several sources into a data warehouse, or you can just check from a centralized point whether all of your servers are working properly.

Federating Views

Another thing that looks like a table, from the particular angle of the federated engine, is a view. You can cheat the engine into linking to a view instead of a table, which opens a whole new world of opportunities.

For instance, you can mitigate the terrible performance of aggregating functions by using views. First, create a view that simply counts tkey records:

server1 (test) > CREATE VIEW tkey_count AS SELECT COUNT(*) AS how_many FROM tkey;
Query OK, 0 rows affected (0.00 sec)

Then, on the second server, federate that view as if it were a table:

create table tkey_count_fed 
(how_many int) 
ENGINE=FEDERATED 
CONNECTION='mysql://server2usr:datacharmer@127.0.0.1:5001/test/tkey_count';

Now try it. This time the count comes in no time at all.

server2 (test) > SELECT * FROM tkey_count_fed;
+----------+
| how_many |
+----------+
|   518584 |
+----------+
1 row in set (0.00 sec)
server2 (test) > INSERT INTO tkey_fed VALUES (NULL, 'this_was_not_there');
Query OK, 1 row affected (0.00 sec)

server2 (test) > SELECT * FROM tkey_count_fed;
+----------+
| how_many |
+----------+
|   518585 |
+----------+
1 row in set (0.00 sec)

Your satisfaction will be almost total when the general log shows that the request did not trigger any unpleasant side effects.

$ tail ~/server1/data/general.log
                 8 Query  SHOW TABLE STATUS LIKE 'tkey_count'
                 8 Query  SELECT `how_many` FROM `tkey_count`
060613 21:18:24  4 Query  INSERT INTO `tkey` (id, word) VALUES  (0, 'this_was_not_there')
060613 21:18:28  8 Query  SHOW TABLE STATUS LIKE 'tkey_count'
                 8 Query  SELECT `how_many` FROM `tkey_count`

Still, there is room for improvement. This solution does not allow a WHERE clause for the COUNT query. All worries should dissipate when you read the next section.

Executing Remote Commands With Federated Tables and VIEWS

It's time to open the Wizardry department and give the federated engine a workout.

You can execute predefined functions on a remote server by implementing a simple framework based on a federated table, a view, and a few support tables.

On server1, create:

On server2, create:

The full implementation of the framework requires some more components to allow different users to execute remote commands concurrently, and some additions to allow the execution of arbitrary commands, but I won't burden you now with such details. I plan to describe the full framework in a separate article.

Half Baked? A Glimpse At Future Developments

Finally, I should mention that there are plans for improving the federated engine in future releases of MySQL.

This knowledge comes from attending a presentation at the MySQL Users Conference 2006, where Patrick Galbraith explained that the plan is to implement a new servers table to hold the connection parameters for a remote server. Unfortunately, this implementation won't allow a name for the table being linked, which will have the same name as the local one. This fact will make the usage of federated tables much harder in some situations where you need to have, in the same database, a local table X and a link to table X on one or more remote servers. I appeal to the MySQL developers to implement this feature in the right way. For example, something along the lines of:

CREATE SERVER server_def (
host     : 'my.host.mydomain.org',
port     : 3306,
user     : 'uname',
password : 'mysecret',
database : 'my_db_name'
);

CREATE TABLE xyz ( ... )
ENGINE = FEDERATED CONNECT TO 'table_name' USING server_def;

Such an enhancement would yield the clear syntax combined with the freedom of defining a table name.

Summing Up

Federated tables are one great feature in MySQL. With the right amount of documentation and test units, it can really make a difference, turning MySQL into a distributed DBMS.

Thanks

Thanks to Roland Bouman and Beat Vontobel for reviewing this draft and providing useful advice. In particular, I owe Roland for an improvement on the fed_table_available function.

Thanks to Patrick Galbraith for answering some questions about the future development of this engine.

Giuseppe Maxia is a QA developer in MySQL community team. A system analyst with 20 years of IT experience, he has worked as a database consultant and designer for several years. He is a frequent speaker at open source events and he's the author of many articles. He lives in Sardinia (Italy).

Return to the Databases.

Are there any other pitfalls or feature requests? Let us know here.
You must be logged in to the O'Reilly Network to post a talkback.

Showing messages 1 through 2 of 2.

• Problem to create table as federated engine
  2009-02-25 09:26:47  valdezr [Reply | View]
  
  
  Although I specify the engine of type federated in my server2 sandbox, when I run the information.schema query, I see the tables were created like MyISAM instead of federated.
  Here is one of my create tables query:
  
  create table tnokey_fed (
  id int not null primary key,
  word varchar(30) not null
  )
  engine=federated
  connection='mysql://server2usr:msandbox@127.0.0.1:5001/test/tnokey';
  
  What could be wrong. I hope you can advice me.
  Thanks in advance.
  

• Framework for Executing Remote Commands
  2008-01-31 06:45:38  Schnaaf [Reply | View]
  
  
  Hi Giuseppe,
  
  Your Missing Manual article about MySQL Federated Tables is great. To me, although a very important part is missing. It's the part concerning executing remote commands using federated tables and views. In your article you write:
  
  "The full implementation of the framework requires some more components to allow different users to execute remote commands concurrently, and some additions to allow the execution of arbitrary commands, but I won't burden you now with such details. I plan to describe the full framework in a separate article."
  
  Has this article been written in the meantime? If not, could you be so kind to put me into the right direction concerning the missing parts for this sollution?