MySQL Federated Tables: The Missing Manual

Listen Print Discuss

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

Federated Connections

Now it's time to examine the first reaction from server1 by perusing the general log:

$ tail ~/server1/data/general.log
...
060604 18:22:14  7 Connect  server2usr@localhost on test
                 7 Query    SELECT *  FROM `tkey` WHERE  1=0
                 7 Quit
                 8 Connect  server2usr@localhost on test
                 8 Query    SELECT *  FROM `tnokey` WHERE  1=0
                 8 Quit

Rule number 1 of the Federated Missing Manual: The receiving server does not know that it's being federated.

I will show you more evidence for this rule later, but for now it's enough to note that, when creating a table, the second server issues a query to the first server with a WHERE clause that will return an empty set. That's because it needs to be sure that the target table exists.

Notice also that the connection closes immediately. Now proceed with...

Rule number 2: The creation of a federated table is an inexpensive operation.

It's one query on the linking server, plus one quick query on the linked server. That's enough evidence to state that a federated table does not require lots of resources, at least during its creation.

Your first operation on server2 will just check that the table exists.

server2 (test) > show tables;
+----------------+
| Tables_in_test |
+----------------+
| tkey_fed       |
| tnokey_fed     |
+----------------+

This simple operation will be visible on the linked server.

$ tail ~/server1/data/general.log
...
060610 23:07:02  6 Connect  server2usr@localhost on test
                 7 Connect  server2usr@localhost on test

There are two connections, one for each federated table. Is it possible to confirm this by other means?

server1 (test) > show processlist;
+----+-------------+-----------------+------+---------+------+-------+------------------+
| Id | User        | Host            | db   | Command | Time | State | Info             |
+----+-------------+-----------------+------+---------+------+-------+------------------+
|  6 | server2usr  | localhost:46443 | test | Sleep   |  298 | NULL  | NULL             |
|  7 | server2usr  | localhost:46444 | test | Sleep   |  298 | NULL  | NULL             |
|  8 | datacharmer | localhost       | test | Query   |    0 | NULL  | show processlist |
+----+-------------+-----------------+------+---------+------+-------+------------------+

There are indeed two connections from server2usr, which is the user that appears in the federated table connection string. Now, go one step further by adding another federated table on server2.

server2 (test) > create table test1 like tkey_fed;
Query OK, 0 rows affected (0.00 sec)

server2 (test) > show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) NOT NULL auto_increment,
  `word` varchar(30) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `word` (`word`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 
    CONNECTION='mysql://server2usr:datacharmer@127.0.0.1:5001/test/tkey'
1 row in set (0.01 sec)

server2 (test) > show create table tkey_fed\G
*************************** 1. row ***************************
       Table: tkey_fed
Create Table: CREATE TABLE `tkey_fed` (
  `id` int(11) NOT NULL auto_increment,
  `word` varchar(30) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `word` (`word`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 
    CONNECTION='mysql://server2usr:datacharmer@127.0.0.1:5001/test/tkey'
1 row in set (0.00 sec)

Now there are three federated tables on server2: two pointing to tkey and one pointing to tnokey. The general log will show the usual inexpensive query, but the surprise comes when checking the process list again:

server1 (test) > show processlist;
+----+-------------+-----------------+------+---------+------+-------+------------------+
| Id | User        | Host            | db   | Command | Time | State | Info             |
+----+-------------+-----------------+------+---------+------+-------+------------------+
|  7 | server2usr  | localhost:46444 | test | Sleep   |  869 | NULL  | NULL             |
|  8 | datacharmer | localhost       | test | Query   |    0 | NULL  | show processlist |
| 10 | server2usr  | localhost:56715 | test | Sleep   |  272 | NULL  | NULL             |
| 11 | server2usr  | localhost:60575 | test | Sleep   |  265 | NULL  | NULL             |
+----+-------------+-----------------+------+---------+------+-------+------------------+

There are three connections, for just two tables that are federated by another server, which leads to...

Rule number 3: Each federated table on a remote server will consume one connection, even if there are several remote tables pointing to the same local table.

While the creation is inexpensive, the actual usage starts to look a bit more onerous. If you plan to federate many tables from several servers, prepare to increase your server's connections.

Developer's angle: There will be some improvements. MySQL is already planning to reduce the number of open connections.

Speaking of connections, here's a good chance to try yet another experiment. What happens to server2's tables if you stop server1?

$ ~/server1/stop.sh

server2 (test) > SELECT table_name, engine 
    -> FROM information_schema.tables 
    -> WHERE table_schema=schema();
ERROR 1430 (HY000): There was a problem processing the query on the 
    foreign data source. Data source error: 
    %The foreign data source you are trying to reference does not exist. 
    Data source error:  : 2003 : Can't connect to MySQL server on '127.0.0.1' (111)

server2 (test) > SELECT count(*) 
    -> FROM information_schema.tables;
ERROR 1430 (HY000): There was a problem processing the query on the 
    foreign data source. Data source error: 
    %The foreign data source you are trying to reference does not exist. 
    Data source error:  : 2003 : Can't connect to MySQL server on '127.0.0.1' (111)

What happened? These queries don't use the federated tables (at least, that's what you might have thought while asking this simple question about table metadata). However, something is complaining about not being able to connect.

Restart server1 and repeat the operation.

$ ~/server1/start.sh

server2 (test) > SELECT table_name, engine 
    -> FROM information_schema.tables 
    -> WHERE table_schema=schema();
+------------+-----------+
| table_name | engine    |
+------------+-----------+
| test1      | FEDERATED |
| tkey_fed   | FEDERATED |
| tnokey_fed | FEDERATED |
+------------+-----------+

The general log reveals that there was a request sent to the linked server. Actually, there is one request for each table:

$ tail ~/server1/data/general.log
Time            Id Command  Argument
060610 23:43:33  1 Connect   server2usr@localhost on test
                 1 Query     SET NAMES latin1
                 1 Query     SHOW TABLE STATUS LIKE 'tkey'
                 2 Connect   server2usr@localhost on test
                 2 Query     SET NAMES latin1
                 2 Query     SHOW TABLE STATUS LIKE 'tkey'
                 3 Connect   server2usr@localhost on test
                 3 Query     SET NAMES latin1
                 3 Query     SHOW TABLE STATUS LIKE 'tnokey'

Rule number 4: Each INFORMATION_SCHEMA request related to a federated table involves a connection.

This rule looks like a bug, and perhaps it is. In the meantime, keep it in mind. If you are working with federated tables, be careful with your INFORMATION_SCHEMA requests.

Developer's angle: Unfortunately, it's a feature. It's the only way to get information about the table. For now, there's no way to avoid this problem.

Checking Whether A Federated Table Is Connected

Because federated tables strictly depend on active connections, it's a good idea to check whether the data is reachable before using a table. Here's a function that does just that, by attempting to query the information schema about a federated table and immediately checking whether it caught SQLSTATE 'HY000'.

CREATE FUNCTION fed_table_available( 
    p_table_schema varchar(50), 
    p_table_name varchar(50) )
RETURNS BOOLEAN
BEGIN
    DECLARE connection_dead BOOLEAN DEFAULT FALSE;
    DECLARE how_many int;
    DECLARE curx CURSOR FOR
        SELECT COUNT(*)
        FROM information_schema.TABLES
        WHERE
            TABLE_SCHEMA = p_table_schema
            AND TABLE_NAME = p_table_name;
    DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000'
        SET connection_dead = TRUE;
    OPEN curx;
    IF connection_dead THEN
        RETURN FALSE;
    ELSE
        FETCH curx into how_many;
        CLOSE curx;
        RETURN (how_many > 0);
    END IF;
END

Using this method, your application can exit gracefully if a federated table is not available.

$ ~/server1/stop.sh

server2 (test) > select fed_table_available(schema(), 'tkey_fed');
+--------------------------------------------+
| fed_table_available( schema(), 'tkey_fed') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.05 sec)

$ ~/server1/start.sh

server2 (test) > select fed_table_available(schema(), 'tkey_fed');
+--------------------------------------------+
| fed_table_available( schema(), 'tkey_fed') |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.05 sec)

Pages: 1, 2, 3, 4, 5

Federated Connections

Checking Whether A Federated Table Is Connected

Tagged Articles

mysql

cluster

reference

federated

documentation

Sponsored Resources

Related to this Article