Luciano
I am confused... As far as I can tell, the set foreign_key_checks=0; is used with the
load data infile command. I am not using this command to restore the database. The
mysqldump command creates a file with the data and schema. I restore it to a new
instance that just has the mysql database using:
unix$> mysql --port=port --socket=socket -p < archive.sql
So, I am not sure where I would insert this line nor if it would work. Should I
insert it in the "archive.sql" from the previous example?
Can I use the load data infile to restore the file I generated?
Also, in the mysqldump command I used the "--disable-keys" command, believing this
would correct the problem. Do you know why it does not?
I know I asked a bunch of questions, thanks for whatever you can offer!
Gabe
-----Original Message-----
From: Luciano Barcaro [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 10:03 AM
To: Tucker, Gabriel
Subject: Re: InnoDB - Foreign Key - Error 150.
Put in your script:
set foreign_key_checks=0;
in the first line.
mysqldump dumps tables in alphabetical order.
Tucker, Gabriel wrote:
> Hello All:
>
> I am having a problem with both V4.0.16 and 4.0.18. Let me explain:
>
> I have a database with two InnoDB tables in v4.0.16. I backup up this database
> every night using the following command:
>
> mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt
> --port=$1 --socket=/bb/bin
> /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p >
> /bb/bin/mysql/backups/archive_$1.sql
>
> I went to restore it the other day and I got this error:
>
> ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno:
> 150)
>
> I thought this might be a version issue, so I copied all the database files to
> another location, and started it using v4.0.18. I then dropped the foreign key
> constraint (no errors) and recreated it (no errors). I backed up the database. And
> when I went to restore it, I got the same error.
>
> The schema [after I deleted and re-added the constraint]:
>
> CREATE TABLE cur_reject_tk_sum (
> cur_reject_tk_id int(11) NOT NULL auto_increment,
> contrib_swift_cd char(4) NOT NULL default '',
> object_id char(8) binary NOT NULL default '',
> tick_date date NOT NULL default '0000-00-00',
> object_type_cd smallint(6) NOT NULL default '0',
> num_received int(11) default NULL,
> num_rejected int(11) default NULL,
> num_spikes_contrib int(11) default NULL,
> num_spikes_compos int(11) default NULL,
> num_spread int(11) default NULL,
> num_filter int(11) default NULL,
> num_delayed int(11) default NULL,
> num_maybe int(11) default NULL,
> num_diff int(11) default NULL,
> num_bid_gt_ask int(11) default NULL,
> num_ask_no_bid int(11) default NULL,
> num_double_bid_ask int(11) default NULL,
> time_first_reject time default NULL,
> time_last_reject time default NULL,
> PRIMARY KEY (cur_reject_tk_id),
> UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id),
> UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date),
> KEY xif1cur_reject_tk_ (object_type_cd),
> KEY xie1cur_reject_tk_ (tick_date),
> KEY xie2cur_reject_tk_ (object_id),
> CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES
> `object_type` (`object_type_cd`)
> ) TYPE=InnoDB;
>
> --
> -- Table structure for table `object_type`
> --
>
> CREATE TABLE object_type (
> object_type_cd smallint(6) NOT NULL default '0',
> descr varchar(254) default NULL,
> PRIMARY KEY (object_type_cd),
> UNIQUE KEY xpkobject_type (object_type_cd)
> ) TYPE=InnoDB;
>
> Any help would be appreciated!
>
> Thanks - Gabe
>
> <><><><><><><><><><><><><><><><><><><><>><><>
>
> "Arise Arise A Rose A Rose"
>
> Gabriel Tucker
>
> 609 750 6668 - P
> 646 268 5681 - F
>
> <><><><><><><><><><><><><><><><><><><><>><><>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
