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]