I backup a db using mysqldump, but when I come to execute the sql file on an
empty db I get this
C:\xampp\xampp\mysql\bin>mysql -uroot db2 < test3.txtERROR 1005 at line
54: Can't create table '.\db2\shift.frm' (errno: 150)
C:\xampp\xampp\mysql\bin>
I have tried to fix the foreign key constraint (error 150), but to no avail, I
cannot see what is wrong with it nd anyway if I created this in the same mysql,
then why doesn't it generate code that is valid?
Here's the create that it fails on:
CREATE 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;
I've tried back ticks on all the `on` column references, but still get same
error.
I have MySQL 4.0.18 and the mysqldump is :mysqldump Ver 9.10 Distrib 4.0.17,
for Win95/Win98 Is this the problem? If so where do I get the right version of
mysqldump? Later ones may not be bkwds compat.
Can any-one help?
Andrew H
