X

Learn MySQL with the Curriculum Team

  • April 11, 2013

Converting InnoDB Tables to MySQL Cluster

Jeremy Smyth
Manager, MySQL Curriculum

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

For this example, I'm using the 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.

So how do I do it?

There are two ways to migrate InnoDB tables with foreign keys to NDB.

  1. Dump the database and edit the script so each table specifies ENGINE=NDB before re-importing the script to a new database
  2. 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. 

Join the discussion

Comments ( 14 )
  • Justin Wednesday, July 24, 2013

    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?


  • Jeremy Smyth Thursday, July 25, 2013

    Which version and exactly what statement are you using? Is it NDB API or SQL?


  • Justin Thursday, July 25, 2013

    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?


  • Jeremy Smyth Thursday, July 25, 2013

    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?


  • Justin Thursday, July 25, 2013

    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?


  • Justin Thursday, July 25, 2013

    CREATE TABLE `new_table` (

    `id` INT(10),

    `date_created` DATETIME

    )

    ENGINE=NDB;

    or

    CREATE TABLE `new_table` (

    `id` INT(10) NULL,

    `date_created` DATETIME

    )

    ENGINE=NDB;


  • Justin Thursday, July 25, 2013

    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.


  • Jeremy Smyth Thursday, July 25, 2013

    You're welcome!


  • Rabail Friday, November 29, 2013

    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.


  • Jeremy Smyth Friday, November 29, 2013

    There are many possible causes for a "table is full" error in NDB. To start with, make sure ALL REPORT MEMORYUSAGE reflects your new setting; if not, you might need to do a rolling reconfiguration.

    You might also be able to find solutions by searching the MySQL forums for that error message. This link should help:

    http://forums.mysql.com/search.php?forum_id=25&phorum_page=search&search=%22table+is+full%22&match_type=ALL&author=&match_forum%5B%5D=25&match_threads=0&match_dates=0


  • guest Thursday, September 18, 2014

    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


  • Jeremy Smyth Thursday, September 18, 2014

    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).


  • guest Monday, September 22, 2014

    my mysql cluster tar is :

    mysql-cluster-gpl-7.2.17-linux2.6-x86_64.tar.gz

    Is this version?


  • Jeremy Smyth Monday, September 22, 2014

    Foreign keys were introduced to NDB with version 7.3:

    http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndb-innodb-engines.html


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha