Source code editor What Is Ajax
FEDERATED storage engine is available beginning with MySQL 5.0.3. It is a storage engine that accesses data in tables of remote databases rather than in local tables.
FEDERATED storage engine is included in MySQL binary distributions. To enable this storage engine if you build MySQL from source, invoke configure with the
To examine the source for the
FEDERATED engine, look in the
sql directory of a source distribution for MySQL 5.0.3 or newer.
A forum dedicated to the
FEDERATED storage engine is available at http://forums.mysql.com/list.php?105.
When you create a
FEDERATED table, the server creates a table format file in the database directory. The file begins with the table name and has an
.frm extension. No other files are created, because the actual data is in a remote table. This differs from the way that storage engines for local tables work.
For local database tables, data files are local. For example, if you create a
MyISAM table named
MyISAM handler creates a data file named
users.MYD. A handler for local tables reads, inserts, deletes, and updates data in local data files, and rows are stored in a format particular to the handler. To read rows, the handler must parse data into columns. To write rows, column values must be converted to the row format used by the handler and written to the local data file.
With the MySQL
FEDERATED storage engine, there are no local data files for a table (for example, there is no
.MYD file). Instead, a remote database stores the data that normally would be in the table. The local server connects to a remote server, and uses the MySQL client API to read, delete, update, and insert data in the remote table. Data retrieval is initiated via a
SELECT * FROM SQL statement. To read the result, rows are fetched one at a time by using the
mysql_fetch_row() C API function, and then converting the columns in the
SELECT result set to the format that the
FEDERATED handler expects.
The flow of information is as follows:
SQL calls issued locally
MySQL handler API (data in handler format)
MySQL client API (data converted to SQL calls)
Remote database -> MySQL client API
Convert result sets (if any) to handler format
Handler API -> Result rows or rows-affected count to local
The procedure for using
FEDERATED tables is very simple. Normally, you have two servers running, either both on the same host or on different hosts. (It is possible for a
FEDERATED table to use another table that is managed by the same server, although there is little point in doing so.)
First, you must have a table on the remote server that you want to access by using a
FEDERATED table. Suppose that the remote table is in the
federated database and is defined like this:
CREATE TABLE test_table ( id INT(20) NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL DEFAULT '', other INT(20) NOT NULL DEFAULT '0', PRIMARY KEY (id), INDEX name (name), INDEX other_key (other) ) ENGINE=MyISAM DEFAULT charset=utf-8;
The example uses a
MyISAM table, but the table could use any storage engine.
Next, create a
FEDERATED table on the local server for accessing the remote table:
CREATE TABLE federated_table ( id INT(20) NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL DEFAULT '', other INT(20) NOT NULL DEFAULT '0', PRIMARY KEY (id), INDEX name (name), INDEX other_key (other) ) ENGINE=FEDERATED DEFAULT charset=utf-8 CONNECTION='mysql://root@remote_host:9306/federated/test_table';
(Before MySQL 5.0.13, use
COMMENT rather than
The structure of this table must be exactly the same as that of the remote table, except that the
ENGINE table option should be
FEDERATED and the
CONNECTION table option is a connection string that indicates to the
FEDERATED engine how to connect to the remote server.
FEDERATED engine creates only the
test_table.frm file in the
The remote host information indicates the remote server to which your local server connects, and the database and table information indicates which remote table to use as the data source. In this example, the remote server is indicated to be running as
remote_host on port 9306, so there must be a MySQL server running on the remote host and listening to port 9306.
The general form of the connection string in the
CONNECTION option is as follows:
mysql is supported as the
scheme value at this point; the password and port number are optional.
Here are some example connection strings:
CONNECTION='mysql://username:password@hostname:port/database/tablename' CONNECTION='mysql://username@hostname/database/tablename' CONNECTION='mysql://username:password@hostname/database/tablename'
The use of
CONNECTION for specifying the connection string is non-optimal and is likely to change in future. Keep this in mind for applications that use
FEDERATED tables. Such applications are likely to need modification if the format for specifying connection information changes.
Because any password given in the connection string is stored as plain text, it can be seen by any user who can use
SHOW CREATE TABLE or
SHOW TABLE STATUS for the
FEDERATED table, or query the
TABLES table in the
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
It is possible for one
FEDERATED table to point to another, but you must be careful not to create a loop.
There is no support for transactions.
FEDERATED table does not support indexes per-se, since the 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
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.
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.
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.
FEDERATED storage engine supports
DELETE, 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.
DROP TABLE statement issued against a
FEDERATED table will only drop the local table, not the remote table.
The implementation uses
DELETE, but not
FEDERATED tables do not work with the query cache.
Some of these limitations may be lifted in future versions of the
Source code editor What Is Ajax