<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="blackhole-storage-engine">
      </a>
      18.6 The BLACKHOLE Storage Engine
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045147569184">
  </a>
  <a class="indexterm" name="idm46045147568096">
  </a>
  <p>
   The
   <code class="literal">
    BLACKHOLE
   </code>
   storage engine acts as a
   <span class="quote">
    “
    <span class="quote">
     black hole
    </span>
    ”
   </span>
   that accepts data but throws it away and
    does not store it. Retrievals always return an empty result:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93063082"><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 prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test<span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">,</span> c <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> BLACKHOLE<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'record one'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'record two'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 2 rows affected (0.00 sec)</span>
<span class="token output">Records: 2  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test<span class="token punctuation">;</span>
<span class="token output">Empty set (0.00 sec)</span></code></pre>
  </div>
  <p>
   To enable the
   <code class="literal">
    BLACKHOLE
   </code>
   storage engine if you
    build MySQL from source, invoke
   <span class="command">
    <strong>
     CMake
    </strong>
   </span>
   with the
   <a class="link" href="source-configuration-options.html#option_cmake_storage_engine_options" title="Storage Engine Options">
    <code class="option">
     -DWITH_BLACKHOLE_STORAGE_ENGINE
    </code>
   </a>
   option.
  </p>
  <p>
   To examine the source for the
   <code class="literal">
    BLACKHOLE
   </code>
   engine,
    look in the
   <code class="filename">
    sql
   </code>
   directory of a MySQL source
    distribution.
  </p>
  <p>
   When you create a
   <code class="literal">
    BLACKHOLE
   </code>
   table, the server
    creates the table definition in the global data dictionary. There
    are no files associated with the table.
  </p>
  <p>
   The
   <code class="literal">
    BLACKHOLE
   </code>
   storage engine supports all kinds
    of indexes. That is, you can include index declarations in the table
    definition.
  </p>
  <p>
   The maximum key length is 3072 bytes.
  </p>
  <p>
   The
   <code class="literal">
    BLACKHOLE
   </code>
   storage engine does not support
    partitioning.
  </p>
  <p>
   You can check whether the
   <code class="literal">
    BLACKHOLE
   </code>
   storage
    engine is available with the
   <a class="link" href="show-engines.html" title="15.7.7.17 SHOW ENGINES Statement">
    <code class="literal">
     SHOW
    ENGINES
    </code>
   </a>
   statement.
  </p>
  <p>
   Inserts into a
   <code class="literal">
    BLACKHOLE
   </code>
   table do not store any
    data, but if statement based binary logging is enabled, the SQL
    statements are logged and replicated to replica servers. This can be
    useful as a repeater or filter mechanism.
  </p>
  <p>
   Suppose that your application requires replica-side filtering rules,
    but transferring all binary log data to the replica first results in
    too much traffic. In such a case, it is possible to set up on the
    replication source server a
   <span class="quote">
    “
    <span class="quote">
     dummy
    </span>
    ”
   </span>
   replica process
    whose default storage engine is
   <code class="literal">
    BLACKHOLE
   </code>
   ,
    depicted as follows:
  </p>
  <div class="figure">
   <a name="idm46045147546960">
   </a>
   <p class="title">
    <b>
     Figure 18.1 Replication using BLACKHOLE for Filtering
    </b>
   </p>
   <div class="figure-contents">
    <div class="mediaobject">
     <img alt="The replication source server uses a source mysqld process and a dummy mysqld process. On the replica, the mysqld process replicates from the dummy mysqld process." src="images/blackhole-1.png" style="width: 100%; max-width: 575px;"/>
    </div>
   </div>
  </div>
  <br class="figure-break"/>
  <p>
   The source writes to its binary log. The
   <span class="quote">
    “
    <span class="quote">
     dummy
    </span>
    ”
   </span>
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   process acts as a replica, applying the
    desired combination of
   <code class="literal">
    replicate-do-*
   </code>
   and
   <code class="literal">
    replicate-ignore-*
   </code>
   rules, and writes a new,
    filtered binary log of its own. (See
   <a class="xref" href="replication-options.html" title="19.1.6 Replication and Binary Logging Options and Variables">
    Section 19.1.6, “Replication and Binary Logging Options and Variables”
   </a>
   .) This filtered log is
    provided to the replica.
  </p>
  <p>
   The dummy process does not actually store any data, so there is
    little processing overhead incurred by running the additional
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   process on the replication source server.
    This type of setup can be repeated with additional replicas.
  </p>
  <p>
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   triggers for
   <code class="literal">
    BLACKHOLE
   </code>
   tables work as expected. However,
    because the
   <code class="literal">
    BLACKHOLE
   </code>
   table does not actually
    store any data,
   <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>
   triggers are not activated:
    The
   <code class="literal">
    FOR EACH ROW
   </code>
   clause in the trigger definition
    does not apply because there are no rows.
  </p>
  <p>
   Other possible uses for the
   <code class="literal">
    BLACKHOLE
   </code>
   storage
    engine include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Verification of dump file syntax.
     </p>
    </li>
    <li class="listitem">
     <p>
      Measurement of the overhead from binary logging, by comparing
        performance using
      <code class="literal">
       BLACKHOLE
      </code>
      with and without
        binary logging enabled.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       BLACKHOLE
      </code>
      is essentially a
      <span class="quote">
       “
       <span class="quote">
        no-op
       </span>
       ”
      </span>
      storage engine, so it could be used for
        finding performance bottlenecks not related to the storage
        engine itself.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <code class="literal">
    BLACKHOLE
   </code>
   engine is transaction-aware, in the
    sense that committed transactions are written to the binary log and
    rolled-back transactions are not.
  </p>
  <p>
   <span class="bold">
    <strong>
     Blackhole Engine and Auto Increment
    Columns
    </strong>
   </span>
  </p>
  <p>
   The
   <code class="literal">
    BLACKHOLE
   </code>
   engine is a no-op engine. Any
    operations performed on a table using
   <code class="literal">
    BLACKHOLE
   </code>
   have no effect. This should be borne in mind when considering the
    behavior of primary key columns that auto increment. The engine does
    not automatically increment field values, and does not retain auto
    increment field state. This has important implications in
    replication.
  </p>
  <p>
   Consider the following replication scenario where all three of the
    following conditions apply:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      On a source server there is a blackhole table with an auto
        increment field that is a primary key.
     </p>
    </li>
    <li class="listitem">
     <p>
      On a replica the same table exists but using the MyISAM engine.
     </p>
    </li>
    <li class="listitem">
     <p>
      Inserts are performed into the source's table without explicitly
        setting the auto increment value in the
      <code class="literal">
       INSERT
      </code>
      statement itself or through using a
      <code class="literal">
       SET INSERT_ID
      </code>
      statement.
     </p>
    </li>
   </ol>
  </div>
  <p>
   In this scenario replication fails with a duplicate entry error on
    the primary key column.
  </p>
  <p>
   In statement based replication, the value of
   <code class="literal">
    INSERT_ID
   </code>
   in the context event is always the
    same. Replication therefore fails due to trying insert a row with a
    duplicate value for a primary key column.
  </p>
  <p>
   In row based replication, the value that the engine returns for the
    row always be the same for each insert. This results in the replica
    attempting to replay two insert log entries using the same value for
    the primary key column, and so replication fails.
  </p>
  <p>
   <span class="bold">
    <strong>
     Column Filtering
    </strong>
   </span>
  </p>
  <p>
   When using row-based replication,
    (
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
    <code class="literal">
     binlog_format=ROW
    </code>
   </a>
   ), a replica
    where the last columns are missing from a table is supported, as
    described in the section
   <a class="xref" href="replication-features-differing-tables.html" title="19.5.1.9 Replication with Differing Table Definitions on Source and Replica">
    Section 19.5.1.9, “Replication with Differing Table Definitions on Source and Replica”
   </a>
   .
  </p>
  <p>
   This filtering works on the replica side, that is, the columns are
    copied to the replica before they are filtered out. There are at
    least two cases where it is not desirable to copy the columns to the
    replica:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      If the data is confidential, so the replica server should not
        have access to it.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the source has many replicas, filtering before sending to the
        replicas may reduce network traffic.
     </p>
    </li>
   </ol>
  </div>
  <p>
   Source column filtering can be achieved using the
   <code class="literal">
    BLACKHOLE
   </code>
   engine. This is carried out in a way
    similar to how source table filtering is achieved - by using the
   <code class="literal">
    BLACKHOLE
   </code>
   engine and the
   <a class="link" href="replication-options-replica.html#option_mysqld_replicate-do-table">
    <code class="option">
     --replicate-do-table
    </code>
   </a>
   or
   <a class="link" href="replication-options-replica.html#option_mysqld_replicate-ignore-table">
    <code class="option">
     --replicate-ignore-table
    </code>
   </a>
   option.
  </p>
  <p>
   The setup for the source is:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28874521"><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> t1 <span class="token punctuation">(</span>public_col_1<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span> public_col_N<span class="token punctuation">,</span>
                 secret_col_1<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span> secret_col_M<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 setup for the trusted replica is:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa8778354"><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> t1 <span class="token punctuation">(</span>public_col_1<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span> public_col_N<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>BLACKHOLE<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The setup for the untrusted replica is:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa90139625"><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> t1 <span class="token punctuation">(</span>public_col_1<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span> public_col_N<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>
 </div>
 <br/>
</div>
