magento2: Magento 2.4 Upgrade issue Unable to apply data patch Magento\CatalogUrlRewrite\Setup\Patch\Data\UpdateUrlKeyForProducts

@moni921 for now you can disable and re-enable foreign key constrain

…php $setup->getConnection()->query(‘SET @TEMPORARY_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1’); $this->groupRepository->delete($customerGroup); $setup->getConnection()->query(‘SET FOREIGN_KEY_CHECKS=IF(@TEMPORARY_FOREIGN_KEY_CHECKS=0, 0, 1)’); …

We had the same issue when upgrading from 2.3.4 to 2.4.1. As someone else has mentioned, applying this pull request as a patch seemingly works.

Tagging @engcom-Echo in here, since he wrote that patch, maybe he has some more useful insights and can push for a quick fix to be added to Magento 2.4.1 or a hotfix to be released if it is deemed necessary 🙂

Please note that this patch is not working for commerce installations. entity_id is not a column name for commerce, it should be row_id instead.

For the update to succeed all keys should be included in the query. Change lines according below in Magento\CatalogUrlRewrite\Setup\Patch\Data\UpdateUrlKeyForProducts:

@@ -58,7 +58,7 @@
         $table = $this->moduleDataSetup->getTable('catalog_product_entity_varchar');
         $select = $this->moduleDataSetup->getConnection()->select()->from(
             $table,
-            ['value_id', 'value']
+            ['entity_id', 'attribute_id', 'store_id', 'value_id', 'value']
         )->where(
             'attribute_id = ?',
             $this->eavSetup->getAttributeId($productTypeId, 'url_key')

Can confirm issue. Upgrading from 2.3.5-p1 to 2.4.

I executed the SQL: SELECT COUNT(*) FROM catalog_product_entity_varchar cpev LEFT JOIN catalog_product_entity cpe on cpev.entity_id = cpe.entity_id WHERE cpe.entity_id IS NULL;

The result is 0

With the pull from @ravi-chandra3197 the upgrade works. https://github.com/magento/magento2/pull/29804/files

For anyone who needs a ready-to-use patch file for composer patches: here you go ->>> save it as patch file and configure it for composer patches, viola

Index: Setup/Patch/Data/UpdateUrlKeyForProducts.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/Setup/Patch/Data/UpdateUrlKeyForProducts.php b/Setup/Patch/Data/UpdateUrlKeyForProducts.php
--- a/Setup/Patch/Data/UpdateUrlKeyForProducts.php	(date 1625672707821)
+++ b/Setup/Patch/Data/UpdateUrlKeyForProducts.php	(date 1625672707821)
@@ -34,6 +34,11 @@
      */
     private $urlProduct;
 
+    /**
+     * @var \Magento\Framework\EntityManager\MetadataPool
+     */
+    private $metadataPool;
+
     /**
      * @param ModuleDataSetupInterface $moduleDataSetup
      * @param EavSetupFactory $eavSetupFactory
@@ -42,11 +47,13 @@
     public function __construct(
         ModuleDataSetupInterface $moduleDataSetup,
         EavSetupFactory $eavSetupFactory,
-        Url $urlProduct
+        Url $urlProduct,
+        \Magento\Framework\EntityManager\MetadataPool $metadataPool
     ) {
         $this->moduleDataSetup = $moduleDataSetup;
         $this->eavSetup = $eavSetupFactory->create(['setup' => $moduleDataSetup]);
         $this->urlProduct = $urlProduct;
+        $this->metadataPool = $metadataPool;
     }
 
     /**
@@ -58,7 +65,7 @@
         $table = $this->moduleDataSetup->getTable('catalog_product_entity_varchar');
         $select = $this->moduleDataSetup->getConnection()->select()->from(
             $table,
-            ['value_id', 'value']
+            [$this->getProductLinkField(), 'attribute_id', 'store_id', 'value_id', 'value']
         )->where(
             'attribute_id = ?',
             $this->eavSetup->getAttributeId($productTypeId, 'url_key')
@@ -99,4 +106,17 @@
     {
         return [];
     }
+
+    /**
+     * Return product id field name - entity_id|row_id
+     *
+     * @return string
+     * @throws \Exception
+     */
+    private function getProductLinkField()
+    {
+        return $this->metadataPool
+            ->getMetadata(\Magento\Catalog\Api\Data\ProductInterface::class)
+            ->getLinkField();
+    }
 }

✅ Confirmed by @engcom-Alfa Thank you for verifying the issue. Based on the provided information internal tickets MC-40700 were created

Issue Available: @engcom-Alfa, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

For the update to succeed all keys should be included in the query. Change lines according below in Magento\CatalogUrlRewrite\Setup\Patch\Data\UpdateUrlKeyForProducts:

@@ -58,7 +58,7 @@
         $table = $this->moduleDataSetup->getTable('catalog_product_entity_varchar');
         $select = $this->moduleDataSetup->getConnection()->select()->from(
             $table,
-            ['value_id', 'value']
+            ['entity_id', 'attribute_id', 'store_id', 'value_id', 'value']
         )->where(
             'attribute_id = ?',
             $this->eavSetup->getAttributeId($productTypeId, 'url_key')

this seems to fix it

@ihor-sviziev : exactly the same problem …

Unable to apply data patch Magento\CatalogUrlRewrite\Setup\Patch\Data\UpdateUrlKeyForProducts for module Magento_CatalogUrlRewrite. Original exception message: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mag`.`catalog_product_entity_varchar`, CONSTRAINT `CAT_PRD_ENTT_VCHR_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELET), query was: INSERT  INTO `catalog_product_entity_varchar` (`value_id`,`value`) VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)

@ihor-sviziev let me give it a try

Hi @DiegoSana @usman786cs, Could you add some additional info how we can reproduce this issue?

Could you try to execute following SQL and let us know if there any result? This SQL checks if you have any product values that not belongs to any product

SELECT COUNT(*)
FROM catalog_product_entity_varchar cpev
LEFT JOIN catalog_product_entity cpe on cpev.entity_id = cpe.entity_id
WHERE cpe.entity_id IS NULL;

For me result is following, and issue isn’t reproducing:

mysql> SELECT COUNT(*)
    -> FROM catalog_product_entity_varchar cpev
    -> LEFT JOIN catalog_product_entity cpe on cpev.entity_id = cpe.entity_id
    -> WHERE cpe.entity_id IS NULL;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.04 sec)

@ihor-sviziev i solved this issue. So my database is upgraded succesfully. Any way, in my case the query is with row id.

SELECT COUNT(*) FROM catalog_product_entity_varchar cpev LEFT JOIN catalog_product_entity cpe on cpev.row_id = cpe.row_id WHERE cpe.row_id IS NULL;

Because i use Magento CE. Before the upgrade, i have some results with this query. So i removed it. Now the result is empty.

For the update to succeed all keys should be included in the query. Change lines according below:

@@ -58,7 +58,7 @@
         $table = $this->moduleDataSetup->getTable('catalog_product_entity_varchar');
         $select = $this->moduleDataSetup->getConnection()->select()->from(
             $table,
-            ['value_id', 'value']
+            ['entity_id', 'attribute_id', 'store_id', 'value_id', 'value']
         )->where(
             'attribute_id = ?',
             $this->eavSetup->getAttributeId($productTypeId, 'url_key')

Can you provide more details? Which file are you changing?