1

I did a magento update from 2.4.6-p3 -> 2.4.7-p1 and now I cannot create programmatically a product. Once I am doing:

$product = $this->product->setData($productData); $this->resourceModelProduct->save($product);

I'm getting this error message:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento`.`catalog_url_rewrite_product_category`, CONSTRAINT `FK_BB79E64705D7F17FE181F23144528FC8` FOREIGN KEY (`url_rewrite_id`) REFERENCES `url_rewrite` (`url_rewrite_id`) ON DELETE CASCADE), query was: INSERT INTO `catalog_url_rewrite_product_category` (`url_rewrite_id`,`category_id`,`product_id`) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?) ON DUPLICATE KEY UPDATE `url_rewrite_id` = VALUES(`url_rewrite_id`), `category_id` = VALUES(`category_id`), `product_id` = VALUES(`product_id`)

It is very odd since the table is empty. See:

mysql> select * from catalog_url_rewrite_product_category; Empty set (0.00 sec)

I also removed all of my category and product urls from the url_rewrite and regenerated it with this plugin: https://github.com/olegkoval/magento2-regenerate_url_rewrites. But I've got the same results. On m2.4.6-p3 , its perfectly working. I also tried this possible fix: https://magento.stackexchange.com/a/322107/6204 , but it does not work for me.

Anyone got this error before and managed to fix it ? Would you please share your thoughts.

Thanks

Improve this question

2 Answers 2

Reset to default
2

Running these queries, fixed my problem:

START TRANSACTION; CREATE TABLE catalog_url_rewrite_product_category_temp SELECT DISTINCT * FROM catalog_url_rewrite_product_category; ALTER TABLE catalog_url_rewrite_product_category RENAME catalog_url_rewrite_product_category_backup; ALTER TABLE catalog_url_rewrite_product_category_temp RENAME catalog_url_rewrite_product_category; DROP TABLE catalog_url_rewrite_product_category_backup; COMMIT;

Credit goes to this comment: https://github.com/magento/magento2/issues/33770#issuecomment-898520221

Don't forget to take a database backup, before, just in case :)

Improve this answer
0

First thing, try to use repository rather than model for saving products.

A second possible issue could comes from the difference between url_key and and url_path.

I already faced a situatino where the url key and path were not properly updated together, causing the issue of rewrites being still there, although the key was really unique; but the path wasn't. I often had this issue on the past with admin customer recreating products by adding existing sku and -1 -2 to the sku causing apparently some issue on path and key. So this is something to be aware about.

I already used olagkoval module and CLI on some websites, it honestly created us more issue than it solved. I think the module is great but the configuration to do exactly what we need might not be properly used.

I would also recommend you mostly to check the url_rewrite table data for the product and the category you might wanna update in the first place rather than catalog_url_rewrite_product_category; all the issues often come mostly from url_rewrite table itself.

Improve this answer
3
  • Thanks for your feedback. I ran these queries: delete from url_rewrite where entity_type = 'category'; and delete from url_rewrite where entity_type = 'product' , try to create my product with the unique sku and url and I still face the issue. If I drop that FK with alter table catalog_url_rewrite_product_category drop foreign key FK_BB79E64705D7F17FE181F23144528FC8; , magically it works. but after I am running s:upgrade, it will recreate it and the issue appears again. Commented Aug 12, 2024 at 13:43
  • Sadly I do not have any 2.4.7 instance to confirm, still in 2.4.6. Ineed it's weird. The real issue would be more likely why does the fk is recreated when you set up. Did you tried recreate the redirection properly by saving the product or running sql request before doing the setup ? Sry i don't have any more ideas there :( Commented Aug 12, 2024 at 13:54
  • 1
    @Claims the foreign key being recreated is not the real issue. This is a feature of Declarative setup, which will recreate the table, columns, indexes, foreign keys, etc., during the bin/magento setup:upgrade task if you remove them manually. Commented Aug 12, 2024 at 15:21

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.