Every now and then a new “something” turns up in Magento that makes me bash my head against the wall 
. Yesterday I had to scenraio where I moved one of my dev installations from one PC to another MAC computer. Anyhow… I kept getting this “duplicate key” errors thrown on Magento page. Although the database export was done properly (with disabled foreign key check and so on…) and my virtual hosts were setup exactly the same was still getting database related error. Here are few more important steps to take if you experience issues while moving Magetno across servers.
-
CLEANUP LOGS SAVED IN DATABSE
TRUNCATE `log_customer` ; TRUNCATE `log_quote` ; TRUNCATE `log_summary` ; TRUNCATE `log_summary_type` ; TRUNCATE `log_url` ; TRUNCATE `log_url_info` ; TRUNCATE `log_visitor` ; TRUNCATE `log_visitor_info` ; TRUNCATE `log_visitor_online` ;
-
DELETE ALL CONTENT FROM /var
Actually, its important only to delete /cache and /session folders - DELETE or RENAME use_cache.ser file stored in /app/etc/ folder
These three little steps can save you some time if you experience issues when moving Magento across servers.
Thanks for your valuable advice.
Furthermore, I found it be important to use the backend generated sql and to uncomment those two lines in the header.
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’;
Sorry for dumb question, but what does it mean: “uncomment those two lines in the header”.
what header? and how to uncomment?
Another note, if you’re doing your SQL dump via phpMyAdmin (not advised of course), you need to add the following to the top of the dump file:
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";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;
And the following to the end:
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;
Almost forgot, it’s also a good idea to clear out the following:
downloader/pearlib/cache/*downloader/pearlib/download/*
media/catalog/product/cache/
media/export/
media/import/
media/tmp/
var/export/
var/import/
var/log/
var/minifycache/*
var/report/
var/session/
var/tmp/
If you’re not using the Fooman Speedster extension, you can ignore the minifycache directory.
Branko: Did you ever experience a problem with attributes: “Flat Catalog module has a limit of 64 filterable and/or sort able attributes”? After moving to another server this problem starts to accur… They say it’s related to Mysql indexes number limit but isn’t it standard to have 64 indexes in table limit?
cheers,
Jernej
I’ve also had my share of wall-head-bangin’ when migrating magento… For me, the best solution to dump the database is via SSH, with the mysqldump command. Everything else (phpmyadmin, magento backup) ended up making me struggle in one point or another. the entire command goes like this:
mysqldump -u [usernamedb1] -p [dbname1] > [filename].sql
After sended to the target server, the following command imports the .sql file into the desired database:
mysql -u [usernamedb2] -p [dbname2] < filename.sql
And then it’s done (and one of the reasons i love SSH ^^). In case of duplicaded entries, i use the search in phpmyadmin (excellent search, by the way) to find and delete the mothersuckin’ thing
Nice work and nice blog, Branko! Keep up like that!
We’re upgrading a magento site from 1.1.8 to 1.3.2.2 and experiencing the same kind of issue…
Magento database migration – when upgrading magento or moving to another server – seems to give problems to many people. Even magentocommerce forums is full of “foreign key” issues but Varien does not give us the minimum information for being bale to make it work.
@Branko : are you sure about the sql clean script you gave ?
I read many times that log_*_type were not to be truncated.
Same for log_quote.
Has anyone tried this log cleaning ? Did it solved the foreign key issue or did it make other problem appear ?
Thanks for sharing
After I found this site, moving stores from live to dev and back – has been a walk in the park
Thank you Branko! However, this time, it doesn’t work at all. So if you have ever run into (and solved) the following problem, I would love to hear your solution:
(1)Exported sql database via SSH, (2)searched/replaced “live.com” to “dev.com”, (3)imported database. (4)Copied file structure from live server to dev server.
Output in browser: Warning: simplexml_load_string() [function.simplexml-load-string]: Entity: line 71: parser error : Opening and ending tag mismatch: default line 46 and reference in /*/*/*/*/www/app/code/core/Mage/Core/Model/Layout/Update.php on line 294
I’ve seen a lot of threads regarding people having the same issue – but no answer anywhere
Actually I use much more easier approach these days. Setup the Virtual host on your local apache to fully reflect the live site. Then create the database and database user on local site with the same name as you have on live site. All you have to do later is simply copy paste the files of Magento site and transfer database. No need to do any search-replace.
Hi,
@Romain : Give it up, you’re never going to successfully upgrade a Magento store over that development distance. Too much has changed within Magento, we’re wary of going from 1.2.x upwards and would never undertake the upgrade of a store of that age. Your better option there is to create a new store and export/import products, templates etc.
I’m also wondering if anyone has had any success with the truncating of store logs. I’m trying to upgrade a 1.3.1.x store, that has never run with the cron (therefore the store logs have never been cleaned), one of the tables runs to 1.5 million entries. I’ve had the cron attempting to clean the tables for 80 minutes now and i’m becoming impatient!
Paul.
Thanks Kyle, this just saved me. The media/*/cache and media/tmp folders were causing broken images on my pages – much appreciated.
JD