<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="mysql-cluster-limitations-syntax">
      </a>
      25.2.7.1 Noncompliance with SQL Syntax in NDB Cluster
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045123658896">
  </a>
  <p>
   Some SQL statements relating to certain MySQL features produce
        errors when used with
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   tables,
        as described in the following list:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <b>
       Temporary tables.
      </b>
      Temporary tables are not supported. Trying either to
              create a temporary table that uses the
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      storage engine or to
              alter an existing temporary table to use
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      fails with the error
      <span class="errortext">
       Table storage engine 'ndbcluster' does not
              support the create option 'TEMPORARY'
      </span>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Indexes and keys in NDB tables.
      </b>
      Keys and indexes on NDB Cluster tables are subject to the
              following limitations:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <b>
          Column width.
         </b>
         Attempting to create an index on an
         <code class="literal">
          NDB
         </code>
         table column whose width is
                  greater than 3072 bytes is rejected with
         <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_too_long_key" target="_top">
          <code class="literal">
           ER_TOO_LONG_KEY
          </code>
         </a>
         :
         <span class="errortext">
          Specified key was too long; max key length
                  is 3072 bytes
         </span>
         .
        </p>
        <p>
         Attempting to create an index on an
         <code class="literal">
          NDB
         </code>
         table column whose width is
                greater than 3056 bytes succeeds with a warning. In such
                cases, statistical information is not generated, which
                means a nonoptimal execution plan may be selected. For
                this reason, you should consider making the index length
                shorter than 3056 bytes if possible.
        </p>
       </li>
       <li class="listitem">
        <p>
         <b>
          TEXT and BLOB columns.
         </b>
         You cannot create indexes on
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         table columns that
                  use any of the
         <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>
         data types.
        </p>
       </li>
       <li class="listitem">
        <p>
         <b>
          FULLTEXT indexes.
         </b>
         The
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         storage engine
                  does not support
         <code class="literal">
          FULLTEXT
         </code>
         indexes,
                  which are possible for
         <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
          <code class="literal">
           MyISAM
          </code>
         </a>
         and
         <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
          <code class="literal">
           InnoDB
          </code>
         </a>
         tables only.
        </p>
        <p>
         However, you can create indexes on
         <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
          <code class="literal">
           VARCHAR
          </code>
         </a>
         columns of
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         tables.
        </p>
       </li>
       <li class="listitem">
        <p>
         <b>
          USING HASH keys and NULL.
         </b>
         Using nullable columns in unique keys and primary keys
                  means that queries using these columns are handled as
                  full table scans. To work around this issue, make the
                  column
         <code class="literal">
          NOT NULL
         </code>
         , or re-create the
                  index without the
         <code class="literal">
          USING HASH
         </code>
         option.
        </p>
       </li>
       <li class="listitem">
        <p>
         <b>
          Prefixes.
         </b>
         There are no prefix indexes; only entire columns can
                  be indexed. (The size of an
         <code class="literal">
          NDB
         </code>
         column index is always the same as the width of the
                  column in bytes, up to and including 3072 bytes, as
                  described earlier in this section. Also see
         <a class="xref" href="mysql-cluster-limitations-unsupported.html" title="25.2.7.6 Unsupported or Missing Features in NDB Cluster">
          Section 25.2.7.6, “Unsupported or Missing Features in NDB Cluster”
         </a>
         ,
                  for additional information.)
        </p>
       </li>
       <li class="listitem">
        <p>
         <b>
          BIT columns.
         </b>
         A
         <a class="link" href="bit-type.html" title="13.1.5 Bit-Value Type - BIT">
          <code class="literal">
           BIT
          </code>
         </a>
         column cannot be
                  a primary key, unique key, or index, nor can it be
                  part of a composite primary key, unique key, or index.
        </p>
       </li>
       <li class="listitem">
        <p>
         <b>
          AUTO_INCREMENT columns.
         </b>
         Like other MySQL storage engines, the
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         storage engine can
                  handle a maximum of one
         <code class="literal">
          AUTO_INCREMENT
         </code>
         column per table,
                  and this column must be indexed. However, in the case
                  of an NDB table with no explicit primary key, an
         <code class="literal">
          AUTO_INCREMENT
         </code>
         column is
                  automatically defined and used as a
         <span class="quote">
          “
          <span class="quote">
           hidden
          </span>
          ”
         </span>
         primary key. For this reason,
                  you cannot create an
         <code class="literal">
          NDB
         </code>
         table
                  having an
         <code class="literal">
          AUTO_INCREMENT
         </code>
         column and
                  no explicit primary key.
        </p>
        <p>
         The following
         <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
          <code class="literal">
           CREATE
                TABLE
          </code>
         </a>
         statements do not work, as shown here:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29204760"><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 comment" spellcheck="true"># No index on AUTO_INCREMENT column; table has no primary key</span>
<span class="token comment" spellcheck="true"># Raises ER_WRONG_AUTO_KEY</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> n <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     a <span class="token datatype">INT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     b <span class="token datatype">INT</span> <span class="token keyword">AUTO_INCREMENT</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">NDB</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1075 (42000)<span class="token punctuation">:</span> Incorrect table definition; there can be only one auto
column and it must be defined as a key 

# Index on AUTO_INCREMENT column; table has no primary key
# Raises NDB error 4335</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> n <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     a <span class="token datatype">INT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     b <span class="token datatype">INT</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">KEY</span> k <span class="token punctuation">(</span>b<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">NDB</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1296 (HY000)<span class="token punctuation">:</span> Got error 4335 'Only one autoincrement column allowed per
table. Having a table without primary key uses an autoincr' from NDBCLUSTER</span></code></pre>
        </div>
        <p>
         The following statement creates a table with a primary
                key, an
         <code class="literal">
          AUTO_INCREMENT
         </code>
         column, and an
                index on this column, and succeeds:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8894644"><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 comment" spellcheck="true"># Index on AUTO_INCREMENT column; table has a primary key</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> n <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     a <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 prompt">    -&gt;</span>     b <span class="token datatype">INT</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">KEY</span> k <span class="token punctuation">(</span>b<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">NDB</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.38 sec)</span></code></pre>
        </div>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <b>
       Restrictions on foreign keys.
      </b>
      Support for foreign key constraints in NDB
              8.4 is comparable to that provided by
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      , subject to the
              following restrictions:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Every column referenced as a foreign key requires an
                explicit unique key, if it is not the table's
                primary key.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          ON UPDATE CASCADE
         </code>
         is not supported
                when the reference is to the parent table's primary
                key.
        </p>
        <p>
         This is because an update of a primary key is
                implemented as a delete of the old row (containing the
                old primary key) plus an insert of the new row (with a
                new primary key). This is not visible to the
         <code class="literal">
          NDB
         </code>
         kernel, which views these two
                rows as being the same, and thus has no way of knowing
                that this update should be cascaded.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          ON DELETE CASCADE
         </code>
         is also not
                supported where the child table contains one or more
                columns of any of the
         <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>
         types. (Bug #89511,
                Bug #27484882)
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          SET DEFAULT
         </code>
         is not supported. (Also
                not supported by
         <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
          <code class="literal">
           InnoDB
          </code>
         </a>
         .)
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <code class="literal">
          NO ACTION
         </code>
         keyword is accepted but
                treated as
         <code class="literal">
          RESTRICT
         </code>
         .
         <code class="literal">
          NO
                ACTION
         </code>
         , which is a standard SQL keyword, is
                the default in MySQL 8.4. (Also the same as
                with
         <code class="literal">
          InnoDB
         </code>
         .)
        </p>
       </li>
       <li class="listitem">
        <p>
         In earlier versions of NDB Cluster, when creating a
                table with foreign key referencing an index in another
                table, it sometimes appeared possible to create the
                foreign key even if the order of the columns in the
                indexes did not match, due to the fact that an
                appropriate error was not always returned internally. A
                partial fix for this issue improved the error used
                internally to work in most cases; however, it remains
                possible for this situation to occur in the event that
                the parent index is a unique index. (Bug #18094360)
        </p>
       </li>
      </ul>
     </div>
     <p>
      For more information, see
      <a class="xref" href="create-table-foreign-keys.html" title="15.1.20.5 FOREIGN KEY Constraints">
       Section 15.1.20.5, “FOREIGN KEY Constraints”
      </a>
      , and
      <a class="xref" href="constraint-foreign-key.html" title="1.7.3.2 FOREIGN KEY Constraints">
       Section 1.7.3.2, “FOREIGN KEY Constraints”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       NDB Cluster and geometry data types.
      </b>
      <a class="indexterm" name="idm46045123575968">
      </a>
      Geometry data types (
      <code class="literal">
       WKT
      </code>
      and
      <code class="literal">
       WKB
      </code>
      ) are supported for
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables. However, spatial
              indexes are not supported.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Character sets and binary log files.
      </b>
      Currently, the
      <code class="literal">
       ndb_apply_status
      </code>
      and
      <code class="literal">
       ndb_binlog_index
      </code>
      tables are created
              using the
      <code class="literal">
       latin1
      </code>
      (ASCII) character set.
              Because names of binary logs are recorded in this table,
              binary log files named using non-Latin characters are not
              referenced correctly in these tables. This is a known
              issue, which we are working to fix. (Bug #50226)
     </p>
     <p>
      To work around this problem, use only Latin-1 characters
            when naming binary log files or setting any the
      <a class="link" href="server-system-variables.html#sysvar_basedir">
       <code class="option">
        --basedir
       </code>
      </a>
      ,
      <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
       <code class="option">
        --log-bin
       </code>
      </a>
      , or
      <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin-index">
       <code class="option">
        --log-bin-index
       </code>
      </a>
      options.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Creating NDB tables with user-defined partitioning.
      </b>
      <a class="indexterm" name="idm46045123562208">
      </a>
      <a class="indexterm" name="idm46045123560720">
      </a>
      <a class="indexterm" name="idm46045123559232">
      </a>
      Support for user-defined partitioning in NDB Cluster is
              restricted to [
      <code class="literal">
       LINEAR
      </code>
      ]
      <code class="literal">
       KEY
      </code>
      partitioning. Using any other
              partitioning type with
      <code class="literal">
       ENGINE=NDB
      </code>
      or
      <code class="literal">
       ENGINE=NDBCLUSTER
      </code>
      in a
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement
              results in an error.
     </p>
     <p>
      It is possible to override this restriction, but doing so is
            not supported for use in production settings. For details,
            see
      <a class="xref" href="partitioning-limitations-storage-engines.html#partitioning-limitations-ndb" title="User-defined partitioning and the NDB storage engine (NDB Cluster)">
       User-defined partitioning and the NDB storage engine (NDB Cluster)
      </a>
      .
     </p>
     <p>
      <b>
       Default partitioning scheme.
      </b>
      All NDB Cluster tables are by default partitioned by
      <code class="literal">
       KEY
      </code>
      using the table's primary key
              as the partitioning key. If no primary key is explicitly
              set for the table, the
      <span class="quote">
       “
       <span class="quote">
        hidden
       </span>
       ”
      </span>
      primary key
              automatically created by the
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      storage engine is used
              instead. For additional discussion of these and related
              issues, see
      <a class="xref" href="partitioning-key.html" title="26.2.5 KEY Partitioning">
       Section 26.2.5, “KEY Partitioning”
      </a>
      .
     </p>
     <p>
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      and
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statements that
            would cause a user-partitioned
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDBCLUSTER
       </code>
      </a>
      table not to meet
            either or both of the following two requirements are not
            permitted, and fail with an error:
     </p>
     <div class="orderedlist">
      <ol class="orderedlist" type="1">
       <li class="listitem">
        <p>
         The table must have an explicit primary key.
        </p>
       </li>
       <li class="listitem">
        <p>
         All columns listed in the table's partitioning
                expression must be part of the primary key.
        </p>
       </li>
      </ol>
     </div>
     <p>
      <b>
       Exception.
      </b>
      If a user-partitioned
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDBCLUSTER
       </code>
      </a>
      table is created
              using an empty column-list (that is, using
      <code class="literal">
       PARTITION BY [LINEAR] KEY()
      </code>
      ), then no
              explicit primary key is required.
     </p>
     <p>
      <b>
       Maximum number of partitions for NDBCLUSTER tables.
      </b>
      The maximum number of partitions that can defined for a
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDBCLUSTER
       </code>
      </a>
      table when
              employing user-defined partitioning is 8 per node group.
              (See
      <a class="xref" href="mysql-cluster-nodes-groups.html" title="25.2.2 NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions">
       Section 25.2.2, “NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions”
      </a>
      , for
              more information about NDB Cluster node groups.
     </p>
     <p>
      <b>
       DROP PARTITION not supported.
      </b>
      It is not possible to drop partitions from
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables using
      <code class="literal">
       ALTER TABLE ... DROP PARTITION
      </code>
      . The
              other partitioning extensions to
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER
              TABLE
       </code>
      </a>
      —
      <code class="literal">
       ADD PARTITION
      </code>
      ,
      <code class="literal">
       REORGANIZE PARTITION
      </code>
      , and
      <code class="literal">
       COALESCE PARTITION
      </code>
      —are supported
              for NDB tables, but use copying and so are not optimized.
              See
      <a class="xref" href="partitioning-management-range-list.html" title="26.3.1 Management of RANGE and LIST Partitions">
       Section 26.3.1, “Management of RANGE and LIST Partitions”
      </a>
      and
      <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       Section 15.1.9, “ALTER TABLE Statement”
      </a>
      .
     </p>
     <p>
      <b>
       Partition selection.
      </b>
      Partition selection is not supported for
      <code class="literal">
       NDB
      </code>
      tables. See
      <a class="xref" href="partitioning-selection.html" title="26.5 Partition Selection">
       Section 26.5, “Partition Selection”
      </a>
      , for more
              information.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       JSON data type.
      </b>
      The MySQL
      <a class="link" href="json.html" title="13.5 The JSON Data Type">
       <code class="literal">
        JSON
       </code>
      </a>
      data type is
              supported for
      <code class="literal">
       NDB
      </code>
      tables in the
      <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
       <span class="command">
        <strong>
         mysqld
        </strong>
       </span>
      </a>
      supplied with NDB
              8.4.
     </p>
     <a class="indexterm" name="idm46045123517856">
     </a>
     <p>
      An
      <code class="literal">
       NDB
      </code>
      table can have a maximum of 3
      <code class="literal">
       JSON
      </code>
      columns.
     </p>
     <p>
      The NDB API has no special provision for working with
      <code class="literal">
       JSON
      </code>
      data, which it views simply as
      <code class="literal">
       BLOB
      </code>
      data. Handling data as
      <code class="literal">
       JSON
      </code>
      must be performed by the
            application.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
