This article has been closed for comments on March 17th, 2009. Approach below has been shown to work. If you experience unexpected results following this approach, then please revise the search/replace string you provided while going trough .sql file. Keep in mind real root url path to dev/live Magento installation.
Maybe this post should have been named Magento MySQL table export, import, and 1452 error. Here’s why. For the last hour and a half I’ve been trying to transfer Magento database from one server to another. I’ve been getting MySQL 1452 error during the import all the time. My export gone pretty smoothly, no errors at all. I’ve exported using default settings of my WAMP servers phpMyAdmin application. Moving a database should be consisting of a two simple step. Step 1: Export your database to a file. Step 2: Import your database from a file to new MySQL server. Sounds easy, right. Well it is. At least it should be.
I wonder is it my bad karma, or what is it when it comes to Magento? Personal lack of experience? Whatever it is, result can sometimes be quite time consuming. Anyway here is my solution to MySQL 1452 error problem and entire process of moving magento from development server to live server.
First, make a backup of entire Magento database with all the default phpMyAdmin options. Backup your database to .sql file.
Let’s suppose your development site is located at http://dev.site.com and your live site is located at http://livesite.com.
Second step for you would be to open the backup file you created at first step and do Search/ReplaceAll from “dev.site.com” to “livesite.com”. Magento stores complete url paths inside the database. Therefore you’ll end up with database full of url paths. My backup file had total of 17300 occurrences of “dev.site.com”. Huge number.
Anyway this is a must do. There is no point at importing database to new server if you haven’t done this replacement since Magento pools links from database. So if you haven’t done replacement and have done import then basically your Magento on http://livesite.com would look for links of http://dev.site.com like http://dev.site.com/some-product-name. This off course isn’t good.
If you search/replaced your .sql file do not rush to import it. If you try to import file as is, you’ll most likely get some errors from MySQL concerning foreign key constraint an so on. I lost about 3 hours of mine time testing and checking things out to get around this. Here’s how. Open your .sql backup file again in your favorite editor and do the following steps.
Place these lines of SQL code on very top of the .sql file:
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;
Place these lines of SQL code on very end of the .sql file:
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;
Now you’re ready to import your .sql file to live MySQL database. Or not? I suggest you ZIP your .sql file and then do import of .zip file trough phpMyAdmin. At least you’re upload will be faster. For example my .sql file was 8MB in size while .zip was 500KB. During the import you should not get any error messages.
Ok, for now you should have copied entire root folder content of your Magento from dev.site.com to root folder of livesite.com. There is only one more thing to do before you can open you’re page in browser.
Having in mind that the root of your Magento is http://livesite.com you now need to open http://livesite.com/app/etc/local.xml file and modify it’s database connection information accordingly to your new database in livesite.com. Save the changes open your browser to livesite.com. Your Magento should be working.
This guide should get you up n running if you’re moving you’re site from one hosting to another. There are few more things to keep in mind it the site is not working. First you need to check folder write permissions trough FTP. Folders like /app, /app/etc, /var, /var/log, /var/session and so on, need to have appropriate access rights (755, 775, 777). Play around with it. There is one more thing that can cause problems. That is .htaccess file. If you’re moving a file from one site to another, and dumping it into same folder, then you probably won’t need to change this. But if you’re dumping it into some sub folder like http://livesite.com/store/ then you’ll have to write following into .htaccess file “RewriteBase /products/” and don’t forget your .sql Search/replace all to set it now replace of “http://dev.site.com” with “http://livesite.com//products/” since you’re dumping Magento into sub folder.
Hope you make it; I did. It took me 7 hours to do something it takes 2h max. However, I’m ready for the round two now.
Feel free to post comments. I could use some community feedback 
I can’t thank you enough for this. How did you figure out the SQL fix?
I knew something related to foreign keys was throwing errors so my first thought was “Is there any way to turn off the checking of foreign keys”.
Since my knowledge of SQL is not that impressive (for now) I started googleing… Unfortunately I did not reference the site with this SQL trick.
I’m glad it helped you.
Well, I think I’ve just won about 7 hours now I have read your topic
…
I’m developping on Magento 1.1.6, is your fix working with this version too? I guess yes, but I’m still wondering. So…
Anyway, thank you very much
.
Again, I’m glad someone finds this post useful. I believe it should work for any version and any other CMS that has a database full of foreign key constraints. It basically tells the database to turn off some checks at the beginning of the file, do some SQL query then turn stuff back on.
As I said, this code is not actually mine. I’m just sorry for not writing it down the source from which I picked it up.
However, entire process described here is based on mine problems while trying to move the Magento to development server. So you can bet this method is working
All the best to all of you Magento guys and girls.
Super,and many thanks. Just done it on a clean 1.1.6 – even without sample data it bugs – tables reference an inexistant store “0″. Since the Magento databases lack integrity anyone know if this is going to create problems down the line. For exemple, cascading deletes not working?
You should be able to skip the [Search/ReplaceAll from “dev.site.com” to “livesite.com” ] step by changing your site’s secure and unsecured links and clearing the DB cache before exporting the database. See here: http://www.webdesignlabs.co.uk/?p=3
Thanks for the SQL code tip. It solved the error problem for me.
Thank you for your input Peter
Hi,
Thank you for the guide, made my day!
I have followed it and it seemed to work but I found out I forgot the / at the end of the base url which resulted in broken links and no templates (naturally). So I deleted the database from the new server, re-did the search and replace and uploaded it again through phpMyAdmin but this time I get the 500 Internal Server Error.
This usually has to do with rights and maybe the .htaccess file right? I have tried, as you suggested, to fiddle around with the access rights but without any change.
Just wondering if you or anyone else has any suggestion?
Thank you again for sharing your hard earned knowledge!
I found that if I delete the .htaccess-file it works perfectly! But that seems quite bad…?
Same situation here with Lisa, “500 Internal Server Error” while accessing to admin. My page in front page wont load up,only text HOME PAGE and basic skins. any suggestion?
The frontpage i cant get to see anything it just gives me a blank page
Not sure if it’ll post, but I’ll share with everyone a little shell script I wrote to automatically dump my magento mysql db and add these pieces of SQL code to the header and footer of the dump. I found myself having to open 30 meg sql files with Textmate just to backup the db… and I’m still in development! Just make sure you include your info at the top of the file in the place of all the text. This will create an .sql file your current working directory.
#!/bin/bash
USERNAME=
PASSWORD=
DATABASE=
SQLFILE= magento.sql
echo -e ‘SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;\nSET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;\nSET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;\nSET NAMES utf8;\nSET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;\nSET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\nSET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’;\nSET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;’ > $SQLFILE
echo “Creating new .sql file with proper SQL code at the”
echo “top to prevent foreign key constraint errors.”
echo “”
mysqldump –user=$USERNAME –password=$PASSWORD $DATABASE >> $SQLFILE
echo “Dumping the $DATABASE database to $SQLFILE.”
echo “”
echo -e ‘SET SQL_MODE=@OLD_SQL_MODE;\nSET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;\nSET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;\nSET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;\nSET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;\nSET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;\nSET SQL_NOTES=@OLD_SQL_NOTES;’ >> $SQLFILE
echo “Done! The $DATABASE database has been successfully”
echo “dumped to $SQLFILE along with the necessary SQL”
echo “code at the beginning and end of the file.”
echo “”
Whoa… make sure you watch out for the smart quotes in my last post. I guess wordpress did that…
Hi Kev… thanks for submitting this script.
Regarding the search and replace of dev.site.com having over 17000 instances: Do you really have to replace the bulk of it in the LOG table? I think it is just a history which won’t actually be used to reference the old server.
Just a thought. Great job on the tutorial. Thanks.
Took me 15 minutes to do it all – works like a charm. Thank you SO much for blogging the solution.
I’m playing around with 1.1.8 and needed to nuke the sample data install and then start from scratch. Google got me to your page when I searched on the error. I couldn’t drop some of the tables. As I only have one db on my host (normally not a problem), I was starting to get bummed out about that.
Your code above and below a lot of DROP TABLE ….; lines in my sql file did it! I ran the commands by doing a “backup” of the nuke_magento.sql file I made.
1 million thanks for sharing the solution!
Thank you for this solution, this gave me a good vision on this topic.
Let me share my experience: I followed your description, but first tried it on my own desktop server to move from one directory to another one. This went fine.
Next, I tried to do the same thing with a (shared) live server. It just didn’t work. I mean importing tables over an already installed example gave me this error code in PHPMyadmin: FOREIGN KEY Constraints.
So I started to go through, tried again and again until I realized that my desktop servers SQL used a different storage engine than the one online. Mine was MyISAM, and the other was InnoDB, so I had to convert mine to InnoDB before inserting it to the online DB. (First, I enabled InnoDB in my SQL’s .ini file, and inserted an SQL file from the Magneto site to convert necessary tables from MyISAM to InnoDB)
Hope this helps those guys in my situation
Well.. one more thing: if you’ve already installed any modules and then move your site, don’t forget to get rid of your old ‘/downloader/pearlib/pear.ini’ file and clean cache. (This file contains your old ‘downloader’ library path – had a good few hours figuring out where the hell my other extensions were)
Thank you for the tip
Hi Branko,
)
i have some ideas to check out for you. For backup database, what about try build in Magento function in administration->system->tools->backups. When you select to backup database Magento makes for you gziped sql file. This file you can import in phpmyadmin. Dont need make any changes in file, for me it worked 100 %. I dont know if somebody mention it, but you also have to change base url in core_config_data table in db. Thanx also for your articles on Inchoo
Great work, thanks for this!
YOU ARE A GOD!!! .. lol .. thank you! worked perfect ..
@mike305
LOL … I need to write a new article, this one is somewhat obsolete now. There are better methods
Hi,
I down the site from development server & upload it on live server.
When I open live server link, it is redirect to old – development link.
I cant understand why this happen, kindly tell me if anyone know this.
Hi vipul
I don’t won’t so sound harsh… but, have you even read this article? Let me quote my self:
“Second step for you would be to open the backup file you created at first step and do Search/ReplaceAll from “dev.site.com” to “livesite.com”. Magento stores complete url paths inside the database. Therefore you’ll end up with database full of url paths. My backup file had total of 17300 occurrences of “dev.site.com”. Huge number.”
Thanks Bronko,
I dont know how to remove that path from database.
Can you tell me how can I do that .
If you give me tht querry, then it also good for me
Pleasssssse read the article!!!
“Second step for you would be to open the backup file you created at first step and do Search/ReplaceAll from “dev.site.com” to “livesite.com””
1) You create database backup (somefile.sql)
2) You open somefile.sql and do Search/ReplaceAll (search for old url and replace with new)… save as new file: newsomefile.sql
3) You import newsomefile.sql to new database
This is a “must have” if you’re a developer. Thank you so much for posting it!
Cheers,
Stretchr
I am facing a problem when i uploaded the files in live server. I have created a module ” customerPayment” by module creator. Then i customized as per user requirements. It works fine in local server as well as paylite server which is windows server but when i uploaded the files in live server ( i.e client server ) it gives 404 error for that perticular module. i could not access that module from backend.
Manojit… to be short, this approach in article is working 100%.
I cannot know what you have done, copied, moved an so on in your installation.
Everything in Magento has tendency to take wast amount of time when even a minor problem arises. I wish I can help you more than just pointing this out, but there is no way I can even assume what the problem is with your Magento unless I take at least hour or two to get familiar with what exactly you are doing. Hope you understand.
Wish you all the best in resolving the issue.
Branko,
You’ve saved me loads of time with this article.
Just a small point, it is always easier doing database operations either on the command line if you have access, or using the MySql administrator applications, again if you have access. PhpMyadmin can sometimes time out on larger databases.
I agree that everything in Magento takes a long time. I think as a developer, it has been thoroughly constructed, and is a world away from systems like osCommerce which you hack to get things done, but the pay off is that is quite long winded to do anything in Magento. Further it isn’t helped by the fact that documentation for a developer is limited and a lot of the times incorrect.
If I could sum my experience of Magento in one word it would be ‘frustrating’.
like aaron above, when i follow this all i see for my honepage is a BLANK white screen. However, if i add /magento/ and view source, i find that all my css/js is coming from the root.
the files are there but the homepage is blank! :S
I’m officially closing this this article for comments.
Approach listed in this article is WORKING, as confirmed by others with positive feedback comments. Besides, I use it on daily basis in Magento development.
Issues like blank screens and so on are caused by… Well, turn on the PHP error reporting and you’ll see what’s causing them.
Important thing is to watch on the search replace string. If you installed Magento in some subdirectory of your site then please keep in mind full root url path. Simple as that.
Wish you all the best with Magento development.