Summary (aka TL/DR):

Support for Foreign Key constraints has been one of the most requested feature enhancements for MySQL Cluster. We are therefore extremely excited to announce that Foreign Keys are part of the first Labs Release of MySQL Cluster 7.3 – available for download, evaluation and feedback now! (Select the mysql-cluster-7.3-labs-June-2012 build)

In this blog, I will attempt to discuss the design rationale, implementation, configuration and steps to get started in evaluating the first MySQL Cluster 7.3 Labs Release.

Pace of Innovation

It was only a couple of months ago that we announced the General Availability (GA) of MySQL Cluster 7.2, delivering 1 billion Queries per Minute, with 70x higher cross-shard JOIN performance, Memcached NoSQL key-value API and cross-data center replication.  This release has been a huge hit, with downloads and deployments quickly reaching record levels.

The announcement of the first MySQL Cluster 7.3 Early Access lab release at today's MySQL Innovation Day event demonstrates the continued pace in Cluster development, and provides an opportunity for the community to evaluate and feedback on new features they want to see.

What’s the Plan for MySQL Cluster 7.3?

Well, Foreign Keys, as you may have gathered by now (!), and this is the focus of this first Labs Release.

As with MySQL Cluster 7.2, we plan to publish a series of preview releases for 7.3 that will incrementally add new candidate features for a final GA release (subject to usual safe harbor statement below*), including:

- New NoSQL APIs;

- Features to automate the configuration and provisioning of multi-node clusters, on premise or in the cloud;

- Performance and scalability enhancements;

- Taking advantage of features in the latest MySQL 5.x Server GA.

Design Rationale

MySQL Cluster is designed as a “Not-Only-SQL” database. It combines attributes that enable users to blend the best of both relational and NoSQL technologies into solutions that deliver web scalability with 99.999% availability and real-time performance, including:

• Concurrent NoSQL and SQL access to the database;
• Auto-sharding with simple scale-out across commodity hardware;
• Multi-master replication with failover and recovery both within and across data centers;
• Shared-nothing architecture with no single point of failure;
• Online scaling and schema changes;
• ACID compliance and support for complex queries, across shards.

Native support for Foreign Key constraints enables users to extend the benefits of MySQL Cluster into a broader range of use-cases, including:

- Packaged applications in areas such as eCommerce and Web Content Management that prescribe databases with Foreign Key support.

- In-house developments benefiting from Foreign Key constraints to simplify data models and eliminate the additional application logic needed to maintain data consistency and integrity between tables.

Implementation

The Foreign Key functionality is implemented directly within MySQL Cluster’s data nodes, allowing any client API accessing the cluster to benefit from them – whether using SQL or one of the NoSQL interfaces (Memcached, C++, Java, JPA or HTTP/REST.)

The core referential actions defined in the SQL:2003 standard are implemented:

• CASCADE
• RESTRICT
• NO ACTION
• SET NULL

In addition, the MySQL Cluster implementation supports the online adding and dropping of Foreign Keys, ensuring the Cluster continues to serve both read and write requests during the operation.

An important difference to note with the Foreign Key implementation in InnoDB is that MySQL Cluster does not support the updating of Primary Keys from within the Data Nodes themselves - instead the UPDATE is emulated with a DELETE followed by an INSERT operation. Therefore an UPDATE operation will return an error if the parent reference is using a Primary Key, unless using CASCADE action, in which case the delete operation will result in the corresponding rows in the child table being deleted. The Engineering team plans to change this behavior in a subsequent preview release.

Also note that when using InnoDB "NO ACTION" is identical to "RESTRICT". In the case of MySQL Cluster “NO ACTION” means “deferred check”, i.e. the constraint is checked before commit, allowing user-defined triggers to automatically make changes in order to satisfy the Foreign Key constraints.

Configuration

There is nothing special you have to do here – Foreign Key constraint checking is enabled by default.

If you intend to migrate existing tables from another database or storage engine, for example from InnoDB, there are a couple of best practices to observe:

1. Analyze the structure of the Foreign Key graph and run the ALTER TABLE ENGINE=NDB in the correct sequence to ensure constraints are enforced

2. Alternatively drop the Foreign Key constraints prior to the import process and then recreate when complete.

Getting Started

Read this blog for a demonstration of using Foreign Keys with MySQL Cluster. 

You can download MySQL Cluster 7.3 Labs Release with Foreign Keys today - (select the mysql-cluster-7.3-labs-June-2012 build)

If you are new to MySQL Cluster, the Getting Started guide will walk you through installing an evaluation cluster on a singe host (these guides reflect MySQL Cluster 7.2, but apply equally well to 7.3)

Post any questions to the MySQL Cluster forum where our Engineering team will attempt to assist you.

Post any bugs you find to the MySQL bug tracking system (select MySQL Cluster from the Category drop-down menu)

And if you have any feedback, please post them to the Comments section of this blog.

Summary

MySQL Cluster 7.2 is the GA, production-ready release of MySQL Cluster. This first Labs Release of MySQL Cluster 7.3 gives you the opportunity to preview and evaluate future developments in the MySQL Cluster database, and we are very excited to be able to share that with you.

Let us know how you get along with MySQL Cluster 7.3, and other features that you want to see in future releases.

* Safe Harbor Statement

This information is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.