Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

V1.4.1.1 - > 1.5.0.1 Database Migration Issues (RESOLVED)
 
bkvargyas
Jr. Member
 
Total Posts:  11
Joined:  2009-07-15
 

I recently decided to look into upgrading our 1.4.1.1 site to 1.5.0.1 and built a new DEV server and installed a clean install of 1.5.0.1 and our template.  Using the WIKI guide posted here: http://www.magentocommerce.com/wiki/groups/227/moving_magento_to_another_server

I made a backup copy of the SQL Database and moved it over to the new machine and restored it.  Just to make sure all was good, I also copied my 1.4.1.1 directory over and brought the site up as-is—all was fine.  I disabled cache and then changed the directory to 1.5.0.1 and then went in and ran “php index.php” like I have always done with previous upgrades --- so the database conversions completed and all was good.

Not this time!  That process has been running for 12+ hours now, and a mysqladmin status shows me 25 queries per second, with 5 open tables and a cpu load on the mysql process of a constant 20%.  Digging deeper with phpMyAdmin, it seems to be stuck doing this:
ALTER TABLE `sales_flat_order` ADD UNIQUE `UNQ_INCREMENT_ID` ( `increment_id` )

The status changes from “manage keys’ to “create table” and flips all day long.  Nothing ever seems to complete.  What is going on here?  At this point, I’m stuck on how to migrate our site to 1.5.0.1 --- I’ve managed two migrations this way and never ran into this type of issue.

Our database backup is about 1.1GB and after it’s been “loaded” the datafile is around 1.8GB --- not that large—but yes, significantly more in size since the last conversion I did.  Last database conversion I did completed in about 1-2 minutes. 

Any ideas?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

It should take less than 1 hour for that database size.  I would start it again on a restored database. I recently upgraded with a similar size of database and it took around 45 minutes, I also had some errors but I was able to repair and restart during the process.

 
Magento Community Magento Community
Magento Community
Magento Community
 
bkvargyas
Jr. Member
 
Total Posts:  11
Joined:  2009-07-15
 

So I’ve dug into this a bit more and here is what I’ve come across.  I took our production store (running V1.4.1.1) and did another mysqldump of the database and imported this into our DEV environment --- I decided to see if I could upgrade to V1.4.2.0 before 1.5.0.1 --- sure enough, I was able to go to V1.4.2.0 successfully.  I had a few errors in the DB I had to correct for, but nothing too major --- and I tested our database under the 1.4.2.0 environment just fine.  So --- I decided to try this newly updated database to 1.5.0.1 --- same problem.  Hung on upgrade --- I let it run 8 hours this time and I also had moved the entire system to a SSD HDD to let it ‘convert” as fas as it could....  still nothing.

So I turned on mysql logging --- and here is where I’m stuck --- It looks like it’s stuck on the file:
app/code/core/Mage/Sales/sql/sales_setup/mysql-upgrade-1.4.0.21-1.4.0.22.php

And here is what the log looks like for thousands of lines:  (It’s running 25 of these a sec)
3 Query ALTER TABLE `sales_flat_order` ADD UNIQUE `UNQ_INCREMENT_ID` (`increment_id`)
3 Query SELECT COUNT(*) as `cnt` FROM `sales_flat_order` WHERE increment_id=’100001066’
3 Query DELETE FROM `sales_flat_order` WHERE increment_id=’100001066’ LIMIT 0
3 Query ALTER TABLE `sales_flat_order` ADD UNIQUE `UNQ_INCREMENT_ID` (`increment_id`)
3 Query SELECT COUNT(*) as `cnt` FROM `sales_flat_order` WHERE increment_id=’100001066’
3 Query DELETE FROM `sales_flat_order` WHERE increment_id=’100001066’ LIMIT 0
3 Query ALTER TABLE `sales_flat_order` ADD UNIQUE `UNQ_INCREMENT_ID` (`increment_id`)
3 Query SELECT COUNT(*) as `cnt` FROM `sales_flat_order` WHERE increment_id=’100001066’
3 Query DELETE FROM `sales_flat_order` WHERE increment_id=’100001066’ LIMIT 0

I’ve been able to manually query that increment_id the sales_flat_order from within MySQL Workbench just fine --- But for watever reason, the 1.5.0.1 upgrade database process get’s hung up on these 3 SQL statements forever.

At this point I’m stuck—I’m willing to hire a MAG DB person to get us past this point if necessary..  Any ideas would be appreciated.

Thanks,

Brian

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Try this:

Find the file app/code/core/Mage/Sales/sql/sales_setup/mysql-upgrade-1.4.0.21-1.4.0.22.php

And comment it out the lines for that query and see if it makes it past it

You can always manually redo that after

 
Magento Community Magento Community
Magento Community
Magento Community
 
bkvargyas
Jr. Member
 
Total Posts:  11
Joined:  2009-07-15
 

So I managed to resolve this problem --- here is what I found:

I had two Order numbers in our system that had been canceled and a link was created to the old older.  The new order which is 1066-1 had TWO entires in the database.  Simply crafting a couple SQL statements took care of that:

SELECT * FROM `magento`.`sales_flat_order` WHERE increment_id=’100001066-1’; (Used to verify correct row)
DELETE FROM `magento`.`sales_flat_order` WHERE increment_id=’100001066-1’; (Delete all rows that were duplicated with this id)

Finally, after doing a “php index.php” again (to get it to run the DB updates), I found another order where it had 4 duplicates!  I did the same thing to that order and now the database conversion completed in about 1 minute—about what I had originally expected it to do.

Magento was trying to delete the non “-1” order, when instead—it should have been trying to delete the “-1” order based on the script I looked at.  From what I can tell, the duplicate entries were created back in the 1.3.2.3 days from the dates on the orders—not sure what caused it.

Also, I took my V1.4.1.1 datatabase and took it to 1.5.0.1 directly with just these few changes, and so now all is working in the backend now!  I would have never found them if I had not enabled MySQL Logging.

Brian

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Nice call Brian, your approach was definitely better than my Dining room table approach!

 
Magento Community Magento Community
Magento Community
Magento Community
 
ladle
Sr. Member
 
Avatar
Total Posts:  173
Joined:  2009-06-11
 
bkvargyas - 27 February 2011 11:45 AM

So I managed to resolve this problem --- here is what I found:

I had two Order numbers in our system that had been canceled and a link was created to the old older.  The new order which is 1066-1 had TWO entires in the database.  Simply crafting a couple SQL statements took care of that:

SELECT * FROM `magento`.`sales_flat_order` WHERE increment_id=’100001066-1’; (Used to verify correct row)
DELETE FROM `magento`.`sales_flat_order` WHERE increment_id=’100001066-1’; (Delete all rows that were duplicated with this id)

Finally, after doing a “php index.php” again (to get it to run the DB updates), I found another order where it had 4 duplicates!  I did the same thing to that order and now the database conversion completed in about 1 minute—about what I had originally expected it to do.

Magento was trying to delete the non “-1” order, when instead—it should have been trying to delete the “-1” order based on the script I looked at.  From what I can tell, the duplicate entries were created back in the 1.3.2.3 days from the dates on the orders—not sure what caused it.

Also, I took my V1.4.1.1 datatabase and took it to 1.5.0.1 directly with just these few changes, and so now all is working in the backend now!  I would have never found them if I had not enabled MySQL Logging.

Brian

FUCKING GENIUS.  This solution almost brought me to tears, I’ve been having this issue for days!  I can’t believe I found this, Thank you so much!!!!!!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jonathan123
Sr. Member
 
Total Posts:  215
Joined:  2008-09-14
 

Thank you so very much. This one had me going for a while. The worst part for me is the errant duplicate order occurred just a few weeks ago and the dev system wasn’t using the same DB so I never saw it on the dev system.... Anyway, thanks again!

 
Magento Community Magento Community
Magento Community
Magento Community
 
knarz
Member
 
Total Posts:  43
Joined:  2009-07-11
 

Thank you 1000000000 times!

bkvargyas +1 smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
danielchindea
Jr. Member
 
Total Posts:  1
Joined:  2010-07-04
 

I could kiss you man! Thank you a lot.

 
Magento Community Magento Community
Magento Community
Magento Community
 
boxer1732
Jr. Member
 
Total Posts:  14
Joined:  2009-02-04
 

Thanks for this POST - life saver for us too!  Thanks for sharing.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Peppermay
Sr. Member
 
Avatar
Total Posts:  111
Joined:  2008-10-18
Lille, France
 

God bless bkvargyas !

How many Magento administrators was about to commit suicide before reading his post ? ^^

 
Magento Community Magento Community
Magento Community
Magento Community
 
Vionic Group
Jr. Member
 
Total Posts:  1
Joined:  2011-02-17
 

Thank you! Solved my 1.4.0.1 to 1.6.2 update issue.

 
Magento Community Magento Community
Magento Community
Magento Community
 
mweather
Member
 
Total Posts:  33
Joined:  2007-08-31
 

Here’s what I did to fix the problem.

first, run this query to determine which increment_ids are dupes (change the table name if needed):

SELECT increment_idCOUNT(*) c FROM sales_flat_order GROUP BY increment_id HAVING c 1;

Then, plug the result into this php script (change the path to mage.php and put replace the increment_ids in the $dupes array with your own):

$dupes= array('R00168293-1','R00168534-1','R00168946-1');
require_once(
'/var/www/magento-1.5/app/Mage.php');

Mage::app('admin');
Mage::getSingleton("core/session", array("name" => "adminhtml"));
Mage::register('isSecureArea',true);
$collection Mage::getResourceModel('sales/order_collection')
  ->
addAttributeToSelect('*')
  ->
setPageSize(5000)
  ->
addFieldToFilter('status''canceled')
  ->
addFieldToFilter('increment_id', array('in'=>$dupes))
  ->
load();

foreach (
$collection as $col{
  Mage
::log($col->getIncrementId() . ' order deleted ');
  try 
{
    $col
->delete();
  
catch (Exception $e{
    
throw $e;
  
}
}

This will delete all the dupes that are canceled orders.

 
Magento Community Magento Community
Magento Community
Magento Community
 
gonzela2006
Member
 
Total Posts:  31
Joined:  2010-01-29
 

Thank you a loooooot bkvargyas.
I have updated successfully from 1.4.1.1 to 1.7.0.2

 
Magento Community Magento Community
Magento Community
Magento Community
 
fysiosupplies
Jr. Member
 
Total Posts:  1
Joined:  2012-10-07
 

@mweather: Nice script! But you also delete if all duplicates are canceled and you want to keep the last updated one.

Find and show duplicate orders:

SELECT  
FROM `sales_flat_order` AS `a
INNER JOIN (
    
SELECT `increment_id
    
FROM `sales_flat_order
    
GROUP BY `increment_id
    
HAVING COUNT(*) > 1) AS `bON `a`.`increment_id` = `b`.`increment_id`

List duplicate increment_id’s:

SELECT `increment_id
FROM `sales_flat_order
GROUP BY `increment_id
HAVING COUNT(*) > 1

and with some extra info:

SELECT `increment_id`, COUNT(*) AS `num`, GROUP_CONCAT(`status`) AS `status` , GROUP_CONCAT(`created_at`) AS `created_at
FROM (SELECT FROM `sales_flat_orderORDER BY `statusASC, `updated_atASC) AS `sales_flat_order
GROUP BY `increment_id
HAVING `num` > 1

List duplicate orders which are canceled (note: also if the last duplicate is not completed):

This will do the same as mweather’s solution only it is done by MySQL.

SELECT 
FROM `sales_flat_order` AS `a
INNER JOIN (
    
SELECT `increment_id
    
FROM `sales_flat_order
    
GROUP BY `increment_id
    
HAVING COUNT(*) > 1) AS `bON `a`.`increment_id` = `b`.`increment_id`
WHERE `a`.`status` = 'canceled'

But you want to keep the last (updated_at) canceled order if all are canceled:

SELECT *  
FROM `sales_flat_order` AS `a
INNER JOIN (
    
SELECT `increment_id` AS `increment_id2`, GROUP_CONCAT(`entity_id`) AS `entity_ids
    
FROM (SELECT FROM `sales_flat_orderORDER BY `statusASC, `updated_atASC) AS `sales_flat_order
    
GROUP BY `increment_id
    
HAVING COUNT(*) > 1) AS `bON `a`.`increment_id` = `b`.`increment_id2`
WHERE `b`.`entity_idsNOT LIKE CONCAT('%,', `a`.`entity_id`)

Delete (and make a backup table)

CREATE TABLE `sales_flat_order_duplicates
SELECT 
FROM `sales_flat_order` AS `a
INNER JOIN (
    
SELECT `increment_id` AS `increment_id2`, GROUP_CONCAT(`entity_id`) AS `entity_ids
    
FROM (SELECT FROM `sales_flat_orderORDER BY `statusASC, `updated_atASC) AS `sales_flat_order
    
GROUP BY `increment_id
    
HAVING COUNT(*) > 1) AS `bON `a`.`increment_id` = `b`.`increment_id2
WHERE `b`.`entity_idsNOT LIKE CONCAT('%,', `a`.`entity_id`) 
ORDER BY `a`.`entity_idASC;

ALTER TABLE `sales_flat_order_duplicatesDROP `entity_ids`;
ALTER TABLE `sales_flat_order_duplicatesDROP `increment_id2`;

DELETE `sales_flat_order
FROM `sales_flat_order
INNER JOIN `sales_flat_order_duplicates
    
USING(`entity_id`);
 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top