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.
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.
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;
replication with failover and recovery both within and across data centers;
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.
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
- 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
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
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.
Read this blog for a demonstration of using Foreign Keys with MySQL
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
And if you have any feedback, please post them to the Comments section of
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
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
development, release, and timing of any features or functionality
described for Oracle’s products remains at the sole
discretion of Oracle.