Oracle 5.0, Справочное руководство

Страница: 1335 / 3232

Limitations of the
FEDERATED
Storage Engine
1315
(Before MySQL 5.0.13, use
COMMENT
rather than
CONNECTION
.)
The basic structure of this table should match 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.
Note
You can improve the performance of a
FEDERATED
table by adding indexes to
the table on the host. The optimization will occur because the query sent to the
remote server will include the contents of the
WHERE
clause and will be sent to
the remote server and subsequently executed locally. This reduces the network
traffic that would otherwise request the entire table from the server for local
processing.
The
FEDERATED
engine creates only the
test_table.frm
file in the
federated
database.
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 format of the connection string in the
CONNECTION
option is as follows:
scheme :// user_name [: password ]@ host_name [: port_num ]/ db_name / tbl_name
Only
mysql
is supported as the
scheme
value at this point; the password and port number are
optional.
Sample 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 nonoptimal 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
INFORMATION_SCHEMA
database.
14.7.3. Limitations of the
FEDERATED
Storage Engine
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.
• There is no support for transactions.
• 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 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
VARCHAR
,
TEXT
or
BLOB
columns will fail. The following definition in
MyISAM
is valid: