You will need to place the following at the head of your dump file.
SET FOREIGN_KEY_CHECKS=0
Also, in MySQL 4.1, mysqldump does this automatically. It puts out the following at the beginning of the dump file:
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
And this at the end (in case the dump file is included as part of some larger job):
/*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */;
-----Original Message----- From: sean peters [mailto:[EMAIL PROTECTED] Sent: Friday, December 19, 2003 10:28 AM To: [EMAIL PROTECTED] Subject: mysqldump table order with foreign keys
Hey all,
I'm trying to dump the structure of a database using:
mysqldump -d myDatabase > structure.sql
Which works fine in getting the table create statements, but, how do i get mysqldump to put the creates in an order that will allow replication with foreign key concerns.
For instance if i have tables as follow: CREATE TABLE Parent ( Parent_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ... )Type= InnoDB;
CREATE TABLE Child ( Child_ID ..., Parent_ID INT NOT NULL, ... FOREIGN KEY (Parent_ID) REFERENCES Parent (Parent_ID) ON DELETE CASCADE )Type=InnoDB;
Etc.
Mysqldump may dump the create statement for table Child before dumping the create statement for Parent. Then, when re-creating the database, i get errors, because the create for table Child references the foreign key in the
table parent, which has not yet been created.
I swear that i've seen a solution to this somewhere, but for the life of me i have not been able to find the answer (now that i need it)
Thanks much, sean peters [EMAIL PROTECTED]
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
