"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote on 05/01/2005 07:14:57 PM:
> I created a mysqldump with --quote-names option, but I get this > error:C:\xampp\xampp\mysql\bin>mysql -uroot db2 < test5. > txtERROR 1005 at line 54: Can't create table '.\db2\shift.frm' > (errno: 150)C:\xampp\xampp\mysql\bin>Line 54 is the CREATE > TABLE... line of the following bit of SQL from the file, and the > original database created on the same server works fine.How is it > that mysqldump can successfully create the sql file, but the code it > has generated fails to create a new database. Can any one say what > might be wrong with the foreign key format (err 150) or mysqldump.exe > I have MySQL 4.0.18 and mysqldump Ver 9.10 Distrib 4.0.17, for > Win95/Win98 (i32) as supplied to me.Andrew HCREATE TABLE `shift` ( > `staff` char(20) NOT NULL default '', `shift` char(1) NOT NULL > default '', `client` char(20) default NULL, `role` char(1) default > NULL, `on` date NOT NULL default '0000-00-00', PRIMARY KEY > (`staff`,`shift`,`on`), KEY `staff` (`staff`,`role`), KEY `shift` > (`shift`), KEY `client` (`client`), KEY `role` (`role`), > CONSTRAINT `shift_ibfk_1` FOREIGN KEY (`staff`, `role`) REFERENCES > `staffrole` (`uniquename`, `role`), CONSTRAINT `shift_ibfk_2` > FOREIGN KEY (`shift`) REFERENCES `shifttype` (`shift`), CONSTRAINT > `shift_ibfk_3` FOREIGN KEY (`client`) REFERENCES `client` > (`uniquename`), CONSTRAINT `shift_ibfk_4` FOREIGN KEY (`staff`) > REFERENCES `staff` (`uniquename`), CONSTRAINT `shift_ibfk_5` > FOREIGN KEY (`role`) REFERENCES `roletype` (`role`)) TYPE=InnoDB; It may be due to the fact that one of the other tables that this table is trying to create a Foreign Key constraint with doesn't exist yet. The best way to get more information about an error code 150 is to run SHOW INNODB STATUS when you get it. In your case I recommend copying your SQL script one command at a time into the MySQL client (the win32 version accepts multi-line pastes very well but each line must be less than 255 characters long. It's a windows thing...) Then when you get the error again issue the command, SHOW INNODB STATUS. The results of that command should help you to diagnose this issue. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
