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.
world_innodb.sqlscript available from the MySQL sample databases. It's a simple database with three tables:
CountryLanguagehave foreign keys referencing the
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.
There are two ways to migrate InnoDB tables with foreign keys to NDB.
ENGINE=NDBbefore re-importing the script to a new database
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.