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]
