load from mysqldump fails with ERROR 1005 (HY000) at line 4567: Can't create table './ecom/tAddress.frm' (errno: 150)
Posted by:
Mike Wood
Date: October 17, 2005 06:15PM
I'm trying to do a simple mysqldump from one server and replicate the structure and data to another server as part of a daily process. The process has worked fine until recently when some InnoDB tables where added with foreign keys.
After I do the mysqldump and move the file I run the dumped fild and get the following error:
ERROR 1005 (HY000) at line 4567: Can't create table './ecom/tAddress.frm' (errno: 150)
I looked into the file created by the mysql dump and discovered that it was failing on the creation of this table:
CREATE TABLE `tAddress` (
`UID_PK` int(11) NOT NULL auto_increment,
`firstName` varchar(100) default NULL,
`lastName` varchar(100) default NULL,
`phoneNumber` varchar(100) default NULL,
`faxNumber` varchar(100) default NULL,
`company` varchar(100) default NULL,
`street1` varchar(200) default NULL,
`street2` varchar(200) default NULL,
`city` varchar(100) default NULL,
`stateProvince` varchar(100) default NULL,
`zipPostalCode` varchar(50) default NULL,
`country` varchar(100) default NULL,
`defaultBilling` tinyint(1) NOT NULL default '0',
`defaultShipping` tinyint(1) NOT NULL default '0',
`memoLabel` varchar(255) default NULL,
`customerUID` int(11) NOT NULL default '0',
`attributeGroupUID` int(11) default NULL,
PRIMARY KEY (`UID_PK`),
KEY `attributeGroupUID` (`attributeGroupUID`),
KEY `customerUID` (`customerUID`),
KEY `address_fn_idx` (`firstName`),
KEY `address_ln_idx` (`lastName`),
CONSTRAINT `FK30D888E0373A000D` FOREIGN KEY (`attributeGroupUID`) REFERENCES `tAttributeGroup` (`UID_PK`),
CONSTRAINT `FK30D888E096605392` FOREIGN KEY (`customerUID`) REFERENCES `tCustomer` (`UID_PK`)
) TYPE=InnoDB;
I tried to create the table manually and got the same error. However, if I removed the "TYPE=InnoDB" or left it there and removed the Constraints then the table was created without a problem.
I understand that there could be an issue with creating constraints on a table when their references doesn't exist yet. However, since I want this process to be automatic and scripted either changing altering the file isn't a real solution.
I tried adding the "-K" option to the mysqldump so it would remove the keys, but still I get this error.
What solution can I use? All I'm trying to do is mysqldump from one machine and run the output onto another machine. Is there anyway to do this without having to manually edit the files each time?
The originating DB is on version 4.0.24-max-log
The receiving DB is on version 4.1.14-max
Thank you for any help,
Mike