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
NDB
storage engine or to
alter an existing temporary table to use
NDB
fails with the error
Table storage engine 'ndbcluster' does not
support the create option 'TEMPORARY'.
Indexes and keys in NDB
tables.
Keys and indexes on MySQL Cluster tables are subject to
the following limitations:
Column width.
Attempting to create an index on an
NDB
table 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
TABLE
statement shows the length of the
index as 3072.
TEXT
and
BLOB
columns.
You cannot create indexes on
NDB
table columns that
use any of the TEXT
or
BLOB
data types.
FULLTEXT
indexes.
The NDB
storage engine
does not support FULLTEXT
indexes,
which are possible for MyISAM
tables only.
However, you can create indexes on
VARCHAR
columns of
NDB
tables.
Prefixes.
There are no prefix indexes; only entire columns can
be indexed. (The size of an NDB
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
Section 17.1.5.6, “Unsupported or Missing Features in MySQL Cluster”,
for additional information.)
BIT
columns.
A BIT
column 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
NDB
storage engine can
handle a maximum of one
AUTO_INCREMENT
column per table.
However, in the case of a Cluster table with no
explicit primary key, an
AUTO_INCREMENT
column is
automatically defined and used as a
“hidden” primary key. For this reason,
you cannot define a table that has an explicit
AUTO_INCREMENT
column unless that
column is also declared using the PRIMARY
KEY
option. Attempting to create a table
with an AUTO_INCREMENT
column that
is not the table's primary key, and using the
NDB
storage engine, fails
with an error.
MySQL Cluster and geometry data types.
Geometry datatypes (WKT
and
WKB
) are supported in
NDB
tables in MySQL 5.1
(including MySQL Cluster NDB 6.X and 7.X through 7.1).
However, spatial indexes are not supported.
Character sets and binary log files.
Currently, the ndb_apply_status
and
ndb_binlog_index
tables 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)
To work around this problem, use only Latin-1 characters
when naming binary log files or setting any the
--basedir
,
--log-bin
, or
--log-bin-index
options.
Creating NDBCLUSTER
tables with
user-defined partitioning.
Support for user-defined partitioning for MySQL Cluster in
MySQL 5.1 (including MySQL Cluster NDB 6.X and 7.X through
7.1) is restricted to [LINEAR
]
KEY
partitioning. Beginning with MySQL
5.1.12, using any other partitioning type with
ENGINE=NDB
or
ENGINE=NDBCLUSTER
in a
CREATE TABLE
statement
results in an error.
Default partitioning scheme.
As of MySQL 5.1.6, all MySQL Cluster tables are by default
partitioned by KEY
using 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
NDBCLUSTER
storage engine is
used instead. For additional discussion of these and
related issues, see Section 18.2.4, “KEY
Partitioning”.
Beginning with MySQL Cluster NDB 6.2.18, MySQL Cluster NDB
6.3.25, and MySQL Cluster NDB 7.0.6,
CREATE TABLE
and
ALTER TABLE
statements that
would cause a user-partitioned
NDBCLUSTER
table not to meet
either or both of the following two requirements are
disallowed, and fail with an error (Bug#40709):
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
NDBCLUSTER
table 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
NDBCLUSTER
table when
employing user-defined partitioning is 8 per node group.
(See Section 17.1.2, “MySQL Cluster Nodes, Node Groups, Replicas, and Partitions”, for
more information about MySQL Cluster node groups.
DROP PARTITION
not supported.
It is not possible to drop partitions from
NDB
tables using
ALTER TABLE ... DROP PARTITION
. The
other partitioning extensions to
ALTER TABLE
—
ADD PARTITION
, REORGANIZE
PARTITION
, and COALESCE
PARTITION
— are supported for Cluster
tables, but use copying and so are not optimised. See
Section 18.3.1, “Management of RANGE
and LIST
Partitions” and
Section 12.1.7, “ALTER TABLE
Syntax”.
Row-based replication.
When using row-based replication with MySQL Cluster,
binary logging cannot be disabled. That is, the
NDB
storage engine ignores
the value of sql_log_bin
.
(Bug#16680)
User Comments
As of 5.1.23-ndb-6.3.8 two new session variables exist which allow NDB to create pseudo-temporary tables.
These tables will be visible to all user sessions and will be present until the cluster restarts.
ndb_table_no_logging=[1|0] controls weather tables created within that session will be REDO logged or checkpointed to disk. This makes the persistence of these NDB table behave similarly to MEMORY engine where are truncated on cluster restart.
ndb_table_temporary=[1|0] controls weather any schema file will be created in the cluster. This option behaves similarly to ndb_table_no_logging except that it will cause no schema file to be created, thus when the cluster restarts it will be completely removed.
Add your own comment.