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 string (or the row in the
mysql.servers table) to connect to the
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
FEDERATED table does not support indexes
in the usual sense; because access to the table data is
handled remotely, it is actually the remote table that makes
use of indexes. This means that, for a query that cannot use
any indexes and so requires a full table scan, the server
fetches all rows from the remote table and filters them
locally. This occurs regardless of any
LIMIT used with
SELECT statement; these
clauses are applied locally to the returned rows.
Queries that fail to use indexes can thus cause poor performance and network overload. In addition, since returned rows must be stored in memory, such a query can also lead to the local server swapping, or even hanging.
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
FEDERATED table 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
TRUNCATE TABLE, and indexes. It
does not support
or any Data Definition Language statements that directly
affect the structure of the table, other than
DROP TABLE. The current
implementation does not use prepared statements.
... 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
Transactions are not supported.
Before MySQL 5.1.21, 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.1.21,
bulk-insert handling such that multiple rows are sent to the
remote table in a batch. This provides a performance
improvement and enables the remote table to perform
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.
When using a
CONNECTION string, you cannot
use an '@' character in the password. You can get round this
limitation by using the
SERVER statement to create a server connection.
DROP TABLE statement issued
FEDERATED table drops only the
local table, not the remote table.
FEDERATED tables do not work with the query
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.