In this section, we provide a list of known limitations in MySQL
Cluster releases in the 5.0.x series compared to
features available when using the
InnoDB storage engines. Currently, there are no
plans to address these in coming releases of MySQL
5.0; however, we will attempt to supply fixes for
these issues in subsequent release series. If you check the
“Cluster” category in the MySQL bugs database at
http://bugs.mysql.com, you can find known bugs
which (if marked “5.0”) we intend to
correct in upcoming releases of MySQL 5.0.
The list here is intended to be complete with respect to the conditions just set forth. You can report any discrepancies that you encounter to the MySQL bugs database using the instructions given in Section 1.8, “How to Report Bugs or Problems”. If we do not plan to fix the problem in MySQL 5.0, we will add it to the list.
(Note: See the end of this section for a list of issues in MySQL 4.1 Cluster that have been resolved in the current version.)
Noncompliance in syntax (resulting in errors when running existing applications):
Text indexes are not supported. That is, you cannot create
indexes on columns of any of the
datatypes, nor does the
FULLTEXT indexes (these
are supported by
MyISAM only). However,
you can index
VARCHAR columns of
BIT column cannot be a primary key or
part of a composite primary key.
Geometry datatypes (
WKB) are not supported by the NDB
storage engine prior to MySQL 5.0.16. (Note that spatial
indexes are still not supported in MySQL 5.0.16 and
In MySQL 5.0.19 and earlier,
UPDATE IGNORE, and
REPLACE are supported only for primary
keys, but not for unique keys. One possible workaround is
to remove the constraint by dropping the unique index,
perform any inserts, and then add the unique index again.
This limitation is removed for
REPLACE in MySQL
5.0.20. (Bug #17431)
Non-compliance in limits or behavior (may result in errors when running existing applications):
A duplicate key error returns the error message
ERROR 23000: Can't write; duplicate key in
Like other MySQL storage engines, the
NDB storage engine can handle a
maximum of one
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
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
NDB Cluster supports only the
READ COMMITTED transaction
There is no partial rollback of transactions. A duplicate key or similar error results in a rollback of the entire transaction.
Important: If a
SELECT from a Cluster table
transaction isolation level is converted to a read
with read lock. This is done to guarantee consistency,
due to the fact that parts of the values stored in
columns of these types are actually read from a
As noted elsewhere in this chapter, MySQL Cluster does not handle large transactions well; it is better to perform a number of small transactions with a few operations each than to attempt a single large transaction containing a great many operations.
Among other considerations, large transactions require very large amounts of memory. Because of this, the transactional behaviour of a number of MySQL statements is effected as described in the following list:
TRUNCATE is not transactional
when used on
NDB tables. If a
TRUNCATE fails to empty the
table, then it is re-run until it is successful.
DELETE FROM (even with no
is transactional. For tables
containing a great many rows, you may find that
performance is improved by using several
DELETE FROM ... LIMIT ...
statements to “chunk” the delete
operation. If the objective is to empty the table,
then you may wish to use
LOAD DATA INFILE is not
transactional. During such an operation the
NDB engine can and does commit
LOAD DATA FROM MASTER is not
supported in MySQL Cluster.
When copying a table as part of an
TABLE, the creation of the copy is
non-transactional. (In any case, this operation is
rolled back when the copy is deleted.)
Node Start, Stop, or Restart:: Starting, stopping, or restarting a node may give rise to temporary errors causing some transactions to fail. These include the following cases:
When first starting a node, it is possible that you may see Error 1204 Temporary failure, distribution changed and similar temporary errors.
The stopping or failure of any data node can result in a number of different node failure errors. (However, there should be no aborted transactions when performing a planned shutdown of the cluster.)
In either of these cases, any errors that are generated must be handled within the application. This should be done by retrying the transaction.
A number of hard limits exist which are configurable, but available main memory in the cluster sets limits. See the complete list of configuration parameters in Section 15.4.4, “Configuration File”. Most configuration parameters can be upgraded online. These hard limits include:
Database memory size and index memory size
DataMemory is allocated as 32KB
pages. As each
DataMemory page is
used, it is assigned to a specific table; once
allocated, this memory cannot be freed except by
deleting the table.
See Section 220.127.116.11, “Defining MySQL Cluster Data Nodes”,
for further information about
The maximum number of operations that can be performed
per transaction is set using the configuration
MaxNoOfLocalOperations. Note that
TRUNCATE TABLE, and
ALTER TABLE are handled as special
cases by running multiple transactions, and so are not
subject to this limitation.
Different limits related to tables and indexes. For
example, the maximum number of ordered indexes per
table is determined by
Database names, table names and attribute names cannot be
as long in
NDB tables as with other
table handlers. Attribute names are truncated to 31
characters, and if not unique after truncation give rise
to errors. Database names and table names can total a
maximum of 122 characters. (That is, the maximum length
NDB Cluster table name is 122
characters less the number of characters in the name of
the database of which that table is a part.)
All Cluster table rows are of fixed length. This means
(for example) that if a table has one or more
VARCHAR fields containing only
relatively small values, more memory and disk space is
required when using the
engine than would be the case for the same table and data
MyISAM engine. (In other
words, in the case of a
the column requires the same amount of storage as a
CHAR column of the same size.)
The maximum number of tables in a Cluster database is limited to 1792.
The maximum number of ordered indexes per cluster,
AUTO_INCREMENT columns and
hidden primary keys, is 2048.
This limitation was lifted in MySQL 5.0.23.
The maximum number of attributes per table is limited to 128.
The maximum permitted size of any one row is 8KB. Note
TEXT column contributes a maximum of
256 bytes towards this total.
The maximum number of attributes per key is 32.
Unsupported features (do not cause errors, but are not supported or enforced):
The foreign key construct is ignored, just as it is in
Savepoints and rollbacks to savepoints are ignored as in
OPTIMIZE operations are not supported.
LOAD TABLE ... FROM MASTER is not
Performance and limitation-related issues:
There are query performance issues due to sequential
access to the
NDB storage engine; it is
also relatively more expensive to do many range scans than
it is with either
Records in range statistic is not
supported, resulting in non-optimal query plans in some
USE INDEX or
FORCE INDEX as a workaround.
Unique hash indexes created with
HASH cannot be used for accessing a table if
NULL is given as part of the key.
MySQL Cluster does not support durable commits on disk. Commits are replicated, but there is no guarantee that logs are flushed to disk on commit.
SQL_LOG_BIN has no effect on data
operations; however, it is supported for schema
MySQL Cluster cannot produce a binlog for tables having
BLOB columns but no primary key.
Only the following schema operations are logged in a cluster binlog which is not on the mysqld executing the statement:
CREATE DATABASE /
DROP DATABASE /
The only supported isolation level is
COMMITTED. (InnoDB supports
REPEATABLE READ, and
Section 15.8.5, “Backup Troubleshooting”,
for information on how this can affect backup and restore
of Cluster databases.
No durable commits on disk. Commits are replicated, but there is no guarantee that logs are flushed to disk on commit.
Problems relating to multiple MySQL
servers (not relating to
ALTER TABLE is not fully locking when
running multiple MySQL servers (no distributed table
MySQL replication will not work correctly if updates are done on multiple MySQL servers. However, if the database partitioning scheme is done at the application level and no transactions take place across these partitions, replication can be made to work.
Autodiscovery of databases is not supported for multiple
MySQL servers accessing the same MySQL Cluster. However,
autodiscovery of tables is supported in such cases. What
this means is that after a database named
db_name is created or imported
using one MySQL server, you should issue a
statement on each additional MySQL server that accesses
the same MySQL Cluster. (As of MySQL 5.0.2, you may also
.) Once this
has been done for a given MySQL server, that server should
be able to detect the database tables without error.
DDL operations are not node failure safe. If a node fails
while trying to peform one of these (such as
CREATE TABLE or
TABLE), the data dictionary is locked and no
further DDL statements can be executed without restarting
Issues exclusive to MySQL
Cluster (not related to
All machines used in the cluster must have the same architecture. That is, all machines hosting nodes must be either big-endian or little-endian, and you cannot use a mixture of both. For example, you cannot have a management node running on a PowerPC which directs a data node that is running on an x86 machine. This restriction does not apply to machines simply running mysql or other clients that may be accessing the cluster's SQL nodes.
It is also not possible to perform a Cluster backup and restore between different architectures. For example, you cannot back up a cluster running on a big-endian platform and then restore from that backup to a cluster running on a little-endian system. (Bug #19255)
It is not possible to make online schema changes such as
those accomplished using
ALTER TABLE or
CREATE INDEX, as the
Cluster engine does not support autodiscovery of
such changes. (However, you can import or create a table
that uses a different storage engine, and then convert it
. In such a case, you must
FLUSH TABLES statement to force
the cluster to pick up the change.)
Online adding or dropping of nodes is not possible (the cluster must be restarted in such cases).
When using multiple management servers:
You must give nodes explicit IDs in connectstrings because automatic allocation of node IDs does not work across multiple management servers.
You must take extreme care to have the same configurations for all management servers. No special checks for this are performed by the cluster.
In order that management nodes be able to see one another, you must restart all data nodes after bringing up the cluster. (See Bug #13070 for a detailed explanation.)
Multiple network interfaces for data nodes are not supported. Use of these is liable to cause problems: In the event of a data node failure, an SQL node waits for confirmation that the data node went down but never receives it because another route to that data node remains open. This can effectively make the cluster inoperable.
The maximum number of data nodes is 48.
The total maximum number of nodes in a MySQL Cluster is 63. This number includes all MySQL Servers (SQL nodes), data nodes, and management servers.
The following Cluster limitations in MySQL 4.1 have been resolved in MySQL 5.0 as shown below:
NDB Cluster storage engine supports all
character sets and collations available in MySQL
Prior to MySQL 5.0.6, the maximum number of metadata objects possible was 1600. Beginning with 5.0.6, this limit is increased to 20320.
Cluster in MySQL 5.0 supports column indexes that make use of prefixes.
Unlike the case in MySQL 4.1, the Cluster storage engine in MySQL 5.0 supports MySQL' query cache. See Section 5.14, “The MySQL Query Cache”.
Beginning with MySQL 5.0.21, it is possible to install MySQL
with Cluster support to a non-default location and change the
search path for font description files using either the
--character-sets-dir options. (Previously,
ndbd in MySQL 5.0 searched only the default
path — typically
— for character sets.)