<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-compression-syntax-warnings">
      </a>
      17.9.1.7 SQL Compression Syntax Warnings and Errors
     </h4>
    </div>
   </div>
  </div>
  <p>
   This section describes syntax warnings and errors that you may
        encounter when using the table compression feature with
   <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
    file-per-table
   </a>
   tablespaces and
   <a class="link" href="glossary.html#glos_general_tablespace" title="general tablespace">
    general
        tablespaces
   </a>
   .
  </p>
  <h5>
   <a name="idm46045161324368">
   </a>
   SQL Compression Syntax Warnings and Errors for File-Per-Table
Tablespaces
  </h5>
  <p>
   When
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   is
        enabled (the default), specifying
   <code class="literal">
    ROW_FORMAT=COMPRESSED
   </code>
   or
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   in
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
        TABLE
    </code>
   </a>
   or
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statements produces the following error if
   <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
    <code class="literal">
     innodb_file_per_table
    </code>
   </a>
   is
        disabled.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa55171768"><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-terminal"><span class="token output">ERROR 1031 (HY000)<span class="token punctuation">:</span> Table storage engine for 't1' doesn't have this option</span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The table is not created if the current configuration does not
          permit using compressed tables.
   </p>
  </div>
  <p>
   When
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   is
        disabled, specifying
   <code class="literal">
    ROW_FORMAT=COMPRESSED
   </code>
   or
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   in
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
        TABLE
    </code>
   </a>
   or
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statements produces the following warnings if
   <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
    <code class="literal">
     innodb_file_per_table
    </code>
   </a>
   is
        disabled.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa83783905"><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">SHOW</span> <span class="token keyword">WARNINGS</span><span class="token punctuation">;</span>
<span class="token output"><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Level   <span class="token punctuation">|</span> Code <span class="token punctuation">|</span> Message                                                       <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Warning <span class="token punctuation">|</span> 1478 <span class="token punctuation">|</span> InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Warning <span class="token punctuation">|</span> 1478 <span class="token punctuation">|</span> InnoDB: ignoring KEY_BLOCK_SIZE=4.                            <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Warning <span class="token punctuation">|</span> 1478 <span class="token punctuation">|</span> InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Warning <span class="token punctuation">|</span> 1478 <span class="token punctuation">|</span> InnoDB: assuming ROW_FORMAT=DYNAMIC.                          <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    These messages are only warnings, not errors, and the table is
          created without compression, as if the options were not
          specified.
   </p>
  </div>
  <p>
   The
   <span class="quote">
    “
    <span class="quote">
     non-strict
    </span>
    ”
   </span>
   behavior lets you import a
   <code class="literal">
    mysqldump
   </code>
   file into a database that does not
        support compressed tables, even if the source database contained
        compressed tables. In that case, MySQL creates the table in
   <code class="literal">
    ROW_FORMAT=DYNAMIC
   </code>
   instead of preventing the
        operation.
  </p>
  <p>
   To import the dump file into a new database, and have the tables
        re-created as they exist in the original database, ensure the
        server has the proper setting for the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
    <code class="literal">
     innodb_file_per_table
    </code>
   </a>
   configuration parameter.
  </p>
  <p>
   The attribute
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   is permitted
        only when
   <code class="literal">
    ROW_FORMAT
   </code>
   is specified as
   <code class="literal">
    COMPRESSED
   </code>
   or is omitted. Specifying a
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   with any other
   <code class="literal">
    ROW_FORMAT
   </code>
   generates a warning that you can
        view with
   <code class="literal">
    SHOW WARNINGS
   </code>
   . However, the table
        is non-compressed; the specified
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   is ignored).
  </p>
  <div class="informaltable">
   <table summary="Warning level, error code, and message text for messages that could be generated when using conflicting clauses for InnoDB table compression.">
    <colgroup>
     <col style="width: 20%"/>
     <col style="width: 20%"/>
     <col style="width: 60%"/>
    </colgroup>
    <thead>
     <tr>
      <th scope="col">
       Level
      </th>
      <th scope="col">
       Code
      </th>
      <th scope="col">
       Message
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <th scope="row">
       Warning
      </th>
      <td>
       1478
      </td>
      <td>
       <code class="literal">
        InnoDB: ignoring KEY_BLOCK_SIZE=
        <em class="replaceable">
         <code>
          n
         </code>
        </em>
        unless ROW_FORMAT=COMPRESSED.
       </code>
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   If you are running with
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   enabled, the
        combination of a
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   with any
   <code class="literal">
    ROW_FORMAT
   </code>
   other than
   <code class="literal">
    COMPRESSED
   </code>
   generates an error, not a warning,
        and the table is not created.
  </p>
  <p>
   <a class="xref" href="innodb-compression-syntax-warnings.html#innodb-compression-create-and-alter-options-table" title="Table 17.11 ROW_FORMAT and KEY_BLOCK_SIZE Options">
    Table 17.11, “ROW_FORMAT and KEY_BLOCK_SIZE Options”
   </a>
   provides an overview the
   <code class="literal">
    ROW_FORMAT
   </code>
   and
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   options that are used with
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   or
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   .
  </p>
  <div class="table">
   <a name="innodb-compression-create-and-alter-options-table">
   </a>
   <p class="title">
    <b>
     Table 17.11 ROW_FORMAT and KEY_BLOCK_SIZE Options
    </b>
   </p>
   <div class="table-contents">
    <table summary="ROW_FORMAT and KEY_BLOCK_SIZE option usage notes and descriptions.">
     <colgroup>
      <col style="width: 20%"/>
      <col style="width: 40%"/>
      <col style="width: 40%"/>
     </colgroup>
     <thead>
      <tr>
       <th scope="col">
        Option
       </th>
       <th scope="col">
        Usage Notes
       </th>
       <th scope="col">
        Description
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <th scope="row">
        <code class="literal">
         ROW_FORMAT=​REDUNDANT
        </code>
       </th>
       <td>
        Storage format used prior to MySQL 5.0.3
       </td>
       <td>
        Less efficient than
        <code class="literal">
         ROW_FORMAT=COMPACT
        </code>
        ; for backward
              compatibility
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         ROW_FORMAT=​COMPACT
        </code>
       </th>
       <td>
        Default storage format since MySQL 5.0.3
       </td>
       <td>
        Stores a prefix of 768 bytes of long column values in the clustered
              index page, with the remaining bytes stored in an overflow
              page
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         ROW_FORMAT=​DYNAMIC
        </code>
       </th>
       <td>
       </td>
       <td>
        Store values within the clustered index page if they fit; if not, stores
              only a 20-byte pointer to an overflow page (no prefix)
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         ROW_FORMAT=​COMPRESSED
        </code>
       </th>
       <td>
       </td>
       <td>
        Compresses the table and indexes using zlib
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         KEY_BLOCK_​SIZE=
         <em class="replaceable">
          <code>
           n
          </code>
         </em>
        </code>
       </th>
       <td>
       </td>
       <td>
        Specifies compressed page size of 1, 2, 4, 8 or 16 kilobytes; implies
        <code class="literal">
         ROW_FORMAT=COMPRESSED
        </code>
        . For general
              tablespaces, a
        <code class="literal">
         KEY_BLOCK_SIZE
        </code>
        value
              equal to the
        <code class="literal">
         InnoDB
        </code>
        page size is not
              permitted.
       </td>
      </tr>
     </tbody>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <p>
   <a class="xref" href="innodb-compression-syntax-warnings.html#innodb-compression-create-and-alter-errors-table" title="Table 17.12 CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF">
    Table 17.12, “CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF”
   </a>
   summarizes error conditions that occur with certain combinations
        of configuration parameters and options on the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   or
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statements, and how
        the options appear in the output of
   <code class="literal">
    SHOW TABLE
        STATUS
   </code>
   .
  </p>
  <p>
   When
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   is
   <code class="literal">
    OFF
   </code>
   , MySQL creates or alters the table, but
        ignores certain settings as shown below. You can see the warning
        messages in the MySQL error log. When
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   is
   <code class="literal">
    ON
   </code>
   , these specified combinations of options
        generate errors, and the table is not created or altered. To see
        the full description of the error condition, issue the
   <code class="literal">
    SHOW ERRORS
   </code>
   statement: example:
  </p>
  <pre class="screen">mysql&gt; <strong class="userinput"><code>CREATE TABLE x (id INT PRIMARY KEY, c INT)</code></strong>
    -&gt; <strong class="userinput"><code>ENGINE=INNODB KEY_BLOCK_SIZE=33333;</code></strong>
ERROR 1005 (HY000): Can't create table 'test.x' (errno: 1478)
mysql&gt; <strong class="userinput"><code>SHOW ERRORS;</code></strong>
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Error | 1478 | InnoDB: invalid KEY_BLOCK_SIZE=33333.     |
| Error | 1005 | Can't create table 'test.x' (errno: 1478) |
+-------+------+-------------------------------------------+
</pre>
  <p>
  </p>
  <div class="table">
   <a name="innodb-compression-create-and-alter-errors-table">
   </a>
   <p class="title">
    <b>
     Table 17.12 CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF
    </b>
   </p>
   <div class="table-contents">
    <table summary="CREATE and ALTER TABLE warnings and errors when InnoDB strict mode is OFF.">
     <colgroup>
      <col style="width: 33%"/>
      <col style="width: 33%"/>
      <col style="width: 33%"/>
     </colgroup>
     <thead>
      <tr>
       <th scope="col">
        Syntax
       </th>
       <th scope="col">
        Warning or Error Condition
       </th>
       <th scope="col">
        Resulting
        <code class="literal">
         ROW_FORMAT
        </code>
        , as shown in
        <code class="literal">
         SHOW TABLE
              STATUS
        </code>
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <th scope="row">
        <code class="literal">
         ROW_FORMAT=REDUNDANT
        </code>
       </th>
       <td>
        None
       </td>
       <td>
        <code class="literal">
         REDUNDANT
        </code>
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         ROW_FORMAT=COMPACT
        </code>
       </th>
       <td>
        None
       </td>
       <td>
        <code class="literal">
         COMPACT
        </code>
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         ROW_FORMAT=COMPRESSED
        </code>
        or
        <code class="literal">
         ROW_FORMAT=DYNAMIC
        </code>
        or
        <code class="literal">
         KEY_BLOCK_SIZE
        </code>
        is specified
       </th>
       <td>
        Ignored for file-per-table tablespaces unless
        <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
         <code class="literal">
          innodb_file_per_table
         </code>
        </a>
        is
              enabled. General tablespaces support all row formats. See
        <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
         Section 17.6.3.3, “General Tablespaces”
        </a>
        .
       </td>
       <td>
        <code class="literal">
         the default row format for file-per-table tablespaces; the
              specified row format for general tablespaces
        </code>
       </td>
      </tr>
      <tr>
       <th scope="row">
        Invalid
        <code class="literal">
         KEY_BLOCK_SIZE
        </code>
        is specified (not 1, 2, 4, 8
              or 16)
       </th>
       <td>
        <code class="literal">
         KEY_BLOCK_SIZE
        </code>
        is ignored
       </td>
       <td>
        the specified row format, or the default row format
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         ROW_FORMAT=COMPRESSED
        </code>
        and valid
        <code class="literal">
         KEY_BLOCK_SIZE
        </code>
        are specified
       </th>
       <td>
        None;
        <code class="literal">
         KEY_BLOCK_SIZE
        </code>
        specified is used
       </td>
       <td>
        <code class="literal">
         COMPRESSED
        </code>
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         KEY_BLOCK_SIZE
        </code>
        is specified with
        <code class="literal">
         REDUNDANT
        </code>
        ,
        <code class="literal">
         COMPACT
        </code>
        or
        <code class="literal">
         DYNAMIC
        </code>
        row format
       </th>
       <td>
        <code class="literal">
         KEY_BLOCK_SIZE
        </code>
        is ignored
       </td>
       <td>
        <code class="literal">
         REDUNDANT
        </code>
        ,
        <code class="literal">
         COMPACT
        </code>
        or
        <code class="literal">
         DYNAMIC
        </code>
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         ROW_FORMAT
        </code>
        is not one of
        <code class="literal">
         REDUNDANT
        </code>
        ,
        <code class="literal">
         COMPACT
        </code>
        ,
        <code class="literal">
         DYNAMIC
        </code>
        or
        <code class="literal">
         COMPRESSED
        </code>
       </th>
       <td>
        Ignored if recognized by the MySQL parser. Otherwise, an error is
              issued.
       </td>
       <td>
        the default row format or N/A
       </td>
      </tr>
     </tbody>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <p>
   When
   <code class="literal">
    innodb_strict_mode
   </code>
   is
   <code class="literal">
    ON
   </code>
   , MySQL rejects invalid
   <code class="literal">
    ROW_FORMAT
   </code>
   or
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   parameters and issues errors.
        Strict mode is
   <code class="literal">
    ON
   </code>
   by default. When
   <code class="literal">
    innodb_strict_mode
   </code>
   is
   <code class="literal">
    OFF
   </code>
   ,
        MySQL issues warnings instead of errors for ignored invalid
        parameters.
  </p>
  <p>
   It is not possible to see the chosen
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   using
   <code class="literal">
    SHOW TABLE
        STATUS
   </code>
   . The statement
   <code class="literal">
    SHOW CREATE
        TABLE
   </code>
   displays the
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   (even if it was ignored when creating the table). The real
        compressed page size of the table cannot be displayed by MySQL.
  </p>
  <h5>
   <a name="idm46045161169424">
   </a>
   SQL Compression Syntax Warnings and Errors for General Tablespaces
  </h5>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If
      <code class="literal">
       FILE_BLOCK_SIZE
      </code>
      was not defined for
            the general tablespace when the tablespace was created, the
            tablespace cannot contain compressed tables. If you attempt
            to add a compressed table, an error is returned, as shown in
            the following example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16191071"><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">TABLESPACE</span> <span class="token punctuation">`</span>ts1<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'ts1.ibd'</span> <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">TABLESPACE</span> ts1 <span class="token keyword">ROW_FORMAT</span><span class="token operator">=</span><span class="token keyword">COMPRESSED</span>
       <span class="token keyword">KEY_BLOCK_SIZE</span><span class="token operator">=</span><span class="token number">8</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1478 (HY000)<span class="token punctuation">:</span> InnoDB<span class="token punctuation">:</span> Tablespace `ts1` cannot contain a COMPRESSED table</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Attempting to add a table with an invalid
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      to a general tablespace
            returns an error, as shown in the following example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3489471"><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">TABLESPACE</span> <span class="token punctuation">`</span>ts2<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'ts2.ibd'</span> <span class="token keyword">FILE_BLOCK_SIZE</span> <span class="token operator">=</span> <span class="token number">8192</span> <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">TABLESPACE</span> ts2 <span class="token keyword">ROW_FORMAT</span><span class="token operator">=</span><span class="token keyword">COMPRESSED</span>
       <span class="token keyword">KEY_BLOCK_SIZE</span><span class="token operator">=</span><span class="token number">4</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1478 (HY000)<span class="token punctuation">:</span> InnoDB<span class="token punctuation">:</span> Tablespace `ts2` uses block size 8192 and cannot
contain a table with physical page size 4096</span></code></pre>
     </div>
     <p>
      For general tablespaces, the
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      of the table must be equal
            to the
      <code class="literal">
       FILE_BLOCK_SIZE
      </code>
      of the tablespace
            divided by 1024. For example, if the
      <code class="literal">
       FILE_BLOCK_SIZE
      </code>
      of the tablespace is
            8192, the
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      of the table
            must be 8.
     </p>
    </li>
    <li class="listitem">
     <p>
      Attempting to add a table with an uncompressed row format to
            a general tablespace configured to store compressed tables
            returns an error, as shown in the following example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24810483"><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">TABLESPACE</span> <span class="token punctuation">`</span>ts3<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'ts3.ibd'</span> <span class="token keyword">FILE_BLOCK_SIZE</span> <span class="token operator">=</span> <span class="token number">8192</span> <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t3 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">TABLESPACE</span> ts3 <span class="token keyword">ROW_FORMAT</span><span class="token operator">=</span><span class="token keyword">COMPACT</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1478 (HY000)<span class="token punctuation">:</span> InnoDB<span class="token punctuation">:</span> Tablespace `ts3` uses block size 8192 and cannot
contain a table with physical page size 16384</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   is not
        applicable to general tablespaces. Tablespace management rules
        for general tablespaces are strictly enforced independently of
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   . For more
        information, see
   <a class="xref" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
    Section 15.1.21, “CREATE TABLESPACE Statement”
   </a>
   .
  </p>
  <p>
   For more information about using compressed tables with general
        tablespaces, see
   <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
    Section 17.6.3.3, “General Tablespaces”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
