Chapter 12. Known Limitations of MySQL Cluster

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:

MySQL Cluster and geometry data types.  Geometry datatypes (WKT and WKB) are supported in NDB tables in MySQL 5.1. However, spatial indexes are not supported.

Creating NDBCLUSTER tables with user-defined partitioning.  Support for user-defined partitioning for MySQL Cluster in MySQL 5.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 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):

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 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 Management of RANGE and LIST Partitions and 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)

Memory usage and recovery.  Memory consumed when data is inserted into an NDB table is not automatically recovered when deleted, as it is with other storage engines. Instead, the following rules hold true:

Transaction isolation level.  The NDBCLUSTER storage engine supports only the READ COMMITTED transaction isolation level. (InnoDB, for example, supports READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, and SERIALIZABLE.) See Section 7.3.4, “MySQL Cluster Backup Troubleshooting”, for information on how this can affect backing up and restoring Cluster databases.)

Transactions and memory usage.  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:

Transactions and the COUNT() function.  When using MySQL Cluster Replication, it is not possible to guarantee the transactional consistency of the COUNT() function on the slave. In other words, when performing on the master a series of statements (INSERT, DELETE, or both) that changes the number of rows in a table within a single transaction, executing SELECT COUNT(*) FROM table queries on the slave may yield intermediate results. This is due to the fact that SELECT COUNT(...) may perform dirty reads, and is not a bug in the NDB storage engine. (See Bug#31321 for more information.)

Temporary errors.  When first starting a node, it is possible that you may see Error 1204 Temporary failure, distribution changed and similar temporary errors.

Errors due to node failure.  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.)

Identifiers.  Database names, table names and attribute names cannot be as long in NDB tables as when using 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. In other words, the maximum length for an NDB table name is 122 characters, less the number of characters in the name of the database of which that table is a part.

Table names containing special characters.  NDB tables whose names contain characters other than letters, numbers, dashes, and underscores and which are created on one SQL node were not always discovered correctly by other SQL nodes. (Bug#31470)

Number of database objects.  The maximum number of all NDB database objects in a single MySQL Cluster — including databases, tables, and indexes — is limited to 20320.

Attributes per table.  The maximum number of attributes (that is, columns and indexes) per table is limited to 128.

Attributes per key.  The maximum number of attributes per key is 32.

Row size.  The maximum permitted size of any one row is 8KB. Note that each BLOB or TEXT column contributes 256 + 8 = 264 bytes towards this total.

Foreign key constraints.  The foreign key construct is ignored, just as it is in MyISAM tables.

OPTIMIZE operations.  OPTIMIZE operations are not supported.

Beginning with MySQL Cluster NDB 6.3.7, this limitation has been lifted. See Section 12.11, “Previous MySQL Cluster Issues Resolved in MySQL 5.1 and MySQL Cluster NDB 6.x”, for more information.

LOAD TABLE ... FROM MASTER.  LOAD TABLE ... FROM MASTER is not supported.

Savepoints and rollbacks.  Savepoints and rollbacks to savepoints are ignored as in MyISAM.

Durability of commits.  There are no durable commits on disk. Commits are replicated, but there is no guarantee that logs are flushed to disk on commit.

Replication.  Statement-based replication is not supported. Use --binlog-format=ROW (or --binlog-format=MIXED) when setting up cluster replication. See Chapter 9, MySQL Cluster Replication, for more information.

Range scans.  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 MyISAM or InnoDB.

Reliability of Records in range.  The Records in range statistic is available but is not completely tested or officially supported. This may result in nonoptimal query plans in some cases. If necessary, you can employ USE INDEX or FORCE INDEX to alter the execution plan. See Index Hint Syntax, for more information on how to do this.

Unique hash indexes.  Unique hash indexes created with USING HASH cannot be used for accessing a table if NULL is given as part of the key.

Machine architecture.  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.

Adding and dropping of data nodes.  Online adding or dropping of data nodes is not currently possible. In such cases, the entire cluster must be restarted.

Binary logging.  MySQL Cluster has the following limitations or restrictions with regard to binary logging:

Disk data objects are subject to the following maximums:

Use of Disk Data tables is not supported when running the cluster in diskless mode. Beginning with MySQL 5.1.12, it is disallowed altogether. (Bug#20008)

No distributed table locks.  A LOCK TABLES works only for the SQL node on which the lock is issued; no other SQL node in the cluster “sees” this lock. This is also true for a lock issued by any statement that locks tables as part of its operations. (See next item for an example.)

ALTER TABLE operations.  ALTER TABLE is not fully locking when running multiple MySQL servers (SQL nodes). (As discussed in the previous item, MySQL Cluster does not support distributed table locks.)

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.

Variable-length column support.  The NDBCLUSTER storage engine now supports variable-length column types for in-memory tables.

Previously, for example, any Cluster table having one or more VARCHAR fields which contained only relatively small values, much more memory and disk space were required when using the NDBCLUSTER storage engine than would have been the case for the same table and data using the MyISAM engine. In other words, in the case of a VARCHAR column, such a column required the same amount of storage as a CHAR column of the same size. In MySQL 5.1, this is no longer the case for in-memory tables, where storage requirements for variable-length column types such as VARCHAR and BINARY are comparable to those for these column types when used in MyISAM tables (see Data Type Storage Requirements).

Replication with MySQL Cluster.  It is now possible to use MySQL replication with Cluster databases. For details, see Chapter 9, MySQL Cluster Replication.

Circular Replication.  Circular replication is also supported with MySQL Cluster, beginning with MySQL 5.1.18. See Section 9.10, “MySQL Cluster Replication — Multi-Master and Circular Replication”.

auto_increment_increment and auto_increment_offset.  The auto_increment_increment and auto_increment_offset server system variables are supported for Cluster replication beginning with MySQL 5.1.20, MySQL Cluster NDB 6.2.5, and MySQL Cluster 6.3.2.

Database autodiscovery and online schema changes.  Autodiscovery of databases is now supported for multiple MySQL servers accessing the same MySQL Cluster. Formerly, autodiscovery in MySQL Cluster 5.1 and MySQL Cluster NDB 6.x releases required that a given mysqld was already running and connected to the cluster at the time that the database was created on a different mysqld — in other words, when a mysqld process connected to the cluster after a database named db_name was created, it was necessary to issue a CREATE DATABASE db_name or CREATE SCHEMA db_name statement on the “new” MySQL server when it first accesseed that MySQL Cluster. Beginning with MySQL Cluster NDB 6.2.16 and MySQL Cluster NDB 6.3.18, such a CREATE statement is no longer required. (Bug#39612)

This also means that online schema changes in NDB tables are now possible. That is, the result of operations such as ALTER TABLE and CREATE INDEX performed on one SQL node in the cluster are now visible to the cluster's other SQL nodes without any additional action being taken.

Backup and restore between architectures.  Beginning with MySQL 5.1.10, it is possible to perform a Cluster backup and restore between different architectures. Previously — for example — you could not 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)

Character set directory.  Beginning with MySQL 5.1.10, it is possible to install MySQL with Cluster support to a nondefault location and change the search path for font description files using either the --basedir or --character-sets-dir options. (Previously, ndbd in MySQL 5.1 searched only the default path — typically /usr/local/mysql/share/mysql/charsets — for character sets.)

Multiple management servers.  In MySQL 5.1 (including all MySQL Cluster NDB 6.x versions), it is no longer necessary, when running multiple management servers, to restart all the cluster's data nodes to enable the management nodes to see one another.

Also, when using multiple management servers and starting concurrently several API nodes (possibly including one or more SQL nodes) whose connectstrings listed the management servers in different order, it was possible for 2 API nodes to be assigned the same node ID. This issue is resolved in MySQL Cluster NDB 6.2.17, 6.3.23, and 6.4.3. (Bug#42973)

Multiple data node processes per host.  Beginning with MySQL Cluster NDB 6.2.0, you can use multiple data node processes on a single host. (In MySQL Cluster NDB 6.1, MySQL 5.1, and earlier release series, we did not support production MySQL Cluster deployments in which more than one ndbd process was run on a single physical machine.)

Length of CREATE TABLE statements.  CREATE TABLE statements may be no more than 4096 characters in length. This limitation affects MySQL 5.1.6, 5.1.7, and 5.1.8 only. (See Bug#17813)

IGNORE and REPLACE functionality.  In MySQL 5.1.7 and earlier, INSERT IGNORE, UPDATE IGNORE, and REPLACE were supported only for primary keys, but not for unique keys. It was possible to work around this issue by removing the constraint, then dropping the unique index, performing any inserts, and then adding the unique index again.

This limitation was removed for INSERT IGNORE and REPLACE in MySQL 5.1.8. (See Bug#17431.)

AUTO_INCREMENT columns.  In MySQL 5.1.10 and earlier versions, the maximum number of tables having AUTO_INCREMENT columns — including those belonging to hidden primary keys — was 2048.

This limitation was lifted in MySQL 5.1.11.

Maximum number of cluster nodes.  Prior to MySQL Cluster NDB 6.1.1, the total maximum number of nodes in a MySQL Cluster was 63, including all SQL nodes (MySQL Servers), API nodes (applications accessing the cluster other than MySQL servers), data nodes, and management servers.

Starting with MySQL Cluster NDB 6.1.1, the total maximum number of nodes in a MySQL Cluster is 255, including all SQL nodes (MySQL Servers), API nodes (applications accessing the cluster other than MySQL servers), data nodes, and management servers. The total number of data nodes and management nodes beginning with this version is 63, of which up to 48 can be data nodes.

Recovery of memory from deleted rows.  Beginning with MySQL Cluster NDB 6.3.7, memory can be reclaimed from an NDB table for reuse with any NDB table by employing OPTIMIZE TABLE, subject to the following limitations:

You can regulate the effects of OPTIMIZE on performance by adjusting the value of the global system variable ndb_optimization_delay, which sets the number of milliseconds to wait between batches of rows being processed by OPTIMIZE. The default value is 10 milliseconds. It is possible to set a lower value (to a minimum of 0), but not recommended. The maximum is 100000 milliseconds (that is, 100 seconds).

Rollbacks.  Prior to MySQL Cluster NDB 6.3.19, the NDBCLUSTER storage engine did not support partial transactions or partial rollbacks of transactions. A duplicate key or similar error aborted the entire transaction, and subsequent statements raised ERROR 1296 (HY000): Got error 4350 'Transaction already aborted' from NDBCLUSTER. In such cases, it was necessary to issue an explicit ROLLBACK and retry the entire transaction.

Beginning with MySQL Cluster NDB 6.3.19, this limitation has been removed, and the behavior of NDBCLUSTER is now in line with that of other transactional storage engines such as InnoDB which can roll back individual statements. (Bug#32656)

Number of tables.  Previously, the maximum number of NDBCLUSTER tables in a single MySQL Cluster was 1792, but this is no longer the case in MySQL 5.1 (including MySQL Cluster NDB 6.x). However, the number of tables is still included in the total maximum number of NDBCLUSTER database objects (20320). (See Section 12.5, “Limits Associated with Database Objects in MySQL Cluster”.)

DDL operations.  Beginning with MySQL Cluster NDB 6.4.0, DDL operations (such as CREATE TABLE or ALTER TABLE) are protected from data node failures. Previously, if a data node failed while trying to perform one of these, the data dictionary became locked and no further DDL statements could be executed without restarting the cluster (Bug#36718).