OK, here is what I found out so far:
The file : /home/mysite/public_html/test/app/code/core/Mage/Sales/sql/sales_setup/mysql4-upgrade-0.9.29-0.9.30.php that causing this error is trying to addKey to two tables: sales_order and sales_order_entity.
1- in phpMyAdmin I exported both tables as sql and save a copy on my pc.
2- I truncated both of the above tables.
3- I visits my home page and WALA it loaded with no errors. The admin also loaded without errors. But because I deleted the data from the order tables, I had no orders in my admin -> Sales -> Order or on my Dashboard.
4- So I went back to phpMyAdmin and tried to import both tables back to the database to retrieve the data. But I got these errors:
When importing sales_order:
-- -- Constraints for dumped tables -- -- -- Constraints for table `sales_order` -- ALTER TABLE `sales_order` ADD CONSTRAINT `FK_SALE_ORDER_STORE` FOREIGN KEY ( `store_id` ) REFERENCES `core_store` ( `store_id` ) ON DELETE SET NULL ON UPDATE CASCADE , ADD CONSTRAINT `FK_SALE_ORDER_TYPE` FOREIGN KEY ( `entity_type_id` ) REFERENCES `eav_entity_type` ( `entity_type_id` ) ON DELETE CASCADE ON UPDATE CASCADE ;
So, as per above, the data in both tables are being imported. When I go to admin--> Sales -> Orders I see all the orders there, but when I click on the orders to see the details, nothing comes up. In dashboard the number of orders remains 0.
As you can see both errors are mySql errorno:121.
I’m not an expert in mySql, but I did a google on this error and I found that this error happens when you use the same foreign key names on different tables. I don’t know how to fix this. If anyone here is expert in mySQL this may be a clue to how this could be fixed.
I was able to upgrade my store from 184.108.40.206 to 1.2.1 and fix the above problem. Here is what I did.
NOTE: Please note that this post is only to let you know that this method worked for my store. I cant guarantee that it will work for you. Make sure to use this on your test store first. You will be editing one file and your database manually. You should know what you are doing.
1- BACK UP BACK UP BACK UP EVERYTHING, I backed up my database and all my files and directories, just in case.
2- Admin -> System -> Cache Management and DISABLE all cache
3- I ran the following command via SSH:
Obviously you need to change /path/to/magento/ to your host path. In my case it was /home/mystore/public_html/
4- Return to Admin. I have been logged out because I already cleared the session. I Logged back in and to System -> Magento Connect -> Magento Connect Manager.
5- I clicked on check for updates. All available updates highlighted. I selected all upgrades, checked the box next to “Clear all sessions after successfull install or upgrade” ( I know it has a typo, it should say successful!).
6- Held my breath and clicked on commit changes. Upgrade took 2-3 minutes and was done. The command window showed that everything was installed OK.
7- After upgrade completed, I clicked on Refresh button. OMG. I had the following error:
10- Return to my browser and to my store at http://www.BargainCollection.com. I had the same error again. If you don’t have the same error and you see an error regarding “CatalogInventory” then skip step 11 and go to step 12.
11- Next I changed the /app/code/core/Mage/Sales/sql/sales_setup/mysql4-upgrade-0.9.29-0.9.30.php to this: (this time I’m removing the /* and */ from first section and adding to the second section):
13- Returned to Dreamweaver and to /app/code/core/Mage/Sales/sql/sales_setup/mysql4-upgrade-0.9.29-0.9.30.php and removed the /* and */ that I added before.
14- Using phpMySQL I removed (delete) the “cataloginventory_stock_status” table.
15- Return to my browser and visit my site. On first visit it may take few minutes to load. This is because the scripts are updating the database. Home page loaded fine, but I had error when I was clicking on any category or product. The error was regarding cataloginventory_stock_status does not exist. the same error was coming up when going to admin. I returned to phpMyAdmin and ran the following sql command:
CREATE TABLE IF NOT EXISTS `cataloginventory_stock_status` ( `product_id` int(10) unsigned NOT NULL, `website_id` smallint(5) unsigned NOT NULL, `stock_id` smallint(4) unsigned NOT NULL, `qty` decimal(12,4) NOT NULL default '0.0000', `stock_status` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`product_id`,`website_id`,`stock_id`), KEY `FK_CATALOGINVENTORY_STOCK_STATUS_STOCK` (`stock_id`), KEY `FK_CATALOGINVENTORY_STOCK_STATUS_WEBSITE` (`website_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `cataloginventory_stock_status` ADD CONSTRAINT `FK_CATALOGINVENTORY_STOCK_STATUS_STOCK` FOREIGN KEY (`stock_id`) REFERENCES `cataloginventory_stock` (`stock_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `FK_CATALOGINVENTORY_STOCK_STATUS_PRODUCT` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `FK_CATALOGINVENTORY_STOCK_STATUS_WEBSITE` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE;
16- I returned to my store. Every page was up and running with no error. But, all products was listed as “Out of Stock”
17- In Admin --> System --> cache management, I clicked on Refresh Inventory Stock status. It took 2-3 minutes for over 10,000 products and after that everything was working properly.
THAT WAS IT!. It fixed the problem and my site was back live and running. I and I just received a new order couple minutes after I was back up!
Final touch ups: I went to Admin -> System -> Cache Management: and refreshed catalog rewrite, image cache, search index and Layered Navigation Indices. I enabled all cache. And I was done.
I hope this helps you to fix your problem, but again, make sure to try it out on your test store before you use it on your production store.
Does the tutorial from Zadpro work for my issue too? I’ve got Magento CE 220.127.116.11 installed and that wasnt a problem with upgrading magento.
Also i got the error first time when the shop was free for all visitors. before i tested with testproducts and categories. and there i hadn’t the problem.
Would be great if anyone knows an answer.
May help: The error always comes when i visit the shop the first time (session in browser) and add the first configurable product to shopping cart. after the error the product is in shopping cart and all following add’s to shopping cart work well.