The following items indicate features that the
FEDERATED storage engine does and does not
The remote server must be a MySQL server.
The remote table that a
points to must exist before you try to
access the table through the
It is possible for one
FEDERATED table to
point to another, but you must be careful not to create a
There is no support for transactions.
FEDERATED table does not support indexes
per se. Because access to the table is handled remotely, it is
the remote table that supports the indexes. Care should be
taken when creating a
FEDERATED table since
the index definition from an equivalent
MyISAM or other table may not be supported.
For example, creating a
with an index prefix on
BLOB columns will fail. The
following definition in
MyISAM is valid:
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=MYISAM;
The key prefix in this example is incompatible with the
FEDERATED engine, and the equivalent
statement will fail:
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=FEDERATED CONNECTION='MYSQL://127.0.0.1:3306/TEST/T1';
If possible, you should try to separate the column and index definition when creating tables on both the remote server and the local server to avoid these index issues.
FEDERATED storage engine supports
DELETE, and indexes. It does
ALTER TABLE, or any
Data Definition Language statements that directly affect the
structure of the table, other than
TABLE. The current implementation does not use
... ON DUPLICATE KEY UPDATE statements, but if a
duplicate-key violation occurs, the statement fails with an
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
INSERT statement on
the federated table.
Before MySQL 5.0.46, for a multiple-row insert into a
FEDERATED table that refers to a remote
transactional table, if the insert failed for a row due to
constraint failure, the remote table would contain a partial
commit (the rows preceding the failed one) instead of rolling
back the statement completely. This occurred because the rows
were treated as individual inserts.
As of MySQL 5.0.46,
bulk-insert handling such that multiple rows are sent to the
remote table in a batch. This provides a performance
improvement. Also, if the remote table is transactional, it
enables the remote storage engine to perform statement
rollback properly should an error occur. This capability has
the following limitations:
The size of the insert cannot exceed the maximum packet size between servers. If the insert exceeds this size, it is broken into multiple packets and the rollback problem can occur.
Bulk-insert handling does not occur for
... ON DUPLICATE KEY UPDATE.
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 system. The
integrity of the data in the local table could be breached if
there was any change to the remote database.
DROP TABLE statement issued
FEDERATED table drops only the
local table, not the remote table.
FEDERATED tables do not work with the query