Hi Guy's, first: Your are correct it is contraints: bash-2.05a$ perror 150 Error code 150: Unknown error: 150 150 = Foreign key constraint is incorrectly formed
Second you could also try: set foreign_key_check=0; at the beginning of the restore file. set foreign_key_check=1; at the end! Best of luck, Ken ----- Original Message ----- From: "Marvin Wright" <[EMAIL PROTECTED]> To: "Tucker, Gabriel" <[EMAIL PROTECTED]>; "Mysql General (E-mail)" <[EMAIL PROTECTED]> Sent: Tuesday, May 04, 2004 10:22 AM Subject: RE: InnoDB - Foreign Key - Error 150. > Hi, > > I don't think mysqldump takes foreign key constraints into account when > dumping them. > You could specify the tables that you want when you dump so you get the > correct order. > > e.g. > > mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys -- opt > --port=$1 --socket=/bb/bin/mysql/sockets/mysql.sock.$1 > --master-data <DB_NAME> object_type cur_reject_tk_sum > -u mysql -p > /bb/bin/mysql/backups/archive_$1.sql > > I dont know your database name do you would have to substitute that. > this should create the dump in the correct order for your restore. > > Marvin. > > > -----Original Message----- > From: Tucker, Gabriel [mailto:[EMAIL PROTECTED] > Sent: 04 May 2004 15:13 > To: Mysql General (E-mail) > Subject: RE: InnoDB - Foreign Key - Error 150. > > > Marvin > > I believe that is the problem with the restore. When I create the archive > file using the mysqldump command and options previously listed, I get the > create table in the order listed below and thus, the foreign key constraint > is created on table cur_reject_tk_sum before the object_type table has been > created. > > Now my questions are: > [1] Is the above scenario my problem? > [2] Is so, how can I correct it? Is this a problem with the way I am using > mysqldump? [see commands below] Or, is this a problem with how I am > restoring the database? [Which, I create a "default" mysql database on its > own port and then run from the prompt "mysql --port= --socket= -p < > archive_file.sql" > > Thanks again > Gabe > > -----Original Message----- > From: Marvin Wright [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 04, 2004 9:59 AM > To: Tucker, Gabriel; Mysql General (E-mail) > Subject: RE: InnoDB - Foreign Key - Error 150. > > > Hi, > > Are you creating them in the correct order ? > > object_type must exist before you can create cur_reject_tk_sum otherwise the > foreign key will give errors. > > Marvin > > > -----Original Message----- > From: Tucker, Gabriel [mailto:[EMAIL PROTECTED] > Sent: 04 May 2004 14:46 > To: Mysql General (E-mail) > Subject: InnoDB - Foreign Key - Error 150. > > > 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] > > > ______________________________________________________________________ __ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ______________________________________________________________________ __ > > ______________________________________________________________________ __ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ______________________________________________________________________ __ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > ______________________________________________________________________ __ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ______________________________________________________________________ __ > > ______________________________________________________________________ __ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ______________________________________________________________________ __ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
