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.
First of all, create the tables and add some data:
mysql> CREATE DATABASE clusterdb;USE clusterdb;
mysql> CREATE TABLE counties (county VARCHAR(30) NOT NULL PRIMARY KEY, country VARCHAR(30))
ENGINE=ndb;
mysql> REPLACE INTO counties VALUES ('Berkshire','England'),('Shropshire','England'),
('Oxfordshire','England'),('Buckinghamshire','England');
mysql> CREATE TABLE towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county VARCHAR(30),
INDEX county_county_index (county), CONSTRAINT county_town FOREIGN KEY (county)
REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE=ndb;
mysql> REPLACE INTO towns VALUES ('Maidenhead','Berkshire'),('Reading','Berkshire'),
('Shrewsbury','Shropshire'),('Oxford','Oxfordshire');
mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county | country |
+-----------------+---------+
| Shropshire | England |
| Buckinghamshire | England |
| Berkshire | England |
| Oxfordshire | England |
+-----------------+---------+
4 rows in set (0.00 sec)
+------------+-------------+
| town | county |
+------------+-------------+
| Reading | Berkshire |
| Shrewsbury | Shropshire |
| Maidenhead | Berkshire |
| Oxford | Oxfordshire |
+------------+-------------+
4 rows in set (0.00 sec)
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:
mysql> UPDATE counties SET country='WALES' WHERE county='Shropshire'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Next, to illustrate the kind of user error that can be prevented, we attempt to add a town in a county that has not yet been defined – we want this to fail as it would break the constraint we created:
mysql> INSERT towns VALUES ('Cambridge','Cambridgeshire');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(Unknown error code)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 255 'Foreign key constaint violated: No parent row found' from NDB |
| Error | 1452 | Cannot add or update a child row: a foreign key constraint fails (Unknown error code) |
+---------+------+---------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
We can also confirm that the constraint checking works in the opposite direction, by attempting to delete a county that has towns defined for it:
mysql> DELETE FROM counties WHERE county='Berkshire'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) mysql> SHOW WARNINGS; +---------+------+-------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------+ | Warning | 1296 | Got error 256 'Foreign key constaint violated: Referenced row exists' from NDB | | Error | 1451 | Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) | +---------+------+-------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
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:
mysql> SHOW CREATE TABLE towns; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | towns | CREATE TABLE `towns` ( `town` varchar(30) NOT NULL, `county` varchar(30) DEFAULT NULL, PRIMARY KEY (`town`), KEY `county_county_index` (`county`), CONSTRAINT `county_town` FOREIGN KEY(`county`) REFERENCES `counties` (`county`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE towns DROP FOREIGN KEY county_town; Query OK, 0 rows affected (0.47 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE towns ADD CONSTRAINT county_town FOREIGN KEY (county) REFERENCES counties(county) ON DELETE CASCADE ON UPDATE CASCADE;
The data in the tables will not have changed as part of dropping and adding constraints and so you can always DROP and the re-add constraints if you need to turn FKs off for a while:
mysql> SELECT * FROM counties;SELECT * FROM towns; +-----------------+---------+ | county | country | +-----------------+---------+ | Buckinghamshire | England | | Berkshire | England | | Oxfordshire | England | | Shropshire | WALES | +-----------------+---------+ 4 rows in set (0.00 sec) +------------+-------------+ | town | county | +------------+-------------+ | Reading | Berkshire | | Shrewsbury | Shropshire | | Maidenhead | Berkshire | | Oxford | Oxfordshire | +------------+-------------+ 4 rows in set (0.00 sec)
Now if we delete the county of Berkshire then the CASCADE option will cause all of the associated towns to be deleted too:
mysql> DELETE FROM counties WHERE county='Berkshire'; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM counties;SELECT * FROM towns; +-----------------+---------+ | county | country | +-----------------+---------+ | Shropshire | WALES | | Buckinghamshire | England | | Oxfordshire | England | +-----------------+---------+ 3 rows in set (0.00 sec) +------------+-------------+ | town | county | +------------+-------------+ | Shrewsbury | Shropshire | | Oxford | Oxfordshire | +------------+-------------+ 2 rows in set (0.00 sec)
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:
mysql> REPLACE INTO counties VALUES ('Berkshire','England');
Query OK, 1 row affected (0.00 sec)
mysql> REPLACE INTO towns VALUES ('Maidenhead','Berkshire'),('Reading','Berkshire');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county | country |
+-----------------+---------+
| Shropshire | WALES |
| Buckinghamshire | England |
| Berkshire | England |
| Oxfordshire | England |
+-----------------+---------+
4 rows in set (0.00 sec)
+------------+-------------+
| town | county |
+------------+-------------+
| Reading | Berkshire |
| Shrewsbury | Shropshire |
| Maidenhead | Berkshire |
| Oxford | Oxfordshire |
+------------+-------------+
4 rows in set (0.00 sec)
We’ll then run a simple C++ app to delete the ‘Berkshire’ row from the counties table. The full source for the app is available here but the key part of the code is this:
static void do_change(Ndb &myNdb)
{
const NdbDictionary::Dictionary* myDict= myNdb.getDictionary();
const NdbDictionary::Table *countiesTable= myDict->getTable("counties");
char royalBorough[255];
royalBorough[0]=9;
strcpy(royalBorough + 1, "Berkshire");
if (countiesTable == NULL)
APIERROR(myDict->getNdbError());
NdbTransaction *myTransaction= myNdb.startTransaction();
if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
NdbOperation *myOperation= myTransaction->getNdbOperation(countiesTable);
if (myOperation == NULL) APIERROR(myTransaction->getNdbError());
std::cout << "About to delete " << royalBorough << "n";
myOperation->deleteTuple();
myOperation->equal("county", (const char *) royalBorough);
if (myTransaction->execute( NdbTransaction::Commit ) == -1)
APIERROR(myTransaction->getNdbError());
myNdb.closeTransaction(myTransaction);
}
We then run this code and confirm that the cascaded delete has worked:
billy@ws2:~/projects/fk$ ./testfk localhost:1186 About to delete Berkshire mysql> SELECT * FROM counties;SELECT * FROM towns; +-----------------+---------+ | county | country | +-----------------+---------+ | Shropshire | WALES | | Buckinghamshire | England | | Oxfordshire | England | +-----------------+---------+ 3 rows in set (0.00 sec) +------------+-------------+ | town | county | +------------+-------------+ | Shrewsbury | Shropshire | | Oxford | Oxfordshire | +------------+-------------+ 2 rows in set (0.00 sec)
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
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:
[…] Read this blog for a demonstration of using Foreign Keys with MySQL Cluster. […]
Hi,
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?
Dhanush Gopinath
Dhanush,
MySQL Cluster 7.2 introduced major performance improvements for joins and when using the multi-threaded data node on servers with lots of cores.
What the 7.3 DMR focussed on was showcasing Foreign Key support rather than additional performance gains.
Please do post the errors/logs if you see your cluster shut down.
Regards, Andrew.
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
Time: Thursday 12 July 2012 – 16:56:09
Status: Temporary error, restart node
Message: Internal program error (failed ndbrequire) (Internal error, programming error or missing error message, please report a bug)
Error: 2341
Error data: DbtcMain.cpp
Error object: DBTC (Line: 16955) 0x00000002
Program: ndbd
Pid: 6613
Version: mysql-5.5.22 ndb-7.3.0
Trace: /rcloud/rcloud_cluster_73/ndb_data/ndb_4_trace.log.2 [t1..t1]
***EOM***
I see that this is already a bug http://bugs.mysql.com/bug.php?id=64609
Is this an issue in 7.3 ?
Dhanush,
to get some more eyes looking at this, I’d suggest that you also post the full issue description on http://forums.mysql.com/list.php?25
Regards, Andrew.
Andrew,
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.
Thanks, John
Hi John,
Great to hear that you’re trying this stuff out!
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.
Thanks, Andrew.
Hi guys!
I have installed MySQL Cluster ver 7.3. for have foreign key support but I have two problems:
1) I can’t create foreign key using ON UPDATE CASCADE option. I have this error: #150 – Cannot add foreign key constraint
My SQL comand is like this:
ALTER TABLE `xxxxxxx`
ADD CONSTRAINT `xxxxxxxx_ibfk_1` FOREIGN KEY (`groupname`) REFERENCES `HsProductsPolicy` (`Code`) ON DELETE CASCADE ON UPDATE CASCADE;
If I delete ON UPDATE CASCADE all is OK: why?
2) I can’t create foreign key from two tables using an index that isn’t PRIMARY KEY: is it possibile?
I have, also in this case, this error:#150 – Cannot add foreign key constraint
I hope that someone help me
Best regards
Mirio,
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?
Andrew.
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
CREATE TABLE IF NOT EXISTS `HsProductsPolicy` (
`HsProductsPolicyID` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘ID della policy per i prodotti’,
`Code` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘Codice della policy. Corrisponderà al nome del gruppo di FreeRadius’,
`Notes` text COLLATE utf8_unicode_ci COMMENT ‘Note della policy’,
`ForFreeHotSpots` tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT ‘Se True la policy è per prodotti da associare a HotSpots liberi’,
`TrafficBandwidthModulation` int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Abilita la modulazione della velocità in base al traffico. 0 = Non abilitato; 1 = Abilitato medio; ecc.’,
`TrafficBandwidthModulationFromTime` int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Attiva dalle ore’,
`TrafficBandwidthModulationToTime` int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Attiva fino alle ore’,
`TrafficBandwidthModulationHours` int(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Periodo di tempo espresso in ore da considerare per l”analisi del traffico effettuato per la modulazione della velocità ‘,
`TrafficBandwidthModulationTrafficUp` int(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Traffico limite in upload per la modulazione della velocità ‘,
`TrafficBandwidthModulationTrafficDown` int(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Traffico limite in download per la modulazione della velocità ‘,
`BandwidthMinUp` int(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Velocità minima di upload’,
`BandwidthMinDown` int(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Velocità minima di download’,
`BandwidthMaxDown` int(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Velocità massima di download’,
`BandwidthMaxUp` int(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Velocità massima di upload’,
PRIMARY KEY (`HsProductsPolicyID`),
KEY `HsProductsPolicyCode` (`Code`)
) ENGINE=NDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED COMMENT=’Tabella con l”elenco delle policy’ AUTO_INCREMENT=8 ;
CREATE TABLE IF NOT EXISTS `radgroupreply` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`groupname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT ”,
`attribute` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT ”,
`op` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘=’,
`value` varchar(253) COLLATE utf8_unicode_ci NOT NULL DEFAULT ”,
PRIMARY KEY (`id`),
KEY `GroupName_IDX` (`groupname`)
) ENGINE=NDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=129 ;
ALTER TABLE `radgroupreply`
ADD CONSTRAINT `radgroupreply_ibfk_1` FOREIGN KEY (`groupname`) REFERENCES `HsProductsPolicy` (`Code`) ON DELETE CASCADE ;
Thanks in advance
Best regards
Any news????
Best regards
Thanks for the detailed blog post, it’s great to hear that MySQL Cluster will support foreign keys soon.
I’ve been playing with 7.3 and I notice that it doesn’t support foreign keys in conjuction with a custom partitioning scheme – for example:
CREATE TABLE Parent (
id int not null PRIMARY KEY
)
ENGINE=NDB
;
— NB: I’ve used a “PARTITION BY KEY” directive
— to ensure the child rows are on the same node
— as their parent row.
CREATE TABLE Child (
id int not null,
parentId int not null,
INDEX idxChild_parentId (parentId),
UNIQUE KEY (parentId, id),
CONSTRAINT fkChild_parentId
FOREIGN KEY (parentId) REFERENCES Parent (id)
ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE=NDB
PARTITION BY KEY (eventId)
;
This gives “ERROR 1506 (HY000) at line 7: Foreign key clause is not yet supported in conjunction with partitioning”
I wonder if the production release of 7.3 will support this? Or is what I’m trying to do achievable another way (or a bad idea, for that matter)?
Hi Kieran,
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.
Andrew.
Hi Kieran,
just tested this scenario on the latest internal version of Cluster 7.3 (a test that failed in the same way as yours in the DMR)…
mysql> CREATE TABLE parent (id INT NOT NULL PRIMARY KEY) ENGINE=NDB;
Query OK, 0 rows affected (0.78 sec)
mysql> CREATE TABLE child (id INT NOT NULL,parentId INT NOT NULL,INDEX idxChild_parentId (parentId), PRIMARY KEY(parentId, id), CONSTRAINT fkChild_parentId FOREIGN KEY (parentId) REFERENCES parent (id) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE=NDB PARTITION BY KEY(parentId);
Query OK, 0 rows affected (1.01 sec)
… so I can confirm that Fks and paritions will be compatible when we release the GA version.
Thanks again for the feedback.
Andrew.
[…] 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
create table wtestinnodb(col1 int, col2 varchar(20), constraint `f1` foreign key(col1) references wtestndb(col1))engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
where wtestndb is ndb table.
Unfortunately, you can have FK that span NDB and InnoDB tables as the constraints are implemented down in the storage engines.
Andrew.
hi
wanted to confirm if the FK support is present with 5.5 Mysql?
the documentation of 5.5 does not mention this.
thanks and regards,
ch Vishnu
Vishnu,
FKs are available for InnoDB in MySQL 5.5 and earlier but for MySQL Cluster they’re not there until MySQL Cluster 7.3 (which includes MySQL 5.6 for the MySQL Server component).
Andrew.
Hi Andrew/All,
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.
Following example for innodb works very well.
mysql> CREATE TABLE `new_products` (
-> `id` int(10) unsigned NOT NULL,
-> `extension_id` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `name` varchar(255) COLLATE latin1_general_ci NOT NULL,
-> `min_months` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `max_months` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `default_months` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `min_years` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `max_years` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `default_years` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `desctype` int(10) unsigned NOT NULL,
-> `type` enum(‘new’,’renewal’,’refund’) COLLATE latin1_general_ci DEFAULT NULL,
-> `renewal_service` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
-> `product_group_id` int(10) unsigned DEFAULT NULL,
-> `navision_code` varchar(32) COLLATE latin1_general_ci DEFAULT ”,
-> `pretty_name` varchar(255) COLLATE latin1_general_ci DEFAULT ”,
-> PRIMARY KEY (`id`,`extension_id`),
-> KEY `name` (`name`),
-> KEY `type` (`type`),
-> KEY `desctype` (`desctype`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
-> ;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `customer_referral_commission` (
-> `product_id` int(10) unsigned NOT NULL,
-> `extension_id` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `customer_commission` decimal(9,2) unsigned DEFAULT ‘0.00’,
-> `date_entered` datetime DEFAULT NULL,
-> `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`product_id`,`extension_id`),
-> KEY `product_id` (`product_id`),
-> KEY `extension_id` (`extension_id`),
-> CONSTRAINT `customer_referral_commission_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `new_products` (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
Query OK, 0 rows affected (0.02 sec)
Following for ndb doesnt work at all.
mysql> CREATE TABLE `new_products_ndb` (
-> `id` int(10) unsigned NOT NULL,
-> `extension_id` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `name` varchar(255) COLLATE latin1_general_ci NOT NULL,
-> `min_months` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `max_months` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `default_months` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `min_years` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `max_years` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `default_years` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `desctype` int(10) unsigned NOT NULL,
-> `type` enum(‘new’,’renewal’,’refund’) COLLATE latin1_general_ci DEFAULT NULL,
-> `renewal_service` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
-> `product_group_id` int(10) unsigned DEFAULT NULL,
-> `navision_code` varchar(32) COLLATE latin1_general_ci DEFAULT ”,
-> `pretty_name` varchar(255) COLLATE latin1_general_ci DEFAULT ”,
-> PRIMARY KEY (`id`,`extension_id`),
-> KEY `name` (`name`),
-> KEY `type` (`type`),
-> KEY `desctype` (`desctype`)
-> ) ENGINE=ndb DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
-> ;
Query OK, 0 rows affected (0.41 sec)
mysql> CREATE TABLE `customer_referral_commission_ndb` (
-> `product_id` int(10) unsigned NOT NULL,
-> `extension_id` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `customer_commission` decimal(9,2) unsigned DEFAULT ‘0.00’,
-> `date_entered` datetime DEFAULT NULL,
-> `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`product_id`,`extension_id`),
-> KEY `product_id` (`product_id`),
-> KEY `extension_id` (`extension_id`),
-> CONSTRAINT `customer_referral_commission_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `new_products` (`id`)
-> ) ENGINE=ndb DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> CREATE TABLE `customer_referral_commission_ndb` (
-> `product_id` int(10) unsigned NOT NULL,
-> `extension_id` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `customer_commission` decimal(9,2) unsigned DEFAULT ‘0.00’,
-> `date_entered` datetime DEFAULT NULL,
-> `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`product_id`,`extension_id`),
-> KEY `product_id` (`product_id`),
-> KEY `extension_id` (`extension_id`),
-> CONSTRAINT `customer_referral_commission_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `new_products` (`id`) on delete cascade on update restrict
-> ) ENGINE=ndb DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
ERROR 1215 (HY000): Cannot add foreign key constraint
Can somebody please help?
Many thanks
Just to add there’s a small typo in the references for the foreign key but smae error.
mysql> CREATE TABLE `customer_referral_commission_ndb` (
-> `product_id` int(10) unsigned NOT NULL,
-> `extension_id` int(10) unsigned NOT NULL DEFAULT ‘0’,
-> `customer_commission` decimal(9,2) unsigned DEFAULT ‘0.00’,
-> `date_entered` datetime DEFAULT NULL,
-> `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`product_id`,`extension_id`),
-> KEY `product_id` (`product_id`),
-> KEY `extension_id` (`extension_id`),
-> CONSTRAINT `customer_referral_commission_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `new_products_ndb` (`id`) on delete cascade on update restrict
-> ) ENGINE=ndb DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
ERROR 1215 (HY000): Cannot add foreign key constraint
Hi Ricky,
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).
Thanks for pointing this out.
Andrew.
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.
Such as, these tables can create:
CREATE TABLE `pz_tb_wdcash` (
`ID` varchar(255) NOT NULL COMMENT ‘ID’,
`pzAccount` varchar(32) DEFAULT NULL COMMENT ‘平台账户ID’,
…
`opratAdmin` varchar(100) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `FKF70CA7C9A279E9A` (`pzAccount`) USING BTREE,
KEY `FKF70CA7C60F7D21E` (`pzBankCard`) USING BTREE,
KEY `FKF70CA7C6D563EAE` (`pzBankCard`) USING BTREE,
CONSTRAINT `pz_tb_wdcash_ibfk_1` FOREIGN KEY (`pzBankCard`) REFERENCES `p2p_td_bank_card` (`ID`),
CONSTRAINT `pz_tb_wdcash_ibfk_2` FOREIGN KEY (`pzAccount`) REFERENCES `p2p_td_user` (`ID`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
pz_tb_wdcash’s 1 foreign key is the below table’s one of keys “ID”.
CREATE TABLE `pz_tb_bank_card` (
`ID` varchar(255) NOT NULL,
`C_USER` varchar(32) NOT NULL COMMENT ‘平台账户ID’,
…
`C_UPDATE_TIME` datetime DEFAULT NULL COMMENT ‘更新时间’,
PRIMARY KEY (`ID`),
KEY `FKF6C0EC392F758BDE` (`C_USER`) USING BTREE,
KEY `FKF6C0EC392F63C0C0` (`C_BANK`) USING BTREE,
CONSTRAINT `pz_tb_bank_card_ibfk_1` FOREIGN KEY (`C_BANK`) REFERENCES `p2p_td_bank` (`ID`),
CONSTRAINT `pz_tb_bank_card_ibfk_2` FOREIGN KEY (`C_USER`) REFERENCES `p2p_td_user` (`ID`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
but why the below table cann’t create?
DROP TABLE IF EXISTS `pz_tb_pz_account`;
/*Error 1215；can not add foreign key constraint*/
CREATE TABLE `pz_tb_pz_account` (
`id` varchar(40) NOT NULL COMMENT ‘ID’,
`pzid` varchar(40) DEFAULT NULL COMMENT ‘配资id’,
…
`status` varchar(5) DEFAULT ‘0’ COMMENT ‘清算审批状态 0 未审批 1 审批完成’,
PRIMARY KEY (`id`),
KEY `FK690FB1F479E54801` (`pzid`) USING BTREE,
CONSTRAINT `pz_tb_pz_account_ibfk_1` FOREIGN KEY (`pzid`) REFERENCES `pz_tb_pz` (`id`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
its reference table has been created as below:
CREATE TABLE `pz_tb_pz` (
`id` varchar(32) NOT NULL COMMENT ‘ID’,
…
`endtype` varchar(5) DEFAULT NULL COMMENT ‘终止类型 1方案到期 2用户手动终止 3到达平仓线’,
PRIMARY KEY (`id`),
KEY `FKB62A63069A279E9A` (`pzAccount`) USING BTREE,
KEY `FKB62A630649DB09AA` (`pzAccount`) USING BTREE
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
why? and I have add MaxNoOfTriggers=5188 in config.ini, is there any good solution?
Thank you.
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:
CREATE TABLE `campuses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`code` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `code` (`code`)
) ENGINE=ndbcluster AUTO_INCREMENT=152 DEFAULT CHARSET=latin1 |
CREATE TABLE `thresholdtriggers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(60) NOT NULL,
`threshold_amount` decimal(10,2) NOT NULL,
`emails` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
And I’m trying to create a third table as follows:
CREATE TABLE IF NOT EXISTS `thresholdtriggerstocampuses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`threshold_trigger_id` int(11) NOT NULL,
`campus_code` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
KEY `threshold_trigger_id` (`threshold_trigger_id`),
KEY `campus_code` (`campus_code`),
CONSTRAINT `thresholdtriggerstocampuses_ibfk_2` FOREIGN KEY (`campus_code`) REFERENCES `campuses` (`code`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `thresholdtriggerstocampuses_ibfk_1` FOREIGN KEY (`threshold_trigger_id`) REFERENCES `thresholdtriggers` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Our lead developer on this project is working on InnoDB tables (where this works fine) and then we’re migrating out of his dev environment into our production MySQL Cluster / NDB environment.
I consistently get the following error when I try to make these changes in that environment.
ERROR 1215 (HY000): Cannot add foreign key constraint
Anybody have any thoughts?
Thank you!
Brad
Hi Brad – the main limitation of FKs in Cluster (vs. InnoDB) is that the constraint is not allowed to modify a PK but it looks like your trying to delete one and so I’d have expected that to be OK.
I’ll try this for myself as soon as I get the chance.
Andrew.
Thanks, Andrew! Sure appreciate your help!
After some further poking around with it, we found we were able to complete one of the two constraints.
This one was fine:
CONSTRAINT `thresholdtriggerstocampuses_ibfk_1` FOREIGN KEY (`threshold_trigger_id`) REFERENCES `thresholdtriggers` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
The other one did not work:
CONSTRAINT `thresholdtriggerstocampuses_ibfk_2` FOREIGN KEY (`campus_code`) REFERENCES `campuses` (`code`) ON DELETE CASCADE ON UPDATE NO ACTION
I still haven’t been able to identify precisely why this won’t work (tried it with a variety of on delete options as well), but I guess this is one step closer right?!
b.
Brad,
I was able to recreate the issue – if you issue
SHOW WARNINGSthen 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…CREATE TABLE campuses (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
code varchar(30) NOT NULL,
PRIMARY KEY (id),
KEY name (name),
UNIQUE KEY code (code)
) ENGINE=ndbcluster AUTO_INCREMENT=152 DEFAULT CHARSET=latin1;
Andrew.
Hi Andrew,
We have two tables that we need to convert to ndbcluster engine with unique key added, but still fails.
Below are the table:
DROP TABLE IF EXISTS `rivet_session`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `rivet_session` (
`ip_address` varchar(16) DEFAULT NULL,
`session_start_time` datetime DEFAULT NULL,
`session_update_time` datetime DEFAULT NULL,
`session_id` varchar(64) NOT NULL DEFAULT ”,
PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `rivet_session_cache`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `rivet_session_cache` (
`session_id` varchar(128) DEFAULT NULL,
`package_` varchar(64) DEFAULT NULL,
`key_` varchar(128) DEFAULT NULL,
`data` varchar(255) DEFAULT NULL,
UNIQUE KEY `riv_sess_cache_ix` (`session_id`,`package_`,`key_`),
KEY `rivet_session_cache_idx` (`session_id`),
CONSTRAINT `rivet_session_cache_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `rivet_session` (`session_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */
Both table fails with “Cannot delete or update a parent row: a foreign key constraint fails”
mysql> alter table rivet_session ENGINE=NDBCLUSTER;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> alter table rivet_session_cache ENGINE=NDBCLUSTER;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
How can this be fixed ?
Thanks for your help!
Naveed
Hi Naveed,
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.
Andrew.
Thanks Andrew for getting back to me 🙂
I did as you suggested by dropping FK’s then Alter table to use engine=ndbcluster and table was saved. I then added FX constraint and still got error:
ALTER TABLE `rivet_session_cache` ADD CONSTRAINT `rivet_session_cache_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `rivet_session` (`session_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
1215- Cannot add foreign key constraint.
Thanks for your help!
Naveed
Naveed,
could you please run
SHOW WARNINGSin order to see why it’s failing?Andrew.
Hi Andrew,
Thanks again for getting back me.
Warning shows:
ALTER TABLE `rivet_session_cache` ADD CONSTRAINT `rivet_session_cache_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `rivet_session` (`session_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ERROR 1215 (HY000): Cannot add foreign key constraint
| Warning | 1215 | Parent column rivet_session.session_id is incompatible with child column rivet_session_cache.session_id in NDB |
| Error | 1215 | Cannot add foreign key constraint
But, Not sure why the id is incomparable ?
Here are my tables:
CREATE TABLE `rivet_session_cache` (
`session_id` varchar(128) DEFAULT NULL,
`package_` varchar(64) DEFAULT NULL,
`key_` varchar(128) DEFAULT NULL,
`data` varchar(255) DEFAULT NULL,
UNIQUE KEY `riv_sess_cache_ix` (`session_id`,`package_`,`key_`),
KEY `rivet_session_cache_idx` (`session_id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
CREATE TABLE `rivet_session` (
`ip_address` varchar(16) DEFAULT NULL,
`session_start_time` datetime DEFAULT NULL,
`session_update_time` datetime DEFAULT NULL,
`session_id` varchar(64) NOT NULL DEFAULT ”,
PRIMARY KEY (`session_id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Thanks,
Naveed
We are able to create table. But, when we are trying to insert data in this table we showing only process is running long time. And after long time we are not getting any response from server.
Also we have test same scenario with single data node it is working fine.
CREATE TABLE `TB_Name` (
`T1` BIGINT(20) NOT NULL,
`T2` BIGINT(20) NOT NULL,
`T3` INT(11) NOT NULL,
`T4` SMALLINT(6) DEFAULT NULL,
`T5` SMALLINT(6) DEFAULT NULL,
PRIMARY KEY (`T1`),
UNIQUE KEY `UK_CoEE` (`T2`,`T3`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;
INSERT INTO `TB_Name`(`T1`,`T2`,`T3`,`T4`,`T5`) VALUES (325302462008513,2460,244,1,7) ,(88727112009284,1,245,1,25);
+—-+————-+——————–+———+———+——+———————————–+———————————————————————————————–
| Id | User | Host | db | Command | Time | State | Info
+—-+————-+——————–+———+———+——+———————————–+———————————————————————————————–
| 4 | root | localhost | DB_Name | Query | 1519 | query end | REPLACE INTO `CoExtElement` VALUES (325302462008513,2460,244,1,7),(88727112009284,1,245,1,25)
+—-+————-+——————–+———+———+——+———————————–+———————————————————————————————–
— We have 6 individual cantos 64 BIT virtual machines on XEN Server.
Following are the configuration files.
————————————————————————
[NDBD DEFAULT]
MaxBufferedEpochs=5000
BackupMaxWriteSize=54M
BackupDataBufferSize=100M
BackupLogBufferSize=50M
BackupMemory=55M
BackupReportFrequency=10
MemReportFrequency=30
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15
DataMemory=14032M
IndexMemory=1024M
MaxNoOfTables=4596
MaxNoOfSubscribers=10000
MaxNoOfTriggers=3500
NoOfReplicas=2
StringMemory=25
DiskPageBufferMemory=1048M
SharedGlobalMemory=512M
LongMessageBuffer=32M
MaxNoOfConcurrentTransactions=195000
BatchSizePerLocalScan=512
FragmentLogFileSize=256M
NoOfFragmentLogFiles=16
RedoBuffer=128M
MaxNoOfExecutionThreads=2
StopOnError=false
LockPagesInMainMemory=1
TimeBetweenEpochsTimeout=32000
TimeBetweenWatchdogCheckInitial=60000
TransactionDeadlockDetectionTimeout=8640000
TransactionInactiveTimeout=60000
HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000
MaxNoOfConcurrentOperations=510000
MaxNoOfLocalOperations=561000
MaxNoOfAttributes=314572
MaxNoOfOrderedIndexes=8000
RealtimeScheduler=1
Datadir=/home/mysql/data/
[MYSQLD DEFAULT]
[TCP DEFAULT]
SendBufferMemory=64M
ReceiveBufferMemory=24M
[NDB_MGMD DEFAULT]
Portnumber=1186
[NDB_MGMD]
NodeId=1
Hostname=192.168.0.53
LogDestination=FILE:filename=ndb_1_cluster.log,maxsize=10000000,maxfiles=6
DataDir=/var/lib/mysql-cluster/
[NDB_MGMD]
NodeId=2
Hostname=192.168.0.55
LogDestination=FILE:filename=ndb_2_cluster.log,maxsize=10000000,maxfiles=6
DataDir=/var/lib/mysql-cluster/
[NDBD]
NodeId=3
Hostname=192.168.0.57
datadir=/home/mysql/data/
[NDBD]
NodeId=4
Hostname=192.168.0.65
datadir=/home/mysql/data/
[MYSQLD]
NodeId=5
hostname=192.168.0.51
[MYSQLD]
NodeId=6
hostname=192.168.0.56
————————————————————————
[root@mgm ~]# ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=3 @192.168.0.57 (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)
id=4 @192.168.0.65 (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.53 (mysql-5.6.19 ndb-7.3.6)
id=2 @192.168.0.55 (mysql-5.6.19 ndb-7.3.6)
[mysqld(API)] 2 node(s)
id=5 @192.168.0.51 (mysql-5.6.19 ndb-7.3.6)
id=6 @192.168.0.56 (mysql-5.6.19 ndb-7.3.6)
————————————————————————
[root@mysqld ~]# cat /etc/my.cnf
[client]
socket=/home/mysql-Cluster/mysql.sock
[mysqld]
max_connections=100
datadir=/home/mysql-Cluster
socket=/home/mysql-Cluster/mysql.sock
ndbcluster
ndb-connectstring=192.168.0.53, 192.168.0.55
ndb-force-send=1
ndb-use-exact-count=0
ndb-extra-logging=1
ndb-batch-size=24M
ndb-autoincrement-prefetch-sz=1024
default-storage-engine=NDB
max_allowed_packet = 5G
skip-name-resolve
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster]
ndb-connectstring=192.168.0.53, 192.168.0.55
[root@mysqld ~]#
————————————————————————
Also i have checked all logs but i am not getting any clue.
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).
Andrew.
We have 6 individual cantos 64 BIT virtual machines on XEN Server.
We are able to create table. But, when we are trying to insert data in this table we showing only process is running long time. And after long time we are not getting any response from server.
Also we have tested same scenario with single data node it is working fine.
Data Node RAM : 19GB Both.
CREATE TABLE `TB_Name` (
`T1` BIGINT(20) NOT NULL,
`T2` BIGINT(20) NOT NULL,
`T3` INT(11) NOT NULL,
`T4` SMALLINT(6) DEFAULT NULL,
`T5` SMALLINT(6) DEFAULT NULL,
PRIMARY KEY (`T1`),
UNIQUE KEY `UK_CoEE` (`T2`,`T3`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;
INSERT INTO `TB_Name`(`T1`,`T2`,`T3`,`T4`,`T5`) VALUES (325302462008513,2460,244,1,7) ,(88727112009284,1,245,1,25);
+—-+————-+——————–+———+———+——+———————————–+———————————————————————————————–
| Id | User | Host | db | Command | Time | State | Info
+—-+————-+——————–+———+———+——+———————————–+———————————————————————————————–
| 4 | root | localhost | DB_Name | Query | 1519 | query end | REPLACE INTO `CoExtElement` VALUES (325302462008513,2460,244,1,7),(88727112009284,1,245,1,25)
+—-+————-+——————–+———+———+——+———————————–+———————————————————————————————–
Configuration Part :
————————————————————————
[NDBD DEFAULT]
MaxBufferedEpochs=5000
BackupMaxWriteSize=54M
BackupDataBufferSize=100M
BackupLogBufferSize=50M
BackupMemory=55M
BackupReportFrequency=10
MemReportFrequency=30
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15
DataMemory=14032M
IndexMemory=1024M
MaxNoOfTables=4596
MaxNoOfSubscribers=10000
MaxNoOfTriggers=3500
NoOfReplicas=2
StringMemory=25
DiskPageBufferMemory=1048M
SharedGlobalMemory=512M
LongMessageBuffer=32M
MaxNoOfConcurrentTransactions=195000
BatchSizePerLocalScan=512
FragmentLogFileSize=256M
NoOfFragmentLogFiles=16
RedoBuffer=128M
MaxNoOfExecutionThreads=2
StopOnError=false
LockPagesInMainMemory=1
TimeBetweenEpochsTimeout=32000
TimeBetweenWatchdogCheckInitial=60000
TransactionDeadlockDetectionTimeout=8640000
TransactionInactiveTimeout=60000
HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000
MaxNoOfConcurrentOperations=510000
MaxNoOfLocalOperations=561000
MaxNoOfAttributes=314572
MaxNoOfOrderedIndexes=8000
RealtimeScheduler=1
Datadir=/home/mysql/data/
[MYSQLD DEFAULT]
[TCP DEFAULT]
SendBufferMemory=64M
ReceiveBufferMemory=24M
[NDB_MGMD DEFAULT]
Portnumber=1186
[NDB_MGMD]
NodeId=1
Hostname=192.168.0.53
LogDestination=FILE:filename=ndb_1_cluster.log,maxsize=10000000,maxfiles=6
DataDir=/var/lib/mysql-cluster/
[NDB_MGMD]
NodeId=2
Hostname=192.168.0.55
LogDestination=FILE:filename=ndb_2_cluster.log,maxsize=10000000,maxfiles=6
DataDir=/var/lib/mysql-cluster/
[NDBD]
NodeId=3
Hostname=192.168.0.57
datadir=/home/mysql/data/
[NDBD]
NodeId=4
Hostname=192.168.0.65
datadir=/home/mysql/data/
[MYSQLD]
NodeId=5
hostname=192.168.0.51
[MYSQLD]
NodeId=6
hostname=192.168.0.56
————————————————————————
[root@mgm ~]# ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=3 @192.168.0.57 (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)
id=4 @192.168.0.65 (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.53 (mysql-5.6.19 ndb-7.3.6)
id=2 @192.168.0.55 (mysql-5.6.19 ndb-7.3.6)
[mysqld(API)] 2 node(s)
id=5 @192.168.0.51 (mysql-5.6.19 ndb-7.3.6)
id=6 @192.168.0.56 (mysql-5.6.19 ndb-7.3.6)
————————————————————————
[root@mysqld ~]# cat /etc/my.cnf
[client]
socket=/home/mysql-Cluster/mysql.sock
[mysqld]
max_connections=100
datadir=/home/mysql-Cluster
socket=/home/mysql-Cluster/mysql.sock
ndbcluster
ndb-connectstring=192.168.0.53, 192.168.0.55
ndb-force-send=1
ndb-use-exact-count=0
ndb-extra-logging=1
ndb-batch-size=24M
ndb-autoincrement-prefetch-sz=1024
default-storage-engine=NDB
max_allowed_packet = 5G
skip-name-resolve
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster]
ndb-connectstring=192.168.0.53, 192.168.0.55
[root@mysqld ~]#
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.
Andrew.
Dear mysql cluster-using pioneer,
Where is the real MySQL Cluster installing package’s URL?
To evaluate mysql cluster’s power, I download “mysql-cluster-gpl-7.4.6-linux-glibc2.5-x86_64” and install in Linux, but after I follow mysql.com’s quick test guide(http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-quick.html).
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?
my.cnf’s setting as below:
[mysqld]
# Options for mysqld process:
ndbcluster # run NDB storage engine
# provide connection string for management server host (default port: 1186)
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?
Best regards,
George Lin
Mobile:0086 180 500 42436
The first thing to check is that you’re running the mysqld_safe that came with the Cluster package and not one that’s already on your system – try
which mysqld_safe.Another possibility is that it isn’t picking up your config file so try adding the explicit
--defaults-file=. 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!
Many thanks to genius Andrew and Oracle’s mysql team!
And I am one of the mysql’s lovers and hope to join your team.
Best regards,
Sincerely,
Georgelin
0086 180 500 42436
Dear Sir Andrew and other pioneers,
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?
Bye.
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.
Andrew.
Dear Andrew,
Thank you for your guide, is the 2nd data node’s fail connecting after 8 hours because of being not consistent MySQLCluster-installing-package’s ownership with the 1st data node?
Georgelin
Dear Andrew,
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.
Such as, these tables can create:
CREATE TABLE `pz_tb_wdcash` (
`ID` varchar(255) NOT NULL COMMENT ‘ID’,
`pzAccount` varchar(32) DEFAULT NULL COMMENT ‘平台账户ID’,
…
`opratAdmin` varchar(100) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `FKF70CA7C9A279E9A` (`pzAccount`) USING BTREE,
KEY `FKF70CA7C60F7D21E` (`pzBankCard`) USING BTREE,
KEY `FKF70CA7C6D563EAE` (`pzBankCard`) USING BTREE,
CONSTRAINT `pz_tb_wdcash_ibfk_1` FOREIGN KEY (`pzBankCard`) REFERENCES `p2p_td_bank_card` (`ID`),
CONSTRAINT `pz_tb_wdcash_ibfk_2` FOREIGN KEY (`pzAccount`) REFERENCES `p2p_td_user` (`ID`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
pz_tb_wdcash’s 1 foreign key is the below table’s one of keys “ID”.
CREATE TABLE `pz_tb_bank_card` (
`ID` varchar(255) NOT NULL,
`C_USER` varchar(32) NOT NULL COMMENT ‘平台账户ID’,
…
`C_UPDATE_TIME` datetime DEFAULT NULL COMMENT ‘更新时间’,
PRIMARY KEY (`ID`),
KEY `FKF6C0EC392F758BDE` (`C_USER`) USING BTREE,
KEY `FKF6C0EC392F63C0C0` (`C_BANK`) USING BTREE,
CONSTRAINT `pz_tb_bank_card_ibfk_1` FOREIGN KEY (`C_BANK`) REFERENCES `p2p_td_bank` (`ID`),
CONSTRAINT `pz_tb_bank_card_ibfk_2` FOREIGN KEY (`C_USER`) REFERENCES `p2p_td_user` (`ID`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
but why the below table cann’t create?
DROP TABLE IF EXISTS `pz_tb_pz_account`;
/*Error 1215；can not add foreign key constraint*/
CREATE TABLE `pz_tb_pz_account` (
`id` varchar(40) NOT NULL COMMENT ‘ID’,
`pzid` varchar(40) DEFAULT NULL COMMENT ‘配资id’,
…
`status` varchar(5) DEFAULT ‘0’ COMMENT ‘清算审批状态 0 未审批 1 审批完成’,
PRIMARY KEY (`id`),
KEY `FK690FB1F479E54801` (`pzid`) USING BTREE,
CONSTRAINT `pz_tb_pz_account_ibfk_1` FOREIGN KEY (`pzid`) REFERENCES `pz_tb_pz` (`id`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
its reference table has been created as below:
CREATE TABLE `pz_tb_pz` (
`id` varchar(32) NOT NULL COMMENT ‘ID’,
…
`endtype` varchar(5) DEFAULT NULL COMMENT ‘终止类型 1方案到期 2用户手动终止 3到达平仓线’,
PRIMARY KEY (`id`),
KEY `FKB62A63069A279E9A` (`pzAccount`) USING BTREE,
KEY `FKB62A630649DB09AA` (`pzAccount`) USING BTREE
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
why? and I have add MaxNoOfTriggers=5188 in config.ini, is there any good solution?
Thank you.
Sorry for the delay – I’m not able to check at this point but it’s worth raising the question on the MySQL Clsuter forum.
hi, MySQL cluster pioneer,
Has the “Error Code: 1215. Cannot add foreign key constraint” been solved in MySQLCluster 7.4.6?
Sorry for the delay – I’m not able to check at this point but if you can’t find the answer in the release notes then it’s worth raising the question on the MySQL Cluster forum.
Best Regards, Andrew.