Additional languages
« 14.9.2.2 Creating a FEDERATED Table Using CREATE SERVER
14.9.4 FEDERATED Storage Engine Resources »
Section Navigation      [Toggle]

14.9.3. FEDERATED Storage Engine Notes and Tips

You should be aware of the following points when using the FEDERATED storage engine:

  • FEDERATED tables may be replicated to other slaves, but you must ensure that the slave servers are able to use the user/password combination that is defined in the CONNECTION (or the row in the mysql.servers table) to connect to the remote server.

The following items indicate features that the FEDERATED storage engine does and does not support:

  • In the first version, the remote server must be a MySQL server. Support by FEDERATED for other database engines may be added in the future.

  • The remote table that a FEDERATED table points to must exist before you try to access the table through the FEDERATED table.

  • It is possible for one FEDERATED table to point to another, but you must be careful not to create a loop.

  • Performance on a FEDERATED table when performing bulk inserts (for example, on a INSERT INTO ... SELECT ... statement) is slower than with other table types because each selected row is treated as an individual INSERT statement on the federated table.

  • Transactions are supported, but distributed transactions (XA) are not currently supported.

  • There is no way for the FEDERATED engine to know if the remote table has changed. The reason for this is that this table must work like a data file that would never be written to by anything other than the database. The integrity of the data in the local table could be breached if there was any change to the remote database.

  • The FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE, and indexes. It does not support ALTER TABLE, or any Data Definition Language statements other than DROP TABLE. The current implementation does not use prepared statements.

  • When using a CONNECTION string, you cannot use an '@' character in the password. You can get round this limitation by using the CREATE SERVER statement to create a server connection.

  • The INSERT_ID and TIMESTAMP options are not propagated to the data provider.

  • Any DROP TABLE statement issued against a FEDERATED table will only drop the local table, not the remote table.

  • The implementation uses SELECT, INSERT, UPDATE, and DELETE, but not HANDLER.

  • FEDERATED tables do not work with the query cache.

  • User-defined partitioning is not supported for FEDERATED tables. Beginning with MySQL 5.1.15, it is no longer possible to create such tables at all.

Some of these limitations may be lifted in future versions of the FEDERATED handler.