Questions

Questions and Answers

30.1.1: When did MySQL 5.1 become production-ready (GA)?

MySQL 5.0.15 was released for production use on 19 October 2005. We are now working on MySQL 5.1, which is currently in beta.

30.1.2: Can MySQL 5.1 do subqueries?

Yes. See Section 12.2.8, “Subquery Syntax”.

30.1.3: Can MySQL 5.1 peform multiple-table inserts, updates, and deletes?

Yes. For the syntax required to perform multiple-table updates, see Section 12.2.10, “UPDATE Syntax”; for that required to perform multiple-table deletes, see Section 12.2.1, “DELETE Syntax”.

A multiple-table insert can be accomplished using a trigger whose FOR EACH ROW clause contains multiple INSERT statements within a BEGIN ... END block. See Section 20.3, “Using Triggers”.

30.1.4: Does MySQL 5.1 have a Query Cache? Does it work on Server, Instance or Database?

Yes. The query cache operates on the server level, caching complete result sets matched with the original query string. If an exactly identical query is made (which often happens, particularly in web applications), no parsing or execution is necessary; the result is sent directly from the cache. Various tuning options are available. See Section 6.5.4, “The MySQL Query Cache”.

30.1.5: Does MySQL 5.1 have Sequences?

No. However, MySQL has an AUTO_INCREMENT system, which in MySQL 5.1 can also handle inserts in a multi-master replication setup. With the --auto-increment-increment and --auto-increment-offset startup options, you can set each server to generate auto-increment values that don't conflict with other servers. The --auto-increment-increment value should be greater than the number of servers, and each server should have a unique offset.

30.1.6: Does MySQL 5.1 have a NOW() function with fractions of seconds?

No. This is on the MySQL roadmap as a “rolling feature”. This means that it is not a flagship feature, but will be implemented, development time permitting. Specific customer demand may change this scheduling.

However, MySQL does parse time strings with a fractional component. See Section 10.3.2, “The TIME Type”.

30.1.7: Does MySQL 5.1 work with multi-core processors?

Yes. MySQL is fully multi-threaded, and will make use of multiple CPUs, provided that the operating system supports them.

30.1.8: Is there a hot backup tool for MyISAM like InnoDB Hot Backup?

This is currently under development for a future MySQL release.

30.1.9: Have there been there any improvements in error reporting when foreign keys fail? Does MySQL now report which column and reference failed?

The foreign key support in InnoDB has seen improvements in each major version of MySQL. Foreign key support generic to all storage engines is scheduled for MySQL 5.2; this should resolve any inadequacies in the current storage engine specific implementation.

30.1.10: Can MySQL 5.1 perform ACID transactions?

Yes. All current MySQL versions support transactions. The InnoDB storage engine offers full ACID transactions with row-level locking, multi-versioning, non-locking repeatable reads, and all four SQL standard isolation levels.

The NDB storage engine supports the READ COMMITTED transaction isolation level only.

Questions

Questions and Answers

30.2.1: Where can I obtain complete documentation for MySQL storage engines and the pluggable storage engine architecture?

See Chapter 13, Storage Engines. That chapter contains information about all MySQL storage engines except for the NDB storage engine used for MySQL Cluster; NDB is covered in Chapter 16, MySQL Cluster.

MySQL Enterprise For expert advice about the storage engine(s) most suitable to your circumstances subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.

30.2.2: Are there any new storage engines in MySQL 5.1?

MySQL 5.1 introduces an alpha version of the new Falcon storage engine.

Falcon support is not available in the standard MySQL 5.1 release. Falcon is available only in a specially forked release of MySQL 5.1. Information is provided here for evaluation purposes only.

For information about the Falcon storage engine, see Using Falcon.

There have also been significant improvements in existing storage engines, in particular for the NDB storage engine that forms the basis for MySQL Cluster.

30.2.3: Have any storage engines been removed in MySQL 5.1?

Yes. MySQL 5.1 no longer supports the BDB storage engine. Any existing BDB tables should be converted to another storage engine before upgrading to MySQL 5.1.

30.2.4: What are the unique benefits of the ARCHIVE storage engine?

The ARCHIVE storage engine is ideally suited for storing large amounts of data without indexes; it has a very small footprint, and performs selects using table scans. See Section 13.10, “The ARCHIVE Storage Engine”, for details.

30.2.5: Do the new features in MySQL 5.1 apply to all storage engines?

The general new features such as views, stored procedures, triggers, INFORMATION_SCHEMA, precision math (DECIMAL column type), and the BIT column type, apply to all storage engines. There are also additions and changes for specific storage engines.

Questions

Questions and Answers

30.3.1: What are server SQL modes?

Server SQL modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers. The MySQL Server apply these modes individually to different clients. For more information, see Section 5.2.6, “SQL Modes”.

30.3.2: How many server SQL modes are there?

Each mode can be independently switched on and off. See Section 5.2.6, “SQL Modes”, for a complete list of available modes.

30.3.3: How do you determine the server SQL mode?

You can set the default SQL mode (for mysqld startup) with the --sql-mode option. Using the statement SET [SESSION|GLOBAL] sql_mode='modes', you can change the settings from within a connection, either locally to the connection, or to take effect globally. You can retrieve the current mode by issuing a SELECT @@sql_mode statement.

30.3.4: Is the mode dependent on the database or connection?

A mode is not linked to a particular database. Modes can be set locally to the session (connection), or globally for the server. you can change these settings using SET [SESSION|GLOBAL] sql_mode='modes'.

30.3.5: Can the rules for strict mode be extended?

When we refer to strict mode, we mean a mode where at least one of the modes TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES is enabled. Options can be combined, so you can add additional restrictions to a mode. See Section 5.2.6, “SQL Modes”, for more information.

30.3.6: Does strict mode impact performance?

The intensive validation of input data that some settings requires more time than if the validation is not done. While the performance impact is not that great, if you do not require such validation (perhaps your application already handles all of this), then MySQL gives you the option of leaving strict mode disabled. However — if you do require it — strict mode can provide such validation.

30.3.7: What is the default server SQL mode when My SQL 5.1 is installed?

By default, no special modes are enabled. See Section 5.2.6, “SQL Modes”, for information about all available modes and MySQL's default behavior.

Questions

Questions and Answers

30.4.1: Does MySQL 5.1 support stored procedures?

Yes. MySQL 5.1 supports two types of stored routines — stored procedures and stored functions.

30.4.2: Where can I find documentation for MySQL stored procedures and stored functions?

See Chapter 19, Stored Procedures and Functions.

30.4.3: Is there a discussion forum for MySQL stored procedures?

Yes. See http://forums.mysql.com/list.php?98.

30.4.4: Where can I find the ANSI SQL 2003 specification for stored procedures?

Unfortunately, the official specifications are not freely available (ANSI makes them available for purchase). However, there are books — such as SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer — which give a comprehensive overview of the standard, including coverage of stored procedures.

30.4.5: How do you manage stored routines?

It is always good practice to use a clear naming scheme for your stored routines. You can manage stored procedures with CREATE [FUNCTION|PROCEDURE], ALTER [FUNCTION|PROCEDURE], DROP [FUNCTION|PROCEDURE], and SHOW CREATE [FUNCTION|PROCEDURE]. You can obtain information about existing stored procedures using the ROUTINES table in the INFORMATION_SCHEMA database (see Section 23.14, “The INFORMATION_SCHEMA ROUTINES Table”).

30.4.6: Is there a way to view all stored procedures and stored functions in a given database?

Yes. For a database named dbname, use this query on the INFORMATION_SCHEMA.ROUTINES table:

SELECT ROUTINE_TYPE, ROUTINE_NAME 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_SCHEMA='dbname';

For more information, see Section 23.14, “The INFORMATION_SCHEMA ROUTINES Table”.

The body of a stored routine can be viewed using SHOW CREATE FUNCTION (for a stored function) or SHOW CREATE PROCEDURE (for a stored procedure). See Section 12.5.4.8, “SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax”, for more information.

30.4.7: Where are stored procedures stored?

In the proc table of the mysql system database. However, you should not access the tables in the system database directly. Instead, use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 12.5.4.8, “SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax”, for more information about these statements.

You can also query the ROUTINES table in the INFORMATION_SCHEMA database — see Section 23.14, “The INFORMATION_SCHEMA ROUTINES Table”, for information about this table.

30.4.8: Is it possible to group stored procedures or stored functions into packages?

No. This is not supported in MySQL 5.1.

30.4.9: Can a stored procedure call another stored procedure?

Yes.

30.4.10: Can a stored procedure call a trigger?

A stored procedure can execute an SQL statement, such as an UPDATE, that causes a trigger to fire.

30.4.11: Can a stored procedure access tables?

Yes. A stored procedure can access one or more tables as required.

30.4.12: Do stored procedures have a statement for raising application errors?

Not in MySQL 5.1. We intend to implement the SQL standard SIGNAL and RESIGNAL statements in a future MySQL release.

30.4.13: Do stored procedures provide exception handling?

MySQL implements HANDLER definitions according to the SQL standard. See Section 19.2.8.2, “DECLARE Handlers”, for details.

30.4.14: Can MySQL 5.1 stored routines return result sets?

Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or above) client-server protocol for this to work. This means that — for instance — in PHP, you need to use the mysqli extension rather than the old mysql extension.

30.4.15: Is WITH RECOMPILE supported for stored procedures?

Not in MySQL 5.1.

30.4.16: Is there a MySQL equivalent to using mod_plsql as a gateway on Apache to talk directly to a stored procedure in the database?

There is no equivalent in MySQL 5.1.

30.4.17: Can I pass an array as input to a stored procedure?

Not in MySQL 5.1.

30.4.18: Can I pass a cursor as an IN parameter to a stored procedure?

In MySQL 5.1, cursors are available inside stored procedures only.

30.4.19: Can I return a cursor as an OUT parameter from a stored procedure?

In MySQL 5.1, cursors are available inside stored procedures only. However, if you do not open a cursor on a SELECT, the result will be sent directly to the client. You can also SELECT INTO variables. See Section 12.2.7, “SELECT Syntax”.

30.4.20: Can I print out a variable's value within a stored routine for debugging purposes?

Yes, you can do this in a stored procedure, but not in a stored function. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You will need to use the MySQL 4.1 (or above) client-server protocol for this to work. This means that — for instance — in PHP, you need to use the mysqli extension rather than the old mysql extension.

30.4.21: Can I commit or roll back transactions inside a stored procedure?

Yes. However, you cannot perform transactional operations within a stored function.

30.4.22: Do MySQL 5.1 stored procedures and functions work with replication?

Yes, standard actions carried out in stored procedures and functions are replicated from a master MySQL server to a slave server. There are a few limitations that are described in detail in Section 19.4, “Binary Logging of Stored Routines and Triggers”.

30.4.23: Are stored procedures and functions created on a master server replicated to a slave?

Yes, creation of stored procedures and functions carried out through normal DDL statements on a master server are replicated to a slave, so the objects will exist on both servers. ALTER and DROP statements for stored procedures and functions are also replicated.

30.4.24: How are actions that take place inside stored procedures and functions replicated?

MySQL records each DML event that occurs in a stored procedure and replicates those individual actions to a slave server. The actual calls made to execute stored procedures are not replicated.

Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function.

30.4.25: Are there special security requirements for using stored procedures and functions together with replication?

Yes. Because a slave server has authority to execute any statement read from a master's binary log, special security constraints exist for using stored functions with replication. If replication or binary logging in general (for the purpose of point-in-time recovery) is active, then MySQL DBAs have two security options open to them:

30.4.26: What limitations exist for replicating stored procedure and function actions?

Non-deterministic (random) or time-based actions embedded in stored procedures may not replicate properly. By their very nature, randomly produced results are not predictable and cannot be exactly reproduced, and therefore, random actions replicated to a slave will not mirror those performed on a master. Note that declaring stored functions to be DETERMINISTIC or setting the log_bin_trust_function_creators system variable to 0 will not allow random-valued operations to be invoked.

In addition, time-based actions cannot be reproduced on a slave because the timing of such actions in a stored procedure is not reproducible through the binary log used for replication. It records only DML events and does not factor in timing constraints.

Finally, non-transactional tables for which errors occur during large DML actions (such as bulk inserts) may experience replication issues in that a master may be partially updated from DML activity, but no updates are done to the slave because of the errors that occurred. A workaround is for a function's DML actions to be carried out with the IGNORE keyword so that updates on the master that cause errors are ignored and updates that do not cause errors are replicated to the slave.

30.4.27: Do the preceding limitations affect MySQL's ability to do point-in-time recovery?

The same limitations that affect replication do affect point-in-time recovery.

30.4.28: What is being done to correct the aforementioned limitations?

As of MySQL 5.1.5, you can choose either statement-based replication or row-based replication. The original replication implementation is based on statement-based binary logging. Row-based binary logging resolves the limitations mentioned earlier.

Beginning with MySQL 5.1.8, mixed replication is also available (by starting the server with --binlog-format=mixed). This hybrid, “smart” form of replication “knows” whether statement-level replication can safely be used, or row-level replication is required.

For additional information, see Section 15.1.2, “Replication Formats”.

Questions

Questions and Answers

30.5.1: Where can I find the documentation for MySQL 5.1 triggers?

See Chapter 20, Triggers.

30.5.2: Is there a discussion forum for MySQL Triggers?

Yes. It is available at http://forums.mysql.com/list.php?99.

30.5.3: Does MySQL 5.1 have statement-level or row-level triggers?

In MySQL 5.1, all triggers are FOR EACH ROW — that is, the trigger is activated for each row that is inserted, updated, or deleted. MySQL 5.1 does not support triggers using FOR EACH STATEMENT.

30.5.4: Are there any default triggers?

Not explicitly. MySQL does have specific special behavior for some TIMESTAMP columns, as well as for columns which are defined using AUTO_INCREMENT.

30.5.5: How are triggers managed in MySQL?

In MySQL 5.1, triggers can be created using the CREATE TRIGGER statement, and dropped using DROP TRIGGER. See Section 20.1, “CREATE TRIGGER Syntax”, and Section 20.2, “DROP TRIGGER Syntax”, for more about these statements.

Information about triggers can be obtained by querying the INFORMATION_SCHEMA.TRIGGERS table. See Section 23.16, “The INFORMATION_SCHEMA TRIGGERS Table”.

30.5.6: Is there a way to view all triggers in a given database?

Yes. You can obtain a listing of all triggers defined on database dbname using a query on the INFORMATION_SCHEMA.TRIGGERS table such as the one shown here:

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT 
    FROM INFORMATION_SCHEMA.TRIGGERS 
    WHERE TRIGGER_SCHEMA='dbname';

For more information about this table, see Section 23.16, “The INFORMATION_SCHEMA TRIGGERS Table”.

You can also use the SHOW TRIGGERS statement, which is specific to MySQL. See Section 12.5.4.30, “SHOW TRIGGERS Syntax”.

30.5.7: Where are triggers stored?

Triggers are currently stored in .TRG files, with one such file one per table. In other words, a trigger belongs to a table.

In the future, we plan to change this so that trigger information will be included in the .FRM file that defines the structure of the table. We also plan to make triggers database-level objects — rather than table-level objects as they are now — to bring them into compliance with the SQL standard.

30.5.8: Can a trigger call a stored procedure?

Yes.

30.5.9: Can triggers access tables?

A trigger can access both old and new data in its own table. Through a stored procedure, or a multiple-table update or delete statement, a trigger can also affect other tables.

30.5.10: Can triggers call an external application through a UDF?

No, not at present.

30.5.11: Is possible for a trigger to update tables on a remote server?

Yes. A table on a remote server could be updated using the FEDERATED storage engine. (See Section 13.9, “The FEDERATED Storage Engine”).

30.5.12: Do triggers work with replication?

Triggers and replication in MySQL 5.1 work in the same wasy as in most other database engines: Actions carried out through triggers on a master are not replicated to a slave server. Instead, triggers that exist on tables that reside on a MySQL master server need to be created on the corresponding tables on any MySQL slave servers so that the triggers activate on the slaves as well as the master.

30.5.13: How are actions carried out through triggers on a master replicated to a slave?

First, the triggers that exist on a master must be re-created on the slave server. Once this is done, the replication flow works as any other standard DML statement that participates in replication. For example, consider a table EMP that has an AFTER insert trigger, which exists on a master MySQL server. The same EMP table and AFTER insert trigger exist on the slave server as well. The replication flow would be:

Questions

Questions and Answers

30.6.1: Where can I find documentation covering MySQL Views?

See Chapter 22, Views.

30.6.2: Is there a discussion forum for MySQL Views?

Yes. See http://forums.mysql.com/list.php?100

30.6.3: What happens to a view if an underlying table is dropped or renamed?

After a view has been created, it is possible to drop or alter a table or view to which the definition refers. To check a view definition for problems of this kind, use the CHECK TABLE statement. (See Section 12.5.2.3, “CHECK TABLE Syntax”.)

30.6.4: Does MySQL 5.1 have table snapshots?

No.

30.6.5: Does MySQL 5.1 have materialized views?

No.

30.6.6: Can you insert into views that are based on joins?

It is possible, provided that your INSERT statement has a column list that makes it clear there's only one table involved.

You cannot insert into multiple tables with a single insert on a view.

Questions

Questions and Answers

30.7.1: Where can I find documentation for the MySQL INFORMATION_SCHEMA database?

See Chapter 23, The INFORMATION_SCHEMA Database

30.7.2: Is there a discussion forum for INFORMATION_SCHEMA?

See http://forums.mysql.com/list.php?101.

30.7.3: Where can I find the ANSI SQL 2003 specification for INFORMATION_SCHEMA?

Unfortunately, the official specifications are not freely available. (ANSI makes them available for purchase.) However, there are books available — such as SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer — which give a comprehensive overview of the standard, including INFORMATION_SCHEMA.

30.7.4: What is the difference between the Oracle Data Dictionary and MySQL's INFORMATION_SCHEMA?

Both Oracle and MySQL provide metadata in tables. However, Oracle and MySQL use different table names and column names. MySQL's implementation is more similar to those found in DB2 and SQL Server, which also support INFORMATION_SCHEMA as defined in the SQL standard.

30.7.5: Can I add to or otherwise modify the tables found in the INFORMATION_SCHEMA database?

No. Since applications may rely on a certain standard structure, this should not be modified. For this reason, MySQL AB cannot support bugs or other issues which result from modifying INFORMATION_SCHEMA tables or data.

Questions

Questions and Answers

30.8.1: Where can I find information on how to migrate from MySQL 5.0 to MySQL 5.1?

For detailed upgrade information, see Section 2.11, “Upgrading MySQL”. We recommend that you do not skip a major version when upgrading, but rather complete the process in steps, upgrading from one major version to the next in each step. This may seem more complicated, but it will you save time and trouble — if you encounter problems during the upgrade, their origin will be easier to identify, either by you or — if you have a MySQL Network subscription — by MySQL support.

30.8.2: How has storage engine (table type) support changed in MySQL 5.1 from previous versions?

Storage engine support has changed as follows:

Questions

Questions and Answers

30.9.1: Where can I find documentation that addresses security issues for MySQL?

The best place to start is Section 5.6, “General Security Issues”.

Other portions of the MySQL Documentation which you may find useful with regard to specific security concerns include the following:

MySQL Enterprise The MySQL Enterprise Monitor enforces best practices for maximizing the security of your servers. For more information see http://www.mysql.com/products/enterprise/advisors.html.

30.9.2: Does MySQL 5.1 have native support for SSL?

Most 5.1 binaries have support for SSL connections between the client and server. We can't currently build with the new YaSSL library everywhere, as it's still quite new and does not compile on all platforms yet. See Section 5.8.7, “Using Secure Connections”.

You can also tunnel a connection via SSH, if (for instance) if the client application doesn't support SSL connections. For an example, see Section 5.8.7.5, “Connecting to MySQL Remotely from Windows with SSH”.

30.9.3: Is SSL support be built into MySQL binaries, or must I recompile the binary myself to enable it?

Most 5.1 binaries have SSL enabled for client-server connections that are secured, authenticated, or both. However, the YaSSL library currently does not compile on all platforms. See Section 5.8.7, “Using Secure Connections”, for a complete listing of supported and unsupported platforms.

30.9.4: Does MySQL 5.1 have built-in authentication against LDAP directories?

No. Support for external authentication methods is on the MySQL roadmap as a “rolling feature”, which means that we plan to implement it in the future, but we have not yet determined when this will be done.

30.9.5: Does MySQL 5.1 include support for Roles Based Access Control (RBAC)?

No. Support for roles is on the MySQL roadmap as a “rolling feature”, which means that we plan to implement it in the future, but we have not yet determined when this will be done.

In the following section, we provide answers to questions that are most frequently asked about MySQL Cluster and the NDB storage engine.

Questions

Questions and Answers

30.10.1: What does “NDB” mean?

This stands for “Network Database”. NDB (also known as NDB Cluster or NDBCLUSTER) is the storage engine that enables clustering in MySQL.

30.10.2: What's the difference in using Cluster vs using replication?

In a replication setup, a master MySQL server updates one or more slaves. Transactions are committed sequentially, and a slow transaction can cause the slave to lag behind the master. This means that if the master fails, it is possible that the slave might not have recorded the last few transactions. If a transaction-safe engine such as InnoDB is being used, a transaction will either be complete on the slave or not applied at all, but replication does not guarantee that all data on the master and the slave will be consistent at all times. In MySQL Cluster, all data nodes are kept in synchrony, and a transaction committed by any one data node is committed for all data nodes. In the event of a data node failure, all remaining data nodes remain in a consistent state.

In short, whereas standard MySQL replication is asynchronous, MySQL Cluster is synchronous.

We have implemented (asynchronous) replication for Cluster in MySQL 5.1. This includes the capability to replicate both between two clusters, and from a MySQL cluster to a non-Cluster MySQL server. See Section 16.10, “MySQL Cluster Replication”.

30.10.3: Do I need to do any special networking to run Cluster? How do computers in a cluster communicate?

MySQL Cluster is intended to be used in a high-bandwidth environment, with computers connecting via TCP/IP. Its performance depends directly upon the connection speed between the cluster's computers. The minimum connectivity requirements for Cluster include a typical 100-megabit Ethernet network or the equivalent. We recommend you use gigabit Ethernet whenever available.

The faster SCI protocol is also supported, but requires special hardware. See Section 16.12, “Using High-Speed Interconnects with MySQL Cluster”, for more information about SCI.

30.10.4: How many computers do I need to run a cluster, and why?

A minimum of three computers is required to run a viable cluster. However, the minimum recommended number of computers in a MySQL Cluster is four: one each to run the management and SQL nodes, and two computers to serve as data nodes. The purpose of the two data nodes is to provide redundancy; the management node must run on a separate machine to guarantee continued arbitration services in the event that one of the data nodes fails.

To provide increased throughput and high availability, you should use multiple SQL nodes (MySQL Servers connected to the cluster). It is also possible (although not strictly necessary) to run multiple management servers.

30.10.5: What do the different computers do in a MySQL Cluster?

A MySQL Cluster has both a physical and logical organization, with computers being the physical elements. The logical or functional elements of a cluster are referred to as nodes, and a computer housing a cluster node is sometimes referred to as a cluster host. There are three types of nodes, each corresponding to a specific role within the cluster. These are:

30.10.6: With which operating systems can I use Cluster?

MySQL Cluster is supported on most Unix-like operating systems, including Linux, Mac OS X, Solaris, BSD, HP-UX, AIX, and IRIX, among others, as well as Novell Netware. Cluster is not supported for Windows at this time. However, we are working to add Cluster support for other platforms, including Windows, and our goal is to offer MySQL Cluster on all platforms for which MySQL itself is supported.

For more detailed information concerning the level of support which is offered for MySQL Cluster on various operating system versions, OS distributions, and hardware platforms, please refer to http://www.mysql.com/support/supportedplatforms/cluster.html.

30.10.7: What are the hardware requirements for running MySQL Cluster?

Cluster should run on any platform for which NDB-enabled binaries are available. Naturally, faster CPUs and more memory will improve performance, and 64-bit CPUs will likely be more effective than 32-bit processors. There must be sufficient memory on machines used for data nodes to hold each node's share of the database (see How much RAM do I Need? for more information). Nodes can communicate via a standard TCP/IP network and hardware. For SCI support, special networking hardware is required.

30.10.8: How much RAM do I need? Is it possible to use disk memory at all?

Previous to MySQL 5.1, Cluster was in-memory only. This meant that all table data (including indexes) was stored in RAM. If your data took up 1GB of space and you wanted to replicate it once in the cluster, you needed 2GB of memory to do so (1 GB per replica). This was in addition to the memory required by the operating system and any applications running on the cluster computers. This is still true of in-memory tables.

If a data node's memory usage exceeds what is available in RAM, then the system will attempt to use swap space up to the limit set for DataMemory. However, this will at best result in severely degraded performance, and may cause the node to be dropped due to slow response time (missed heartbeats). We do not recommend on relying on disk swapping in a production environment for this reason. In any case, once the DataMemory limit is reached, any operations requiring additional memory (such as inserts) will fail.

NDB Cluster in MySQL 5.1 includes support for Disk Data, which helps to alleviate these issues. See Section 16.11, “MySQL Cluster Disk Data Tables”, for more information.

You can use the following formula for obtaining a rough estimate of how much RAM is needed for each data node in the cluster:

(SizeofDatabase × NumberOfReplicas × 1.1 ) / NumberOfDataNodes

To calculate the memory requirements more exactly requires determining, for each table in the cluster database, the storage space required per row (see Section 10.5, “Data Type Storage Requirements”, for details), and multiplying this by the number of rows. You must also remember to account for any column indexes as follows:

Note that creating MySQL Cluster tables with USING HASH for all primary keys and unique indexes will generally cause table updates to run more quickly — in some cases by a much as 20 to 30 percent faster than updates on tables where USING HASH was not used in creating primary and unique keys. This is due to the fact that less memory is required (because no ordered indexes are created), and that less CPU must be utilized (because fewer indexes must be read and possibly updated). However, it also means that queries that could otherwise use range scans must be satisfied by other means, which can result in slower selects.

When calculating Cluster memory requirements, you may find useful the ndb_size.pl utility which is available in recent MySQL 5.1 releases. This Perl script connects to a current (non-Cluster) MySQL database and creates a report on how much space that database would require if it used the NDBCluster storage engine. For more information, see Section 16.9.15, “ndb_size.pl — NDBCluster Size Requirement Estimator”.

It is especially important to keep in mind that every MySQL Cluster table must have a primary key. The NDB storage engine creates a primary key automatically if none is defined, and this primary key is created without USING HASH.

There is no easy way to determine exactly how much memory is being used for storage of Cluster indexes at any given time; however, warnings are written to the Cluster log when 80% of available DataMemory or IndexMemory is in use, and again when use reaches 85%, 90%, and so on.

30.10.9: What filesystems can I use with MySQL Cluster? What about network filesystems or network shares?

Generally, any filesystem that is native to the host operating system should work well with MySQL Cluster. If you find that a given filesystem works particularly well (or not so especially well) with MySQL Cluster, we invite you to discuss your findings in the MySQL Cluster Forums.

We do not test MySQL Cluster with FAT or VFAT filesystems on Linux. Because of this, and due to the fact that these are not very useful for any purpose other than sharing disk partitions between Linux and Windows operating systems on multi-boot computers, we do not recommend their use with MySQL Cluster.

MySQL Cluster is implemented as a shared-nothing solution; the idea behind this is that the failure of a single piece of hardware should not cause the failure of multiple cluster nodes, or possibly even the failure of the cluster as a whole. For this reason, the use of network shares or network filesystems is not supported for MySQL Cluster. This also applies to shared storage devices such as SANs.

30.10.10: Can I run MySQL Cluster nodes inside virtual machines (such as those created by VMWare, Parallels, or Xen)?

This is possible but not recommended for a production environment.

We have found that running MySQL Cluster processes inside a virtual machine can give rise to issues with timing and disk subsystems that have a strong negative impact on the operation of the cluster. The behavior of the cluster is often unpredictable in these cases.

If the issue can be reproduced outside the virtual environment, then we may be able to provide assistance. Otherwise, we cannot support it at this time.

30.10.11: I'm trying to populate a Cluster database. The loading process terminates prematurely and I get an error message like this one:

ERROR 1114: The table 'my_cluster_table' is full

The cause is very likely to be that your setup does not provide sufficient RAM for all table data and all indexes, including the primary key required by the NDB storage engine and automatically created in the event that the table definition does not include the definition of a primary key.

It is also worth noting that all data nodes should have the same amount of RAM, since no data node in a cluster can use more memory than the least amount available to any individual data node. In other words, if there are four computers hosting Cluster data nodes, and three of these have 3GB of RAM available to store Cluster data while the remaining data node has only 1GB RAM, then each data node can devote only 1GB to clustering.

30.10.12: MySQL Cluster uses TCP/IP. Does this mean that I can run it over the Internet, with one or more nodes in remote locations?

It is very unlikely that a cluster would perform reliably under such conditions, as MySQL Cluster was designed and implemented with the assumption that it would be run under conditions guaranteeing dedicated high-speed connectivity such as that found in a LAN setting using 100 Mbps or gigabit Ethernet — preferably the latter. We neither test nor warrant its performance using anything slower than this.

Also, it is extremely important to keep in mind that communications between the nodes in a MySQL Cluster are not secure; they are neither encrypted nor safeguarded by any other protective mechanism. The most secure configuration for a cluster is in a private network behind a firewall, with no direct access to any Cluster data or management nodes from outside. (For SQL nodes, you should take the same precautions as you would with any other instance of the MySQL server.)

30.10.13: Do I have to learn a new programming or query language to use MySQL Cluster?

No. Although some specialized commands are used to manage and configure the cluster itself, only standard (My)SQL queries and commands are required for the following operations:

Some specialized configuration parameters and files are required to set up a MySQL Cluster — see Section 16.4.4, “Configuration File”, for information about these.

A few simple commands are used in the MySQL Cluster management client for tasks such as starting and stopping cluster nodes. See Section 16.7.2, “Commands in the MySQL Cluster Management Client”.

30.10.14: How do I find out what an error or warning message means when using MySQL Cluster?

There are two ways in which this can be done:

30.10.15: Is MySQL Cluster transaction-safe? What isolation levels are supported?

Yes: For tables created with the NDB storage engine, transactions are supported. In MySQL 5.1, Cluster supports only the READ COMMITTED transaction isolation level.

30.10.16: What storage engines are supported by MySQL Cluster?

Clustering in MySQL is supported only by the NDB storage engine. That is, in order for a table to be shared between nodes in a cluster, it must be created using ENGINE=NDB (or ENGINE=NDBCLUSTER, which is equivalent).

It is possible to create tables using other storage engines (such as MyISAM or InnoDB) on a MySQL server being used for clustering, but these non-NDB tables will not participate in the cluster; they are local to the individual MySQL server instance on which they are created.

30.10.17: Which versions of the MySQL software support Cluster? Do I have to compile from source?

Cluster is supported in all server binaries in the 5.1 release series for operating systems on which MySQL Cluster is available. See Section 5.2, “mysqld — The MySQL Server”. You can determine whether your server has NDB support using either the SHOW VARIABLES LIKE 'have_%' or SHOW ENGINES statement.

You can also obtain NDB support by compiling MySQL from source, but it is not necessary to do so simply to use MySQL Cluster. To download the latest binary, RPM, or source distribution in the MySQL 5.1 series, visit http://dev.mysql.com/downloads/mysql/5.1.html.

30.10.18: In the event of a catastrophic failure — say, for instance, the whole city loses power and my UPS fails — would I lose all my data?

All committed transactions are logged. Therefore, although it is possible that some data could be lost in the event of a catastrophe, this should be quite limited. Data loss can be further reduced by minimizing the number of operations per transaction. (It is not a good idea to perform large numbers of operations per transaction in any case.)

30.10.19: Is it possible to use FULLTEXT indexes with Cluster?

FULLTEXT indexing is not supported by the NDB storage engine in MySQL 5.1, or by any storage engine other than MyISAM. We are working to add this capability in a future release.

30.10.20: Can I run multiple nodes on a single computer?

It is possible but not advisable. One of the chief reasons to run a cluster is to provide redundancy. To enjoy the full benefits of this redundancy, each node should reside on a separate machine. If you place multiple nodes on a single machine and that machine fails, you lose all of those nodes. Given that MySQL Cluster can be run on commodity hardware loaded with a low-cost (or even no-cost) operating system, the expense of an extra machine or two is well worth it to safeguard mission-critical data. It also worth noting that the requirements for a cluster host running a management node are minimal. This task can be accomplished with a 200 MHz Pentium CPU and sufficient RAM for the operating system plus a small amount of overhead for the ndb_mgmd and ndb_mgm processes.

It is acceptable to run multiple cluster data nodes on a single host for learning about MySQL Cluster, or for testing purposes; however, this is not supported for production use.

30.10.21: Can I add nodes to a cluster without restarting it?

Not at present. A simple restart is all that is required for adding new MGM or SQL nodes to a Cluster. When adding data nodes the process is more complex, and requires the following steps:

In a future MySQL Cluster release series, we hope to implement a “hot” reconfiguration capability for MySQL Cluster to minimize (if not eliminate) the requirement for restarting the cluster when adding new nodes. However, this is not planned for MySQL 5.1.

30.10.22: Are there any limitations that I should be aware of when using MySQL Cluster?

Limitations on NDB tables in MySQL 5.1 include:

For a complete listing of limitations in MySQL Cluster, see Section 16.13, “Known Limitations of MySQL Cluster”.

30.10.23: How do I import an existing MySQL database into a cluster?

You can import databases into MySQL Cluster much as you would with any other version of MySQL. Other than the limitations mentioned elsewhere in this FAQ and in Section 16.13, “Known Limitations of MySQL Cluster”, the only other special requirement is that any tables to be included in the cluster must use the NDB storage engine. This means that the tables must be created with ENGINE=NDB or ENGINE=NDBCLUSTER.

It is also possible to convert existing tables using other storage engines to NDB Cluster using one or more ALTER TABLE statement, but this requires an additional workaround. See Section 16.13, “Known Limitations of MySQL Cluster”, for details.

30.10.24: How do cluster nodes communicate with one another?

Cluster nodes can communicate via any of three different protocols: TCP/IP, SHM (shared memory), and SCI (Scalable Coherent Interface). Where available, SHM is used by default between nodes residing on the same cluster host; however, this is considered experimental in MySQL 5.1. SCI is a high-speed (1 gigabit per second and higher), high-availability protocol used in building scalable multi-processor systems; it requires special hardware and drivers. See Section 16.12, “Using High-Speed Interconnects with MySQL Cluster”, for more about using SCI as a transport mechanism in MySQL Cluster.

30.10.25: What is an arbitrator?

If one or more nodes in a cluster fail, it is possible that not all cluster nodes will be able to “see” one another. In fact, it is possible that two sets of nodes might become isolated from one another in a network partitioning, also known as a “split brain” scenario. This type of situation is undesirable because each set of nodes tries to behave as though it is the entire cluster.

When cluster nodes go down, there are two possibilities. If more than 50% of the remaining nodes can communicate with each other, we have what is sometimes called a “majority rules” situation, and this set of nodes is considered to be the cluster. The arbitrator comes into play when there is an even number of nodes: in such cases, the set of nodes to which the arbitrator belongs is considered to be the cluster, and nodes not belonging to this set are shut down.

The preceding information is somewhat simplified. A more complete explanation taking into account node groups follows:

When all nodes in at least one node group are alive, network partitioning is not an issue, because no one portion of the cluster can form a functional cluster. The real problem arises when no single node group has all its nodes alive, in which case network partitioning (the “split-brain” scenario) becomes possible. Then an arbitrator is required. All cluster nodes recognize the same node as the arbitrator, which is normally the management server; however, it is possible to configure any of the MySQL Servers in the cluster to act as the arbitrator instead. The arbitrator accepts the first set of cluster nodes to contact it, and tells the remaining set to shut down. Arbitrator selection is controlled by the ArbitrationRank configuration parameter for MySQL Server and management server nodes. (See Section 16.4.4.4, “Defining the Management Server”, for details.) It should also be noted that the role of arbitrator does not in and of itself impose any heavy demands upon the host so designated, and thus the arbitrator host does not need to be particularly fast or to have extra memory especially for this purpose.

30.10.26: What data types are supported by MySQL Cluster?

MySQL Cluster supports all of the usual MySQL data types, with the exception of those associated with MySQL's spatial extensions. (See Chapter 18, Spatial Extensions.) In addition, there are some differences with regard to indexes when used with NDB tables. Note: MySQL Cluster Disk Data tables (that is, tables created with TABLESPACE ... STORAGE DISK ENGINE=NDBCLUSTER) have only fixed-width rows. This means that (for example) each Disk Data table record containing a VARCHAR(255) column requires space for 255 characters (as required for the character set and collation being used for the table), regardless of the actual number of characters stored therein.

See Section 16.13, “Known Limitations of MySQL Cluster”, for more information about these issues.

30.10.27: Ho