Hello.
With 'set foreign_key_checks=0' creation of the table works on my 4.1.11. Execute: set foreign_key_checks=0; Then create `shift` and all corresponding tables and set foreign_key_checks=1; "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > 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]
