Re: Fw: mysqldump generates invalid code
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 '-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 >> >'-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]
Fw: mysqldump generates invalid code
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 '-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 > >'-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]
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 >'-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]
mysqldump generates invalid code
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 '-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
