Magento Forum

MySQL error on Import database backup to a new server
 
sanbainto
Jr. Member
 
Total Posts:  2
Joined:  2009-03-17
 

Hello,

First, i’m spanish so excuse me for my bad english ...

I made a database backup in magento -> system ->tools ->import/export database from localhost (WAMPSERVER). Once on the server on the Internet, when I import the backup from PHPmyAdmin get the error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`ofirep_tienda/#sql-121f_18e12`, CONSTRAINT `FK_CATALOG_CATEGORY_EMTITY_INT_STORE` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE)

I added on the .SQL file at the beginning / final the lines:
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’;
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;

I made the database backup from a PHPmyAdmin and the error is the same !

What can i do to import magento database on the new server?

Thank you a lot !
Dams.

 
Magento Community Magento Community
Magento Community
Magento Community
 
sanbainto
Jr. Member
 
Total Posts:  2
Joined:  2009-03-17
 
sanbainto - 30 April 2009 07:00 AM

Hello,

First, i’m spanish so excuse me for my bad english ...

I made a database backup in magento -> system ->tools ->import/export database from localhost (WAMPSERVER). Once on the server on the Internet, when I import the backup from PHPmyAdmin get the error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`ofirep_tienda/#sql-121f_18e12`, CONSTRAINT `FK_CATALOG_CATEGORY_EMTITY_INT_STORE` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE)

I added on the .SQL file at the beginning / final the lines:
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’;
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;

I made the database backup from a PHPmyAdmin and the error is the same !

What can i do to import magento database on the new server?

Thank you a lot !
Dams.

any ideas?

 
Magento Community Magento Community
Magento Community
Magento Community
 
nmax
Jr. Member
 
Total Posts:  2
Joined:  2009-05-14
 

Hi,
A lot of users seem to have experienced this issue. Please see the instructions on the following post if that helps.

http://www.magentocommerce.com/boards/viewthread/32194/

 
Magento Community Magento Community
Magento Community
Magento Community
 
nmax
Jr. Member
 
Total Posts:  2
Joined:  2009-05-14
 

Hi,

Just have tested it myself. Here is what you need to do. Once you take a backup using PhpMyAdmin, before importing it again, do the following

-- add this above the first line

SET FOREIGN_KEY_CHECKS = 0;

-- add this below the last line

SET FOREIGN_KEY_CHECKS = 1;

This should work.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Pooch
Member
 
Total Posts:  70
Joined:  2008-05-15
Danville, CA USA
 

That did the trick! Thanks.

 
Magento Community Magento Community
Magento Community
Magento Community
 
gvn11
Jr. Member
 
Total Posts:  5
Joined:  2009-07-13
 

nmax, Thank you so much for this fix. Saved me a lot of trouble.

 
Magento Community Magento Community
Magento Community
Magento Community
 
furqan
Jr. Member
 
Total Posts:  12
Joined:  2008-05-27
 

Thank you so much “nmax”

 
Magento Community Magento Community
Magento Community
Magento Community
 
lynnspain
Member
 
Avatar
Total Posts:  65
Joined:  2008-08-07
Baleares, Spain
 

Hi, I wish I had found this earlier. I did a new install ( changing servers) then tried to import the data via myphp and got the errors stated here, I then emptied all the tables and tried again, eventually I got things to import but am now worried that I have messed things up. the site seems to work and the data is all there, the admin side seems to be ok too. Can anyone see potential dangers in the future here. I am a bit concerned about putting a lot of work into the is site only to discover this move created a problem. My main concern is the encryption key.

 
Magento Community Magento Community
Magento Community
Magento Community
 
greenestmarketing
Jr. Member
 
Total Posts:  3
Joined:  2008-09-23
 

Nmax—Your fix is amazing.  For almost 6 months now I’ve had two sites I’ve been trying to migrate to a different server and this fix combined with one other one that fixed another issue I was having finally got the sites up and running without any errors.  For the benefit of others, I have added the following lines to the beginning of each .sql database export:

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

and then I added this line to the very last line of the .sql file:

SET FOREIGN_KEY_CHECKS = 1;

So far, it’s 2/2 importing the databases without error and allowing the archived files to just connect and work on the new server.

 
Magento Community Magento Community
Magento Community
Magento Community
 
nicZcool
Jr. Member
 
Avatar
Total Posts:  4
Joined:  2009-04-28
 

This tricks solved my issue. Credits for nmax. Thanks dude.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Philster53142
Member
 
Total Posts:  67
Joined:  2009-10-03
 

Sorry if this is a stupid question but how do I add this information? I opened it up in wordpad and it’s really busy looking. I don’t really know when the “first line” starts and the “last line” ends. Some of it looks commented out and it looks like a database for a while then scroll down and it looks like commented out into and then it starts to look like a database again. I just don’t want to do it wrong.

really confused...

Insert at beginning
1 SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";
2 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
3 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
4 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
5 SET NAMES utf8;
6 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=\’NO_AUTO_VALUE_ON_ZERO\’;
9 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

Insert at End.

1 SET SQL_MODE=@OLD_SQL_MODE;
2 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
3 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
4 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
5 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
6 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
7 SET SQL_NOTES=@OLD_SQL_NOTES;

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top