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.sql
script available from the MySQL sample databases. It's a simple database with three tables: Country
, City
, and CountryLanguage
. Both City
and CountryLanguage
have foreign keys referencing the Country
table's Code
field.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=NDB
before re-importing the script to a new databaseDumping 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.
I have a question - whenever I try to add a datetime / timestamp / time datatype to my MySQL Cluster, I get:
SQL Error (1296): Got error 706 'Inconsistency during table creation' from NDBCLUSTER
Do you have any idea what this could mean?
Which version and exactly what statement are you using? Is it NDB API or SQL?
I believe it's the NDB API - if it helps, I use ndb_mgmd to start management server, ndbd to start NDB storage server and service mysql start to start the SQL API. Does that tell you?
What version of MySQL Cluster software are you using? and what's the statement you're using to create the table (or add the column) to the clustered table?
Management Server:
MySQL-Cluster-gpl-management-7.1.26-1.rhel5.x86_64.rpm
MySQL-Cluster-gpl-tools-7.1.26-1.rhel5.x86_64.rpm
NDB Server:
MySQL-Cluster-gpl-storage-7.1.26-1.rhel5.x86_64.rpm
SQL Server:
MySQL-Cluster-client-gpl-7.3.2-1.rhel5.x86_64.rpm
MySQL-Cluster-server-gpl-7.3.2-1.rhel5.x86_64.rpm
MySQL-Cluster-shared-gpl-7.3.2-1.rhel5.x86_64.rpm
-- would you oppose communicating by email?
For anyone who reads this - turns out it was because my SQL node was using a different(newer) version than my NDB nodes and Management nodes. Thanks a lot for the help, Jeremy.
You're welcome!
Hi.
I edited the sqldump with engine=ndbcluster option. Now when I restore the sql dump. It give the following error. ERROR 1114 (HY000) at line 2064: The table 'abc' is full.
I have increased the DataMemory and IndexMemory to 1G, and my DB is in MBs. It still gives error. Please guide.
I have a question,when I run
ALTER TABLE CLIENT_CONFIGS ADD CONSTRAINT `FK_CLIENT_CONFIG_HOST` FOREIGN KEY (`HOST_ID`) REFERENCES `HOSTS` (`HOST_ID`);
ERROR 1506 (HY000): Foreign key clause is not yet supported in conjunction with partitioning
How can I do?
thinks
This is most likely a version problem. Make sure you're using a recent version of the MySQL Cluster binaries (especially if you've got user-defined partitioning on the table in question).
my mysql cluster tar is :
mysql-cluster-gpl-7.2.17-linux2.6-x86_64.tar.gz
Is this version?