89334

Federated Storage Engine Notes and Tips

You should be aware of the foliowing 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 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 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 sińce the index definition from an equivalent MylSAM or other table may not be

supported. For example, creating a FEDERATED table with an index prefix on

VARCHAR, TEXT or

BLOB columns will fail. The following definition in MylSAM is valid:

CREATE TABLE 'Tl' ('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 'Tl'('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.