<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="federated-usagenotes">
      </a>
      18.8.3 FEDERATED Storage Engine Notes and Tips
     </h3>
    </div>
   </div>
  </div>
  <p>
   You should be aware of the following points when using the
   <code class="literal">
    FEDERATED
   </code>
   storage engine:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       FEDERATED
      </code>
      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
      <code class="literal">
       CONNECTION
      </code>
      string (or the row in the
      <code class="literal">
       mysql.servers
      </code>
      table) to connect to the
          remote server.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following items indicate features that the
   <code class="literal">
    FEDERATED
   </code>
   storage engine does and does not
      support:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The remote server must be a MySQL server.
     </p>
    </li>
    <li class="listitem">
     <p>
      The remote table that a
      <code class="literal">
       FEDERATED
      </code>
      table
          points to
      <span class="emphasis">
       <em>
        must
       </em>
      </span>
      exist before you try to
          access the table through the
      <code class="literal">
       FEDERATED
      </code>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      It is possible for one
      <code class="literal">
       FEDERATED
      </code>
      table to
          point to another, but you must be careful not to create a
          loop.
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <code class="literal">
       FEDERATED
      </code>
      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
      <code class="literal">
       WHERE
      </code>
      or
      <code class="literal">
       LIMIT
      </code>
      used with
          this
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement; these
          clauses are applied locally to the returned rows.
     </p>
     <p>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      Care should be taken when creating a
      <code class="literal">
       FEDERATED
      </code>
      table since the index definition
          from an equivalent
      <code class="literal">
       MyISAM
      </code>
      or other table
          may not be supported. For example, creating a
      <code class="literal">
       FEDERATED
      </code>
      table fails if the table uses an
          index prefix on any
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      ,
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      or
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      columns. The following
          definition using
      <code class="literal">
       MyISAM
      </code>
      is valid:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36568226"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>T1<span class="token punctuation">`</span><span class="token punctuation">(</span>
    <span class="token punctuation">`</span>A<span class="token punctuation">`</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">UNIQUE</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span><span class="token punctuation">`</span>A<span class="token punctuation">`</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>MYISAM<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The key prefix in this example is incompatible with the
      <code class="literal">
       FEDERATED
      </code>
      engine, and the equivalent
          statement fails:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67519477"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>T1<span class="token punctuation">`</span><span class="token punctuation">(</span>
    <span class="token punctuation">`</span>A<span class="token punctuation">`</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">UNIQUE</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span><span class="token punctuation">`</span>A<span class="token punctuation">`</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>FEDERATED
  <span class="token keyword">CONNECTION</span><span class="token operator">=</span><span class="token string">'MYSQL://127.0.0.1:3306/TEST/T1'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      Internally, the implementation uses
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      ,
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      ,
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      , and
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      , but not
      <a class="link" href="handler.html" title="15.2.5 HANDLER Statement">
       <code class="literal">
        HANDLER
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       FEDERATED
      </code>
      storage engine supports
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      ,
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      ,
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      ,
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      ,
      <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
       <code class="literal">
        TRUNCATE TABLE
       </code>
      </a>
      , and indexes. It
          does not support
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      ,
          or any Data Definition Language statements that directly
          affect the structure of the table, other than
      <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
       <code class="literal">
        DROP TABLE
       </code>
      </a>
      . The current
          implementation does not use prepared statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FEDERATED
      </code>
      accepts
      <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
       <code class="literal">
        INSERT
          ... ON DUPLICATE KEY UPDATE
       </code>
      </a>
      statements, but if a
          duplicate-key violation occurs, the statement fails with an
          error.
     </p>
    </li>
    <li class="listitem">
     <p>
      Transactions are not supported.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FEDERATED
      </code>
      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:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         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.
        </p>
       </li>
       <li class="listitem">
        <p>
         Bulk-insert handling does not occur for
         <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
          <code class="literal">
           INSERT
              ... ON DUPLICATE KEY UPDATE
          </code>
         </a>
         .
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      There is no way for the
      <code class="literal">
       FEDERATED
      </code>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      When using a
      <code class="literal">
       CONNECTION
      </code>
      string, you cannot
          use an '@' character in the password. You can get round this
          limitation by using the
      <a class="link" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
       <code class="literal">
        CREATE
          SERVER
       </code>
      </a>
      statement to create a server connection.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="server-system-variables.html#sysvar_insert_id">
       <code class="literal">
        insert_id
       </code>
      </a>
      and
      <a class="link" href="server-system-variables.html#sysvar_timestamp">
       <code class="literal">
        timestamp
       </code>
      </a>
      options are not
          propagated to the data provider.
     </p>
    </li>
    <li class="listitem">
     <p>
      Any
      <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
       <code class="literal">
        DROP TABLE
       </code>
      </a>
      statement issued
          against a
      <code class="literal">
       FEDERATED
      </code>
      table drops only the
          local table, not the remote table.
     </p>
    </li>
    <li class="listitem">
     <p>
      User-defined partitioning is not supported for
      <code class="literal">
       FEDERATED
      </code>
      tables.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
