MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
You should be aware of the following points when using the
FEDERATED
storage engine:
FEDERATED
tables may be replicated to other
replicas, but you must ensure that the replica 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
remote server.
The following items indicate features that the
FEDERATED
storage engine does and does not
support:
The remote server must be a MySQL server.
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.
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
WHERE
or LIMIT
used with
this 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 fails if the table uses an
index prefix on any VARCHAR
,
TEXT
or
BLOB
columns. The following
definition using 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 fails:
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.
Internally, the implementation uses
SELECT
,
INSERT
,
UPDATE
, and
DELETE
, but not
HANDLER
.
The FEDERATED
storage engine supports
SELECT
,
INSERT
,
UPDATE
,
DELETE
,
TRUNCATE TABLE
, and indexes. It
does not support ALTER TABLE
,
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.
FEDERATED
accepts
INSERT
... ON DUPLICATE KEY UPDATE
statements, but if a
duplicate-key violation occurs, the statement fails with an
error.
Transactions are not supported.
FEDERATED
performs bulk-insert handling
such that multiple rows are sent to the remote table in a
batch, which improves performance. 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
INSERT
... 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 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 drops only the
local table, not the remote table.
FEDERATED
tables do not work with the query
cache.
User-defined partitioning is not supported for
FEDERATED
tables.