2

it seems updating magento is really hard :)

I have magento 1.4.1.1 and i want to update it to the newest magento 1.4.2.

I follow this guide: How to upgrade magento 1.4 1.1 to 1.7.0.2

What i of course do: - Disable and clear cache - Clear sessions - Disable all extensions before update - Backup backup backup :D

When i update from 1.4.1.1 to 1.4.2 i trigger the mainpage to make the mysql changes. This is where to problems start.

I get this error:

a:5:{i:0;s:435:"Error in file: "/var/www/vhosts/dev/docs/app/code/core/Mage/Sales/sql/sales_setup/mysql4-upgrade-1.4.0.20-1.4.0.21.php" - SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`dev`.<result 2 when explaining filename '#sql-20eb_3d66'>, CONSTRAINT `FK_PRODUCT_ORDERED_AGGREGATED_MONTHLY_PRODUCT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entit)";i:1;s:974:"#0 /var/www/vhosts/dev/docs/app/code/core/Mage/Core/Model/Resource/Setup.php(390): Mage::exception('Mage_Core', 'Error in file: ...')

I have also tried to upgrade to 1.7 directly which leeds to numerous other similar errors. I of course could comment out all those pieces which cause problems but i dont think that would be wise :)

Do you have any idea what i can do avoid / fix these errors?

Thank you very much :)

Greets

Community
  • 1
  • 1
  • I'm going to add "updating Magento is really annoying"... (picture of bald headed, insane smiley in a rubber room here) – Fiasco Labs Dec 02 '12 at 21:52

3 Answers3

2

Yes, upgrading Magento is almost never smooth.

To diagnose this particular problem you have to compare types sales_bestsellers_aggregated_monthly.product_id and catalog_product_entity.entity_id fields.

You can find more details on what is causing this problem and also a solution in my article dedicated to this subject.

In English: How 3rd Party Extensions Can Ruin Your Magento Upgrade

Auf Deutsch: Wie Externe Extensions Dein Magento-Upgrade Ruinieren Können

user487772
  • 8,800
  • 5
  • 47
  • 72
  • Thank you for the post! I'm going to be in Reuben's position here soon, trying to figure out where all the upgrades went wrong and the looming spectre of the Foreign Key thing was giving me the heebie jeebies. – Fiasco Labs Dec 02 '12 at 21:55
  • Hello Tim, thank you so much for the first hint on how to solve my problem :) – Ruben Schulze-Fröhlich Dec 07 '12 at 07:55
  • @Fiasco Labs, feel free to ask if you will have any issues in process. – user487772 Dec 07 '12 at 08:00
  • I have compared the two fields and have found out that in these two tables there are not even two product_id fields. in catalog_product_entity there is only a entity_id field. In sales_bestsellers_aggregated_monthly there is a product_id field. How can i solve this problem? Do i have to rename or even create a product_id field in the catalog_product_entity table? Greets and thank you so much :) – Ruben Schulze-Fröhlich Dec 07 '12 at 08:32
  • oh, it was my typo. `sales_bestsellers_aggregated_monthly`.`product_id` is referencing `catalog_product_entity`.`entity_id`. so you have to compare these two. – user487772 Dec 07 '12 at 08:42
  • Hello Tim, i think this is to complicated for me. Do you offer any kind of service to fix these errors for me? paied of course :D Greets – Ruben Schulze-Fröhlich Dec 07 '12 at 08:55
  • please email me to tim[]openstream.ch and i will help you during the week-end for free. – user487772 Dec 07 '12 at 09:06
0

If you had made a backup of your database and it is possible to restart the process, I would advise you to go another way, but here you will have to move to 1.5 than to 1.6 than to 1.7:

First, you have to get SSH access from your hosting provider and connect to your server via SSH protocol. Then,

1 – Change permissions on lib/pear folder to writable (recursively): chmod -R 777 lib/PEAR

2 – Execute this command to prepare Magento for upgrade: ./pear mage-setup

Most likely you will see this result after command execution: Channel “connect.magentocommerce.com/core” is already initialized Upgrade from Magento 1.4.1.x to Magento 1.4.2.0 This step is necessary even if you upgrade your Magento store to 1.5 or 1.6 versions.

3 – Execute Magento upgrade command: ./pear upgrade -f magento-core/Mage_All_Latest-stable Output of the command will show you what core packages were upgraded: upgrade ok: channel://connect.magentocommerce.com/core/Interface_Adminhtml_Default-1.4.2.0

4 – When this part of the upgrade will be complete, enter these commands: chmod 550 ./mage ./mage mage-setup . You will see this result after command execution: Successfully added: connect20.magentocommerce.com/community It means that Magento connect 2.0 channel was added to the channels list successfully.

5 – Next, enter this command: ./mage sync Here you should see:: … Successfully added: community/Mage_Locale_en_US-1.4.2.0

Successfully added: community/Interface_Install_Default-1.4.2.0

Successfully added: community/Phoenix_Moneybookers-1.2.3

Successfully added: community/Mage_Downloader-1.5.0.0

Successfully added: community/Lib_Google_Checkout-1.4.2.0

Upgrade to Magento 1.4.2.0 is complete and now you can proceed with upgrade to Magento 1.5.1.0 or 1.6.0.0. Before proceeding with this part of Magento upgrade, it is very important to see to what version Magento upgrade scripts will upgrade your store. Enter this command to check this: ./mage list-upgrades If you will see this result:

Updates for community:

Mage_All_Latest: 1.4.2.1 => 1.6.0.0

Lib_Js_Mage: 1.4.2.0 => 1.6.0.0

Lib_Varien: 1.4.2.0 => 1.6.0.0

Lib_Phpseclib: 1.4.2.0 => 1.5.0.0

It means that your Magento will be upgraded to version 1.6.0.0. If it is not what you need you can change upgrade channel to “stable” and upgrade your Magento to version 1.5.1.0.

6 – Enter this command to change the upgrade channel to stable: ./mage config-set preferred_state stable After this the “./mage list-upgrades” command will show you this result: Updates for community:

Mage_All_Latest: 1.4.2.1 => 1.5.1.0.1

Lib_Js_Mage: 1.4.2.0 => 1.5.1.0

Lib_Varien: 1.4.2.0 => 1.5.1.0

Lib_Phpseclib: 1.4.2.0 => 1.5.0.0

Mage_Core_Adminhtml: 1.4.2.0 => 1.5.1.0

Mage_Core_Modules: 1.4.2.0 => 1.5.1.0

7 – After channel selection you can upgrade your Magento to 1.5.1.0 (or to Magento 1.6.0.0) using this command: ./mage upgrade-all –force

You will see upgraded packages on your screen:

… Package upgraded: community/Mage_Locale_en_US 1.6.0.0

Package upgraded: community/Lib_Mage 1.6.0.0

Package upgraded: community/Lib_ZF 1.11.1.0

Package upgraded: community/Lib_Js_Prototype 1.7.0.0.1

Package upgraded: community/Lib_ZF_Locale 1.11.1.0

Now the upgrade is complete. If everything was upgraded correctly, you will see upgraded store in your browser. In the admin panel, you can check the version of your store.

If you had made a brand new installation of Magento version, and you won’t do manual data transfer, since it would take days or even weeks. What you can do to move your database directly from 1.4 to the new Magento 1.7 you can use automated service.

Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
0

I have similiar problem:

app\code\core\Mage\Sales\sql\sales_setup\mysql4-upgrade-1.4.0.20-1.4.0.21.php" - SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails ..

but i my case, error appeared with this key:

FK_PRODUCT_ORDERED_AGGREGATED_DAILY_PRODUCT_ID

The solution was quite easy, I just delete entries from tables:
sales_bestsellers_aggregated_daily
sales_bestsellers_aggregated_monthly
sales_bestsellers_aggregated_yearly
where product_id wasn't correspond with entity_id at catalog_product_entity table.

This query helps you detect useless product_ids:

SELECT product_id FROM sales_bestsellers_aggregated_yearly where product_id not in  
(select entity_id from catalog_product_entity)
  • This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](http://stackoverflow.com/questions/ask). You can also [add a bounty](http://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question once you have enough [reputation](http://stackoverflow.com/help/whats-reputation). – Luke Jul 12 '14 at 19:29
  • This is correct answer to the question. Steps that I have given above, allowed me to perform a seamless upgrade. The issue in the question is almost the same like my, one difference is that I have 3 problematic tables. – Adrian Badowski Jul 16 '14 at 15:00