SQL Code

Avoiding Errors When Restoring a MySQL DataBase

If you're ever trying to restore a MySQL database from a backup file, you may receive the following error: 051005 14:27:04 Error in foreign key constraint creation for table `table_name`. This is most likely to the fact that you are using InnoDB table formats, and you have foreign key contraints set up. Here's how to get past it.

So basically the error is caused by the foreign key checks, looking for data that hasn't been created/restored yet. Here's how to get past it. Let's assume you've backed up the database to a file called 'my_db.bkp'. Open the file, and insert this line:


SET FOREIGN_KEY_CHECKS=0;

Make sure it's at the top of the backup file, before any data creation steps, such as in the following:


SET FOREIGN_KEY_CHECKS=0;

--
-- Table structure for table `bucket_types`
--

CREATE TABLE bucket_types (
  id mediumint(4) NOT NULL default '0',
  table_id mediumint(2) NOT NULL default '0',
  description varchar(20) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY table_id (table_id),
  CONSTRAINT `0_42` FOREIGN KEY (`table_id`) REFERENCES `bucket_types_tables` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB COMMENT='describes the bucket';

Then you can restore the database easily with the following line:

mysql –user=root –pass=rootpass my_db < c:\my_db.bkp [/code]

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image