Foreign Keys in MySQL Cluster

Foreign Key constraints between tables

The newly announced GA of MySQL Cluster 7.3 (7.3.2) builds upon second DMR (7.3.1 m2) released earlier in the year which added Foreign Keys to MySQL Cluster. Foreign Keys is a feature requested by many, many people and has often been cited as the reason for not being able to replace InnoDB with MySQL Cluster when they needed the extra availability or scalability.

Note that this post is an up-version of the original – and was first published with the 7.3 labs release in June 2012.

What’s a Foreign Key

The majority of readers who are already familiar with Foreign Keys can skip to the next section.

Foreign Keys are a way of implementing relationships/constraints between columns in different tables. For example, in the above figure, we want to make sure that the value of the county column in the towns table has an associated entry in the counties table. In this way, no-one can place a town in a non-existent county and similarly no one can remove a county and leave orphaned towns behind.

We refer to the towns table as the child and the counties table as the parent.

There are different categories of constraints that influence how they’re enforced when a row is updated or deleted from the parent table:

Cascade: If a row is deleted from the parent then any rows in the child table with a matching FK value will also be deleted. Similarly for changes to the value in the parent table.

Restrict: A row cannot be deleted from the parent table if this would break a FK constraint with the child table. Similarly for changes to the value in the parent table.

No Action: Very similar to “Restrict” except that any events/triggers on the parent table will be executed before the constraint is enforced – giving the application writer the option to resolve any FK constraint conflicts using a stored procedure.

Set NULL: If NULL is a permitted value for the FK column in the child table then it will be set to NULL if the associated data in the parent table is updated or deleted.

Set Default: If there is a default value for the FK column in the child table then it will be used if the associated data in the parent table is updated or deleted. Note that this is not implemented in this version – the constraint can be added to the schema but any subsequent deletion or update to the column in the parent table will fail.

How to use Foreign Keys with MySQL Cluster

For anyone familiar with using FKs with InnoDB, a lot of this will be very similar – that’s intentional in the design of this Cluster feature. Throughout this section we will use the tables shown in the above figure.

Note that the FK is actually defined as part of creating the towns table with CONSTRAINT county_town FOREIGN KEY (county) REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE RESTRICT. county_town is the label assigned to the constraint (if you don’t specify one then one will be automatically created – you can see it by executing SHOW CREATE TABLE towns). The constraint is set up from the county column in the towns (child) table to the county column in the counties (parent) table.

As a first check, we can confirm that values that are not part of the constraint can be modified in the parent table – in this case we’ll move the England/Wales border:

It is also possible to delete FK constraints and add new ones, we do that next and at the same time we’ll switch to a CASCADE restriction. Note that adding and deleting FKs are on-line operations. In case you have forgotten the name of the constraint (or we had not explicitly named it) we can use the SHOW CREATE TABLE command to remind us:

Note that MySQL Cluster implements FKs in the data nodes and the constraints will be enforced regardless of how the data is written (through SQL, javascript, Memcached, ClusterJ, REST or directly through the NDB API). Before illustrating this, we add back the data we have just deleted:

Comparison with InnoDB’s Foreign Keys

As mentioned, we’ve attempted to mirror the way that InnoDB works as much as possible in the way that it implements Foreign Keys as that’s what we think will allow people to move more easily to MySQL Cluster when appropriate. There are a couple of exceptions though:

InnoDB doesn’t support “No Action” constraints, MySQL Cluster does

You cannot set up FKs between 2 tables where one is stored using MySQL Cluster and the other InnoDB.

You cannot change primary keys through the NDB API which means that the MySQL Server actually has to simulate such operations by deleting and re-adding the row. If the PK in the parent table has a FK constraint on it then this causes non-ideal behaviour. With Restrict or No Action constraints, the change will result in an error. With Cascaded constraints, you’d want the rows in the child table to be updated with the new FK value but, the implicit delete of the row from the parent table would remove the associated rows from the child table and the subsequent implicit insert into the parent wouldn’t reinstate the child rows. For this reason, an attempt to add an ON UPDATE CASCADE where the parent column is a primary key will be rejected.

MySQL Workbench

Viewing and updating Foreign Key Constraints in MySQL Workbench

While not really the subject of this post, MySQL Workbench is a great tool for database developers and one of the features is the creation, modification and reverse engineering of schemas. For example, you could import your existing schema, add a new table and then introduce a FK constraint between an existing table and your new one. Previously, WorkBench would only allow you to play with FKs for InnoDB tables.

Closing remarks

We believe that this is going to enable a whole new set of applications exploit the advantages of MySQL Cluster (on-line scalability, real-time performance, NoSQL APIs, real-time performance, on-line schema changes,…) either because:

they want to simplify their application by pushing these referential checks down into the database – letting the developer focus more of their time on extending the application;

because they’re stuck with 3rd party middleware that is dependent on FKs

or that the application is already so dependent on FKs that it would be too much pain to remove them.

If you are new to MySQL Cluster then here are some useful resources to get you started:

We are evaluating mysql cluster and started with 7.2 version and moved onto 7.3 test version. 7.3 looks quite stable, and the foreigh keys are created and indexed. At times when memory limit is approached on all data nodes, the all data nodes in the cluster gets forcefully shutdown. Is this the way it behaves? I will try to post an error message next time it happens.

Also we didn’t notice any significant improvement w.r.t querying. The performance improvement of the same query in 7.2 and 7.3 is negligible. How much of a performance improvement are you expecting. Or is there any more configurations which we have to do?

We figured out most of the errors was due to the lack of enough RAM in our systems. When we have increased them, we don’t see it anymore. Previously using an 8GB RAM, we have configured 5.8GB for Data memory and 500 M for Index Memory. Whenever data used to touch more than 20 million rows, one of them will shut down.

all report memory showed around 95% of usage. What is the best amount of usage in this case? Should we try adding more memory when it touches, say around 70%? Would the querying be slower if memory usage is more?

If you’re using in-memory tables then performance shouldn’t be impacted by running low on RAM as all of the data is in memory anyway (though you may want to set lockpagesinmainmemory to prevent things getting swapped to disk). Having said that, it’s always good to have a bit of spare headroom!

Just when I said all is well after increasing the RAM, I got the error again. We had 2 data nodes and I was doinng a rolling restart of a node after reducing the Datamemory from 12.5 G to 6 G, I came across this error:

We are facing this issue most of the times when we restart a node after adding or reducing DataMemory

The new MySQL Cluster 7.3 test version looks very promising. However after inserting a few thousand rows and then trying to delete part of the data it was causing data nodes to terminate.

There was also a strange output from “SHOW CREATE TABLE” if an FK constraint is applied against another table, it shows against the current table. SHOW CREATE TABLE config …. CONSTRAINT `configid` FOREIGN KEY `configid` references config (configid) on delete no action on update no action. Referencing itself.

Would it be possible to get a newer release of 7.3? As it’s been around three months and I’m super excited to use the new FK features which 7.3 will offer. MySQL Cluster 7.3 would be a perfect fit, so I’m very keen.

A couple of comments – the first is that yes, you can expect further versions of MySQL Cluster 7.3. The second would be that if you think you’ve found a bug then we’d really appreciate it if you could create a bug at http://bugs.mysql.com/ and provide as much information as possible on how we can recreate the issue.

You don’t show the full schema but I imagine that you’re hitting the main difference between FKs with MySQL Cluster vs. InnoDB – namely that the FK constraint isn’t allowed to update the value of a Primary Key. Is this the case?

Dear Andrew!
Thanks for your answer.
For the first case I think problem is that FK constraint is not allowed to update Primary Key value but What about my second case?
Here my table schema and FK that generate error: #150 – Cannot add foreign key constraint

thanks for pointing this out – I’ve just recreated it. This appears to be a constraint that’s been inherited from InnoDB (we aim to match InnoDB behaviour but what your attempting to do makes a lot of sense for Cluster tables). I’m looking into why the restriction is needed (suspecting that it shouldn’t be needed for Cluster) and whether we can remove it for NDB tables.

[…] MySQL Cluster and NoSQL I mentioned the new DMR 7.3.1 for MySQL Cluster. Our efforts to improve ease of use for Cluster continues, please learn more aboutm, and try out the autoinstaller. MySQL Cluster was there as a “NoSQL” database long before the term was coined, and was just awarded the “storage engine of the year“. This DMR also contains support for Foreign Keys which I know some of you have been waiting for. I’ll encourage the cluster engineers to write an in depth blog about the complexities in supporting foreign keys in a distributed databases. Meanwhile please learn more here. […]

Has anyone run into problems with crossing NDB and INNODB tables Parent/Child FK issues in 7.3.1 m2? Looks like FK is only valid between NDB to NDB tables and cannot be mixed ? Can someone verify this? I assume this is not supported ? I get

I am having similar problem with constraints for ndb. I am porting an appication from innodb to ndb. Few of the constraints dont get added on ndb. I have noticed that constraint to a column that’s part of a composite index doesnt work on ndb. Is there a restriction or is it a bug? Can you please point me to the documentation as I can’t see anything relevant.

you’ve hit a use case where InnoDB deviates from/goes beyond the SQL standard in allowing the referenced key to a non-unique key (MySQL Cluster doesn’t). Note that by referencing just part of the multi-part key it’s actually non-unique even if the full key was unique.

If this isn’t clear in the documentation then please raise a bug report (we aim for Cluster to mirror InnoDB FK behaviour and so any deviations should be documented).

Has cluster ndb 7.4.6 totally support “referencing just part of the multi-part key ” as a foreign key?

“…Note that by referencing just part of the multi-part key it’s actually non-unique even if the full key was unique …” does this means that in cluster NDB engine, the reference foreign key should be only one UNIQUE key, but cann’t be a part of multi-coloumn key?

But it is strange that some table constraint with a part of multi-coloumn key can create in NDBCluster, but some tables doesn’t.

Thanks for the well-written article. As a systems engineer supporting a dev team, I don’t often get into the weeds of this stuff and you helped me wrap my mind around some foreign key issues in NDB.

We have been developing a new application around a MySQL Cluster data engine and have been quite impressed with the solution thus far, but I’ve run into a number of issues when trying to add / modify foreign key constraints. For example, I have two tables:

I was able to recreate the issue – if you issue SHOW WARNINGS then you get a clue – the problem is that ‘code’ isn’t a unique key and so it breaks one of the limitations of FKs for NDB tables. I’ve raised bug http://bugs.mysql.com/70696 to get it documented. If you change the table definition to the following then you can add the constraint…

we don’t support ALTER TABLE on a table that already has FKs defined – try deleting the FK constraint, perform the ALTER TABLE and then add them back. Note that FK constraints are not allowed to change the PK on the parent table.

This is a relatively resource – heavy config (I’m thinking of memory usage) – are your sure that there is enough physical memory on the physical server and the VM to ensure that none of the Cluster’s data will be swapped to disk? If not then performance will be very poor (note that all of the DataMemory is allocated when the data node starts up rather than it being incrementally allocated as you add rows).

Does each of the machines have plenty of RAM? For example, the data nodes for example have been configured to have 14GB of Data Memory – if any of that is swapped to disk then the performance is likely to be extremely poor and depending on settings, could cause timeouts etc.

After I start as “mysqld_safe…” but not “ndb_mgmd -f …”,I got this error message”…this binary does not contain NDBCLUSTER storage engine,InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes…”, but my.cnf has told mysqld_safe to start as NDBCLUSTER.

Does this error mean my downloaded file doesn’t have NDBCLUSTER at all? then where is the cluster installing files’ downloading URL which contains real NDBCLUSTER storage engine?

I am mysql Cluster’s lover and hope to join you and get help reply as soon as possible.

P.S.: Thanks for sir Andrew’s several answer before, I have succeeded in installing “mysql-cluster-gpl-7.4.6-linux-glibc2.5-x86_64” in Linux but not windows, can you tell me the real cluster installing package’s url or help me to solve the problem?

Dear Sir Andrew,
Thank you very much, the safe SQL node can begin to run without error temporally now. You are great, and it seems that the default install binary is the correct one.
As my memory, yesterday, I denifitely use the “–defaults-file=/usr/local/mysql/my.cnf” to start, after it failed, I didn’t use this option and it told me to put my.cnf to /usr/, and I did that, but it failed again.

And today, I follow your steps and denifitely use the mysqld_safe in the installing package folder of “/usr/local/mysql-cluster-gpl-7.4.6-linux-glibc2.5-x86_64/bin”.

Hope you and oracle’s genius free MySQLCluster 7.4.6 can work greatly with real over 99.999% high availabilty and high scalabitly and realtime replication with partitioning!

Thanks fro Andrew and others’ guide, I have run MySQLCluster 7.4.6 2 data nodes, 1 mgm and 1 SQL node in linux with 1 SQL node in win7. but cluster has these two problems.

1st problem: I got thrice error: ” Node 3: Forced node shutdown completed. Caused by error 2305: ‘Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s)(Arbitration error). Temporary error, restart node’.” ,
what is the cause?

2nd problem: before I install 1 SQL node in win7, I have uninstalled the old community MySQL server, and through the win7 SQL node, I can see
table record created in mgm node, but the cluster’s other info, such as root passwords and other accounts info can not sync to Win7, and it is strange that the SQL node still use the win7’s old mysql password to update and connect to cluster, is this a bug?

For the 1st problem, it looks as though one of your machines failed or you had a networking problem. Note that to avoid a single point of failure, there are some important rules to follow in terms of where the management node (which acts as the arbitrator in the event of network partitioning) resides – take a look at MySQL Cluster fault tolerance – impact of deployment decisions.

In order to not let my question be more outstanding among reply, could you please forgive me to submit it as a new topic here again?
my submission reply is :

Hi, Dear Andrew,

Has cluster ndb 7.4.6 totally support “referencing just part of the multi-part key ” as a foreign key?

“…Note that by referencing just part of the multi-part key it’s actually non-unique even if the full key was unique …” does this means that in cluster NDB engine, the reference foreign key should be only one UNIQUE key, but cann’t be a part of multi-coloumn key?

But it is strange that some table constraint with a part of multi-coloumn key can create in NDBCluster, but some tables doesn’t.