Right, as ever we’re left to fix these things ourselves. Here’s the solution to my problem, might help others out.
This SQL:
SELECT * FROM catalog_category_product_index LEFT JOIN catalog_product_entity ON catalog_category_product_index.product_id = catalog_product_entity.entity_id WHERE catalog_product_entity.entity_id IS NULL
Highlighted corrupt product referencess in the ‘catalog_category_product_index’ table, which had to be manually deleted from within PHPMyAdmin.
Do the same with ‘catalog_category_product’ and you should be good to go, until the tables get corrupt again, of course.
It seems that for the error that Lenny2 and I were having with the catalog_product_website table was caused, at least for me, by the fact that the catalog_product_website table was of type InnoDB, but the table that it was referencing to get the foreign key, core_website was of a different type. I changed the core_website table to type InnoDB (was type of MyISAM) and that seems to have fixed that error. Here is the SQL command:
ALTER TABLE core_website ENGINE=INNODB
Please make sure to backup your database before editing your tables manually.
Now I’m getting the same error as Redpen. I’m going to look into that now…
Ok so I found that the error that both redpen and I were having
redpen -
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PRODUCT_INDEX_PRODUCT_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON D)
is cause by the same thing as the one that Lenny2 and I were first experiencing
Lenny2 -
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`store/catalog_product_website`, CONSTRAINT `FK_CATAOLOG_PRODUCT_WEBSITE_WEBSITE` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE)
As I said in my post above, the foreign key constraint command requires that both the table that it is being applied to, and the table in which it is referencing the foreign key, must be of type InnoDB.
The catalog_category_product_index in my database was of type InnoDB but the table it was referencing to get the foreign key, catalog_product_entity was of type MyISAM. Once I change the catalog_product_entity table to type InnoDB the error disappeared and I can now create products and categories. I did this with the following command.
ALTER TABLE catalog_product_entity ENGINE=INNODB
Please make sure you backup your database before you mess with it.
Not sure why this is marked as ‘fixed’ - just had the same problem surface again. Seems like the DB slowly gets corrupted during normal usage, had to manually go in and remove ‘stray’ records from the DB via PHPMyAdmin… =(
Same problem here, thanks redpen for the temporary “fix”.
Also I lost a product—the product ID with corrupt references was also missing from the product list in admin. Sigh.
Beginning to think I should have gone with oscommerce. The silence from the developers is deafening.
A little bit of debugging info-- the offending product was one I don’t believe I went through with saving. I think I changed my mind after going past the first screen (attribute set choice) and after filling out some of the attribute groups. It’s also possible I did save it, then deleted it. So it seems that either an incomplete addition to the catalogue or a deletion of a product can trigger the corruption of the database.
Any update on this? My tables are innodb, I’ve run the fixes posted, I’m still unable to create categories without the “Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails” error popping up.
Any update on this? My tables are innodb, I’ve run the fixes posted, I’m still unable to create categories without the “Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails” error popping up.
Check the roadmap fixes to see if this is being addressed.
Right, as ever we’re left to fix these things ourselves. Here’s the solution to my problem, might help others out.
This SQL:
SELECT * FROM catalog_category_product_index LEFT JOIN catalog_product_entity ON catalog_category_product_index.product_id = catalog_product_entity.entity_id WHERE catalog_product_entity.entity_id IS NULL
Highlighted corrupt product referencess in the ‘catalog_category_product_index’ table, which had to be manually deleted from within PHPMyAdmin.
Do the same with ‘catalog_category_product’ and you should be good to go, until the tables get corrupt again, of course.
redpen can you explain this al little more detaild as I get the same error and get results for this query
