Source code editor What Is Ajax
In the sections that follow, we discuss known limitations in MySQL 5.0 Cluster releases as compared with the 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.
This information 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.
See Section 15.10.10, “Previous MySQL Cluster Issues Resolved in MySQL 5.0” for a list of issues in MySQL Cluster in MySQL 4.1 that have been resolved in the current version.
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:
BLOB columns. You cannot create indexes on
NDB table columns that use any of the
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
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.
In this section, we list limits found in MySQL Cluster that either differ from limits found in, or that are not found in, standard MySQL.
Memory usage and recovery. Memory comsumed 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:
DELETE statement on an
NDB table makes the memory formerly used by the deleted rows available for re-use by inserts on the same table only. This memory cannot be used by other
DROP TABLE or
TRUNCATE operation on an
NDB table frees the memory that was used by this table for re-use by any
NDB table, either by the same table or by another
TRUNCATE drops and re-creates the table. See Section 13.2.9, “
Memory freed by
DELETE operations but still allocated to a specific table can also be made available for general re-use by performing a rolling restart of the cluster. See Section 15.4.1, “Performing a Rolling Restart of the Cluster”.
Limits imposed by the cluster's configuration. 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.3.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 dropping the table.
See Section 126.96.36.199, “Defining Data Nodes”, for further information about
The maximum number of operations that can be performed per transaction is set using the configuration parameters
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
Memory usage. 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
NDB storage engine than would be the case for the same table and data using the
MyISAM engine. (In other words, in the case of a
VARCHAR column, the column requires the same amount of storage as a
CHAR column of the same size.)
Node and data object maximums. The following limits apply to numbers of cluster nodes and metadata objects:
The maximum number of data nodes is 48.
A data node cannot have a node ID greater than 49.
The total maximum number of nodes in a MySQL Cluster is 63. This number includes all SQL nodes (MySQL Servers), API nodes (applications accessing the cluster other than MySQL servers), data nodes, and management servers.
The maximum number of metadata objects in MySQL 5.0 Cluster 20320. This limit is hard-coded.
A number of limitations exist in MySQL Cluster with regard to the handling of transactions. These include the following:
Transaction isolation level. The
NDBCLUSTER storage engine supports only the
READ COMMITTED transaction isolation level. (
InnoDB, for example, supports
REPEATABLE READ, and
SERIALIZABLE.) See Section 15.7.5, “Backup Troubleshooting”, for information on how this can affect backing up and restoring Cluster databases.)
SELECT from a Cluster table includes a
TEXT column, the
READ COMMITTED 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 separate table.
Rollbacks. There is no partial rollback of transactions. A duplicate key or similar error rolls back the entire transaction.
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:
TRUNCATE is not transactional when used on
NDB tables. If a
TRUNCATE fails to empty the table, then it must be re-run until it is successful.
DELETE FROM (even with no
WHERE clause) 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 your objective is to empty the table, then you may wish to use
LOAD DATA statements.
LOAD DATA INFILE is not transactional when used on
When executing a
LOAD DATA INFILE statement, the
NDB engine performs commits at irregular intervals that enable better utilization of the communication network. It is not possible to know ahead of time when such commits take place.
LOAD DATA FROM MASTER is not supported in MySQL Cluster.
ALTER TABLE and transactions. When copying an
NDB table as part of an
ALTER TABLE, the creation of the copy is non-transactional. (In any case, this operation is rolled back when the copy is deleted.)
Starting, stopping, or restarting a node may give rise to temporary errors causing some transactions to fail. These include the following cases:
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.)
In either of these cases, any errors that are generated must be handled within the application. This should be done by retrying the transaction.
Some database objects such as tables and indexes have different limitations when using the
NDBCLUSTER storage engine:
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.
Number of tables. The maximum number of tables in a Cluster database in MySQL 5.0 is limited to 1792.
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
TEXT column contributes 256 + 8 = 264 bytes towards this total.
A number of features supported by other storage engines are not supported for
NDB tables. Trying to use any of these features in MySQL Cluster does not cause errors in or of itself; however, errors may occur in applications that expects the features to be supported or enforced:
Foreign key constraints. The foreign key construct is ignored, just as it is in
OPTIMIZE operations are not supported.
LOAD TABLE ... FROM MASTER.
LOAD TABLE ... FROM MASTER is not supported.
Savepoints and rollbacks. Savepoints and rollbacks to savepoints are ignored as in
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. Replication is not supported.
See Section 15.10.3, “Limits Relating to Transaction Handling”, for more information relating to limitations on transaction handling in
The following performance issues are specific to or especially pronounced in MySQL Cluster:
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
Records in range. The
Records in range statistic is available but is not completely tested or officially supported. This may result in non-optimal query plans in some cases. If necessary, you can employ
USE INDEX or
FORCE INDEX to alter the execution plan. See Section 188.8.131.52, “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.
The following are limitations specific to the
NDBCLUSTER storage engine:
Machine architecture. The following issues relate to physical architecture of cluster hosts:
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.
Backup and restore between architectures. 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)
Online schema changes. It is not possible to make online schema changes such as those accomplished using
ALTER TABLE or
CREATE INDEX, as the
NDB 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 to
ALTER TABLE . In such a case, you must issue a
FLUSH TABLES statement to force the cluster to pick up the change.)
SQL_LOG_BIN has no effect on data operations; however, it is supported for schema operations.
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 /
ALTER TABLE operations.
ALTER TABLE is not fully locking when running multiple MySQL servers (no distributed table lock).
Replication. 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.
Database autodiscovery. 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
CREATE DATABASE statement on each additional MySQL server that accesses the same MySQL Cluster. (As of MySQL 5.0.2, you may also use
CREATE SCHEMA .) Once this has been done for a given MySQL server, that server should be able to detect the database tables without error.
DDL operations. DDL operations are not node failure safe. If a node fails while trying to peform one of these (such as
CREATE TABLE or
ALTER TABLE), the data dictionary is locked and no further DDL statements can be executed without restarting the cluster.
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.
Prior to MySQL 5.0.14, all data nodes had to be restarted after bringing up the cluster in order for the management nodes to be able to see one another.
Multiple data node processes. While it is possible to run multiple cluster processes concurrently on a single host, it is not always advisable to do so for reasons of performance and high availability, as well as other considerations. In particular, in MySQL 5.0, we do not support for production use any MySQL Cluster deployment in which more than one ndbd process is run on a single physical machine.
We may support multiple data nodes per host in a future MySQL release, following additional testing. However, in MySQL 5.0, such configurations can be considered experimental only.
Multiple network addresses. Multiple network addresses per data node 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.
It is possible to use multiple network hardware interfaces (such as Ethernet cards) for a single data node, but these must be bound to the same address. This also means that it not possible to use more than one
[TCP] section per connection in the
config.ini file. See Section 184.108.40.206, “Cluster TCP/IP Connections”, for more information.
The following Cluster limitations in MySQL 4.1 have been resolved in MySQL 5.0 as shown below:
Character set support. The
NDB Cluster storage engine supports all character sets and collations available in MySQL 5.0.
Character set directory. 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
/usr/local/mysql/share/mysql/charsets — for character sets.)
Metadata objects. Prior to MySQL 5.0.6, the maximum number of metadata objects possible was 1600. Beginning with MySQL 5.0.6, this limit is increased to 20320.
Column indexes using prefixes. MySQL Cluster in MySQL 5.0 supports column indexes that make use of prefixes.
Query cache. Unlike the case in MySQL 4.1, the Cluster storage engine in MySQL 5.0 supports MySQL's query cache. See Section 7.5.4, “The MySQL Query Cache”.
REPLACE functionality. In MySQL 5.0.19 and earlier,
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.0.20. (See Bug#17431.)
auto_increment_offset server system variables are supported for
NDBCLUSTER tables beginning with MySQL 5.0.46.
Source code editor What Is Ajax