errors while attempting to migrate data from innodb|myisam to ndbcluster

[Jim Conner]

So, I've done pretty well at getting this ndbcluster going so far. My setup is as follows:

lb:vip-> api1 and api2
host1: mgm
host2: ndb1
host3: ndb2+api1
host4: ndb3+api2

Now, this environment is still a test environment but the idea is to roll it into production after its fully tested. We have a current prod environment from which I'm getting my data to test the new environment. Essentially I'm just trying to import an old data snapshot from prod (that was created using mysqldump).

A little history
----------------

I first attempted to change all the engine lines in the sql dump (from 4.0.19 to 5.0.19 using a perl script I wrote) to change from innodb or myisam (with the exception of the mysql table) to ndbcluster and then import that into the new db. Although the sql file was changed properly, the file never imported correctly. At the time this could have been because of the cluster database not being set up properly.

I decided to try and import the data differently. I decided to simply import the data into one of the API nodes exactly as it was in the original database and then change the engines in the databases to ndbcluster. GENIUS! No, really, no need to clap. Ok. So, in my attempts at taking this seeming back-door I've run into a few snags. Surprised? Don't be. Apparently, others have done this genius thing and run into similar problems. However, in my attempts at learning from other peoples' troubleshooting techniques, I've learned mine seems to be a bit unique. Hopefully, I'm wrong.

So, now, I get through about half of the "import" of my data into the cluster when I start running into these nifty gems of errors:

--- snip ---

[root@tlbfc04db03 mysql]# /usr/local/sbin/engine2ndbcluster
db we're connected to: my_db
Altering the following tables:
table1
table2
Using sql: 'alter table table1 engine = ndbcluster' on my_db.table1
DBD::mysql::st execute failed: Can't create table './my_db/#sql-4a71_5.frm' (errno: 155) at /usr/local/sbin/engine2ndbcluster line 56.
Can't create table './my_db/#sql-4a71_5.frm' (errno: 155) at /usr/local/sbin/engine2ndbcluster line 56.
-1 were affected...
Using sql: 'alter table table2 engine = ndbcluster' on my_db.table2
DBD::mysql::st execute failed: Table '#sql-4a71_5' already exists at /usr/local/sbin/engine2ndbcluster line 56.
Table '#sql-4a71_5' already exists at /usr/local/sbin/engine2ndbcluster line 56.
-1 were affected...

db we're connected to: my_db2
Altering the following tables:
table1
table2
Using sql: 'alter table table1 engine = ndbcluster' on my_db2.table1
DBD::mysql::st execute failed: Cannot delete or update a parent row: a foreign key constraint fails at /usr/local/sbin/engine2ndbcluster line 56.
Cannot delete or update a parent row: a foreign key constraint fails at /usr/local/sbin/engine2ndbcluster line 56.
-1 were affected...
Using sql: 'alter table table2 engine = ndbcluster' on my_db2.table2
DBD::mysql::st execute failed: Cannot delete or update a parent row: a foreign key constraint fails at /usr/local/sbin/engine2ndbcluster line 56.
Cannot delete or update a parent row: a foreign key constraint fails at /usr/local/sbin/engine2ndbcluster line 56.
-1 were affected...

--- snip ---


So, you see, there are two separate errors here. I have no clues about the errno: 155 and 156 (got those too). One thing I've read on the foreign key constraint is that the there is a referential integrity thing (see: http://sql-info.de/mysql/referential-integrity.html) according to my findings so far. The only thing that isn't consistent with this is that I have some tables that don't have any foreign keys at all...but alas, I admit, I'm not a guru at some of this stuff...soooo.

Here's one show create table that threw the infamous error above:

CREATE TABLE `table_name` (
`userid` varchar(32) NOT NULL default '',
`dcode` varchar(32) NOT NULL default '',
`vin` varchar(32) NOT NULL default '',
`prize_value` varchar(32) default NULL,
`game` varchar(32) NOT NULL default '',
`airline` varchar(32) NOT NULL default '',
`acct` varchar(32) NOT NULL default '',
`Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`Confirmed` enum('Yes','No') NOT NULL default 'No',
`lineid` int(11) NOT NULL auto_increment,
PRIMARY KEY (`lineid`),
UNIQUE KEY `dcode` (`dcode`,`vin`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Here's another:

CREATE TABLE `table_name_1` (
`unique_id` varchar(25) NOT NULL default '',
`fullvin` varchar(17) NOT NULL default '',
`vin_last8` varchar(8) NOT NULL default '',
`pdn` varchar(5) NOT NULL default '',
`bca` varchar(10) NOT NULL default '',
`spb` varchar(10) NOT NULL default '',
`contract_date` date NOT NULL default '0000-00-00',
`purchase_date` date NOT NULL default '0000-00-00',
`nu` varchar(5) NOT NULL default '',
`car_year` varchar(4) NOT NULL default '',
`car_make` varchar(25) NOT NULL default '',
`car_model` varchar(25) NOT NULL default '',
`standspecrate` varchar(10) NOT NULL default '',
`lob` varchar(5) NOT NULL default '',
`tier` char(2) NOT NULL default '',
`term` char(3) NOT NULL default '',
`rate` varchar(6) NOT NULL default '',
`eligible` varchar(6) NOT NULL default '',
`notes` varchar(50) NOT NULL default '',
`contract_features` varchar(25) NOT NULL default '',
`multi` char(1) NOT NULL default '',
`date_upload` date NOT NULL default '0000-00-00',
`date_confirm` date NOT NULL default '0000-00-00',
`date_buy` date NOT NULL default '0000-00-00',
`lineid` int(11) NOT NULL auto_increment,
PRIMARY KEY (`lineid`),
UNIQUE KEY `unique_id` (`unique_id`),
KEY `fullvin` (`fullvin`),
KEY `vin_last8` (`vin_last8`),
KEY `date_buy_idx` (`date_buy`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

So, at least in these two examples it seems that I have local keys and I don't see why a constraint issue would arise for something local to that table. Am I correct?


Additional Information
----------------------

So, apparently, even though mgm shows that all nodes are started and the api nodes are seen as connected. However, if I connect to API2 it doesn't see ANYTHING as if its not talking to the mgm. What's up with that? That could be a problem, don't you think? :) The my.cnf has the nbdcluster line and the nbd-connectstring for both the daemon and the client sections.


Help would be greatly appreciated. TIA peoples.

- Jim



Edited 2 time(s). Last edit at 03/27/2006 02:14PM by Jim Conner.