Thank you for your help. I really appreciate it. I created the dump with --quote-names option, but this does not solve the problem unfortunately. I still get error:
C:\xampp\xampp\mysql\bin>mysql -uroot db2 < test5.txt ERROR 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, and the original database works fine. I want to upgarde, but unfortuantely my employer refuses to allow upgrade or to buy a fully supported database server. Is there perhaps a known defect recorded for this, which I could use as leverage. Andrew H 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; > ----Original Message---- > From: [EMAIL PROTECTED] > Date: 30/04/2005 13:52 > To: mysql@lists.mysql.com > Subj: Re: mysqldump generates invalid code > > Hello. > > Make the dump using --quote-names option for mysqldump. Does it solve > the problem? You have an old MySQL version. I strongly recommend you to > upgrade. > > > > > >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 > > > >"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.NET http://www.ensita.net/ > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET > <___/ www.mysql.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] net > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
