220.127.116.11 Noncompliance with SQL Syntax in NDB Cluster
Some SQL statements relating to certain MySQL features produce errors when used with
NDB tables, as described in the following list:
Temporary tables. Temporary tables are not supported. Trying either to create a temporary table that uses the
NDBstorage engine or to alter an existing temporary table to use
NDBfails with the error Table storage engine 'ndbcluster' does not support the create option 'TEMPORARY'.
Indexes and keys in NDB tables. Keys and indexes on NDB Cluster tables are subject to the following limitations:
Column width. Attempting to create an index on an
NDBtable column whose width is greater than 3072 bytes succeeds, but only the first 3072 bytes are actually used for the index. In such cases, a warning Specified key was too long; max key length is 3072 bytes is issued, and a
SHOW CREATE TABLEstatement shows the length of the index as 3072.
USING HASH keys and NULL. 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
NOT NULL, or re-create the index without the
Prefixes. There are no prefix indexes; only entire columns can be indexed. (The size of an
NDBcolumn 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 Section 18.104.22.168, “Unsupported or Missing Features in NDB Cluster”, for additional information.)
BIT columns. A
BITcolumn cannot be a primary key, unique key, or index, nor can it be part of a composite primary key, unique key, or index.
AUTO_INCREMENT columns. Like other MySQL storage engines, the
NDBstorage engine can handle a maximum of one
AUTO_INCREMENTcolumn per table. However, in the case of an NDB table with no explicit primary key, an
AUTO_INCREMENTcolumn is automatically defined and used as a “hidden” primary key. For this reason, you cannot define a table that has an explicit
AUTO_INCREMENTcolumn unless that column is also declared using the
PRIMARY KEYoption. Attempting to create a table with an
AUTO_INCREMENTcolumn that is not the table's primary key, and using the
NDBstorage engine, fails with an error.
Restrictions on foreign keys. Support for foreign key constraints in NDB 7.5 is comparable to that provided by
InnoDB, subject to the following restrictions:
Every column referenced as a foreign key requires an explicit unique key, if it is not the table's primary key.
ON UPDATE CASCADEis not supported when the reference is to the parent table's primary key.
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
NDBkernel, which views these two rows as being the same, and thus has no way of knowing that this update should be cascaded.
SET DEFAULTis not supported. (Also not supported by
NO ACTIONkeywords are accepted but treated as
RESTRICT. (Also the same as with
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)
Prior to NDB 7.5.6, when adding or dropping a foreign key using
ALTER TABLE, the parent table's metadata is not updated, which makes it possible subsequently to execute
ALTER TABLEstatements on the parent that should be invalid. To work around this issue, execute
SHOW CREATE TABLEon the parent table immediately after adding or dropping the foreign key; this forces the parent's metadata to be reloaded.
This issue is fixed in NDB 7.5.6. (Bug #82989, Bug #24666177)
NDB Cluster and geometry data types. Geometry data types (
WKB) are supported for
NDBtables. However, spatial indexes are not supported.
Character sets and binary log files. Currently, the
ndb_binlog_indextables are created using the
latin1(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)
Creating NDB tables with user-defined partitioning. Support for user-defined partitioning in NDB Cluster is restricted to [
KEYpartitioning. Using any other partitioning type with
CREATE TABLEstatement results in an error.
It is possible to override this restriction, but doing so is not supported for use in production settings. For details, see User-defined partitioning and the NDB storage engine (NDB Cluster).
Default partitioning scheme. All NDB Cluster tables are by default partitioned by
KEYusing the table's primary key as the partitioning key. If no primary key is explicitly set for the table, the “hidden” primary key automatically created by the
NDBstorage engine is used instead. For additional discussion of these and related issues, see Section 22.2.5, “KEY Partitioning”.
The table must have an explicit primary key.
All columns listed in the table's partitioning expression must be part of the primary key.
Exception. If a user-partitioned
NDBCLUSTERtable is created using an empty column-list (that is, using
PARTITION BY [LINEAR] KEY()), then no explicit primary key is required.
Maximum number of partitions for NDBCLUSTER tables. The maximum number of partitions that can defined for a
NDBCLUSTERtable when employing user-defined partitioning is 8 per node group. (See Section 21.1.2, “NDB Cluster Nodes, Node Groups, Replicas, and Partitions”, for more information about NDB Cluster node groups.
DROP PARTITION not supported. It is not possible to drop partitions from
ALTER TABLE ... DROP PARTITION. The other partitioning extensions to
REORGANIZE PARTITION, and
COALESCE PARTITION—are supported for NDB tables, but use copying and so are not optimized. See Section 22.3.1, “Management of RANGE and LIST Partitions” and Section 13.1.8, “ALTER TABLE Statement”.
NDBtable can have a maximum of 3
The NDB API has no special provision for working with
JSONdata, which it views simply as
BLOBdata. Handling data as
JSONmust be performed by the application.
CPU and thread info ndbinfo tables. NDB 7.5.2 adds several new tables to the
ndbinfoinformation database providing information about CPU and thread activity by node, thread ID, and thread type. The tables are listed here:
cpustat: Provides per-second, per-thread CPU statistics
cpustat_50ms: Raw per-thread CPU statistics data, gathered every 50ms
cpustat_1sec: Raw per-thread CPU statistics data, gathered each second
cpustat_20sec: Raw per-thread CPU statistics data, gathered every 20 seconds
threads: Names and descriptions of thread types
For more information about these tables, see Section 21.5.14, “ndbinfo: The NDB Cluster Information Database”.
Lock info ndbinfo tables. NDB 7.5.3 adds new tables to the
ndbinfoinformation database providing information about locks and lock attempts in a running NDB Cluster. These tables are listed here:
cluster_locks: Current lock requests which are waiting for or holding locks; this information can be useful when investigating stalls and deadlocks. Analogous to
locks_per_fragment: Counts of lock claim requests, and their outcomes per fragment, as well as total time spent waiting for locks successfully and unsuccessfully. Analogous to