By Jeremy Smyth on Apr 11, 2013
If you're considering using MySQL Cluster, you might be tempted to try it out on one of your existing databases. MySQL Cluster 7.3 (available as a development release at the time of this post) supports foreign keys, so it seems sensible to try migrating an InnoDB database with foreign keys to MySQL Cluster.
What won't work
So, you might try something like this:
ALTER TABLE Country ENGINE=NDB; ALTER TABLE City ENGINE=NDB; ALTER TABLE CountryLanguage ENGINE=NDB;
Sadly, this won't work; InnoDB won't let you convert a table from InnoDB to another engine if the table is at either end of a foreign key, to avoid dangling constraints. If you try to convert an InnoDB table to NDB using one of the above statements, this happens:
mysql> ALTER TABLE Country ENGINE=NDB; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Now, the clever among you might be aware of the foreign_key_checks variable, which disables InnoDB's foreign key constraint checking when you turn it off. It's useful when you're importing data from a script and don't want to check constraints until the import has finished. However, you can't use it to switch off contraints when changing a table's storage engine; it's designed to be used temporarily, for data, so it won't allow dangling constraints. Similarly, the ndb_deferred_constraints variable can't be used for this purpose either, because it doesn't affect InnoDB's constraint protection.
So how do I do it?
There are two ways to migrate InnoDB tables with foreign keys to NDB.
- Dump the database and edit the script so each table specifies ENGINE=NDB before re-importing the script to a new database
- Drop the constraints, alter the tables to use the NDB engine, and recreate the constraints
Dumping the database and editing the script is a straightforward use of mysqldump and a text editor.
Dropping and recreating the constraints is more interesting, so I'll walk through that.
First, I use SHOW CREATE TABLE to see the constraints' definitions, and particularly their names:
mysql> SHOW CREATE TABLE City\G *************************** 1. row *************************** Table: City Create Table: CREATE TABLE `City` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 1 row in set (0.02 sec)
Running the same command for the CountryLanguage table gives me the following constraint (the Country table does not have any foreign keys defined):
CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
Now I know what they're called, I can drop them:
mysql> ALTER TABLE City -> DROP FOREIGN KEY city_ibfk_1; Query OK, 4079 rows affected (0.95 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE CountryLanguage -> DROP FOREIGN KEY countryLanguage_ibfk_1; Query OK, 984 rows affected (0.58 sec) Records: 984 Duplicates: 0 Warnings: 0
Then I can run the ALTER TABLE statements shown at the top of this post:
mysql> ALTER TABLE Country ENGINE=NDB; Query OK, 239 rows affected (4.15 sec) Records: 239 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE City ENGINE=NDB; Query OK, 4079 rows affected (4.02 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE CountryLanguage ENGINE=NDB; Query OK, 984 rows affected (4.32 sec) Records: 984 Duplicates: 0 Warnings: 0
Finally, I can recreate the constraints based on their definitions from the output of the SHOW CREATE TABLE statements above:
mysql> ALTER TABLE City -> ADD CONSTRAINT `city_ibfk_1` -> FOREIGN KEY (`CountryCode`) -> REFERENCES `Country` (`Code`); Query OK, 0 rows affected (1.46 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE CountryLanguage -> ADD CONSTRAINT `countryLanguage_ibfk_1` -> FOREIGN KEY (`CountryCode`) -> REFERENCES `Country` (`Code`); Query OK, 0 rows affected (1.01 sec) Records: 0 Duplicates: 0 Warnings: 0
Now that we have our constraints back, I can see if they work:
mysql> DELETE FROM Country WHERE Code='IRL'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code)
All is as expected, and now my InnoDB data is working in my cluster.