Cannot upgrade to latest version due to patch error

[0xMatt]

Cannot upgrade to latest version due to patch error

Issue Description

Getting this error when attempting to upgrade mailchimp:

Unable to apply data patch Ebizmarts\MailChimp\Setup\Patch\Data\Migrate452 for module Ebizmarts_MailChimp. Original exception message: Rolled back transaction has not been completed correctly.

Preconditions

1. Magento 2.4.6
2. From Version: 103.4.51
3. To Version: 103.4.52

Steps to reproduce

1. Run composer update while on version 103.4.51 which pulls in 103.4.52.
2. Run bin/magento s:up

Actual and Expected result

Expected result:
setup:upgrade succeeds without error

Actual result:
setup:upgrade fails with error in issue description

Additional information

• PHP 8.1.20
• Mariadb 10.5.20

[lytesaber]

Also getting the same data patch error, will have to roll back to the previous version until a fix has been provided.

Unable to apply data patch Ebizmarts\MailChimp\Setup\Patch\Data\Migrate452 for module Ebizmarts_MailChimp. Original exception message: Rolled back transaction has not been completed correctly.

[PachisPachis]

Same here.

[akosglue]

confirmed, m2 2.4.6, php 8.1

[Garde85]

me too! This error appers to me on different magento installations. Magento 2.4.5-p3, php 8.1.

[meetvora69]

me too! This error appears to me on different magento installations. Magento 2.4.6-p3, php 8.2

[gonzaloebiz]

Hi

we tested updating from 103.4.51 to 103.4.52 with no problems, even with a db-prefix and no problem at all. (we don't test it with split databases because was deprecated) Perhaps if you replace the vendor/mailchimp/mc-magento2/Setup/Patch/Data/Migrate452.php with this one

<?php

namespace Ebizmarts\MailChimp\Setup\Patch\Data;

use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Sales\Model\OrderFactory;
use Ebizmarts\MailChimp\Model\ResourceModel\MailChimpSyncEcommerce\CollectionFactory as SyncFactory;
use Ebizmarts\MailChimp\Helper\Data;

class Migrate452 implements DataPatchInterface
{
    /**
     * @var ModuleDataSetupInterface
     */
    private $moduleDataSetup;
    /**
     * @var SyncFactory
     */
    private $syncFactory;
    /**
     * @var OrderFactory
     */
    private $orderFactory;
    /**
     * @var Data
     */
    private $helper;

    /**
     * @param ModuleDataSetupInterface $moduleDataSetup
     * @param SyncFactory $syncFactory
     * @param OrderFactory $orderFactory
     * @param Data $helper
     */
    public function __construct(
        ModuleDataSetupInterface $moduleDataSetup,
        SyncFactory $syncFactory,
        OrderFactory $orderFactory,
        Data $helper
    )
    {
        $this->moduleDataSetup = $moduleDataSetup;
        $this->syncFactory = $syncFactory;
        $this->orderFactory = $orderFactory;
        $this->helper = $helper;
    }
    public function apply()
    {
        $this->moduleDataSetup->getConnection()->startSetup();
        $syncCollection = $this->syncFactory->create();
        $syncCollection->addFieldToFilter('type', ['eq'=>'ORD']);
        foreach ($syncCollection as $item) {
            $orderId = $item->getRelatedId();
            try {
                $order = $this->orderFactory->create()->loadByAttribute('entity_id', $orderId);
                $order->setMailchimpSent($item->getMailchimpSent());
                $order->setMailchimpSyncError($item->getMailchimpSyncError());
                $order->save();
            } catch (\Exception $e) {
                $this->helper->log($e->getMessage(). " for order [$orderId]");
            }
        }
        // UPDATE catalog_product_entity as A
        // INNER JOIN mailchimp_sync_ecommerce as B ON A.entity_id = B.related_id
        // SET A.mailchimp_sync_error = B.mailchimp_sync_error, A.mailchimp_sent = B.mailchimp_sent
        // WHERE B.type = 'PRO';
        try {
            $tableProducts = $this->moduleDataSetup->getTable('catalog_product_entity');
            $tableEcommerce = $this->moduleDataSetup->getTable('mailchimp_sync_ecommerce');
            $query = "UPDATE `$tableProducts` as A ";
            $query .= "INNER JOIN `$tableEcommerce` as B ON A.`entity_id` = B.`related_id` ";
            $query .= "SET A.`mailchimp_sync_error` = B.`mailchimp_sync_error`, A.`mailchimp_sent` = B.`mailchimp_sent` ";
            $query .= "WHERE B.`type` = 'PRO'";
            $this->helper->log($query);
            $this->moduleDataSetup->getConnection()->query($query);
        } catch (\Exception $e) {
            $this->helper->log($e->getMessage());
            throw new \Exception($e->getMessage());
        }

        $this->moduleDataSetup->getConnection()->endSetup();
    }

    public static function getDependencies()
    {
        return [];
    }
    public function getAliases()
    {
        return [];
    }

}

Enable the logs in the extension configuration and try to run the setup:upgrade This will generate entries in the file var/log/MailChimp.log and theses entries can help us to know what's happens

Best

[0xMatt]

The patch does not help.

The error is not related to the second query:

UPDATE `catalog_product_entity` as A INNER JOIN `mailchimp_sync_ecommerce` as B ON A.`entity_id` = B.`related_id`
SET A.`mailchimp_sync_error` = B.`mailchimp_sync_error`,
    A.`mailchimp_sent`       = B.`mailchimp_sent`
WHERE B.`type` = 'PRO';

This runs fine.

However, I did find the cause.

In my local environment, whenever I important production data, I have a script that clears sensitive data: customers, orders, vault, etc.

The script did not clear the mailchimp_sync_ecommerce table. Since the patch iterates through that table first then gets the order which does not exist, it still wasn't clear at first due to the method of getting the order and the logs:

[2023-07-07T13:12:08.868829+00:00] MailChimpLogger.INFO: Rolled back transaction has not been completed correctly. for order [7921]

I swapped out the OrderFactory with the OrderRepositoryInterface and the problem became clear after that:

$order = $this->orderRepository->get($orderId);
$order->setMailchimpSent($item->getMailchimpSent());
$order->setMailchimpSyncError($item->getMailchimpSyncError());
$this->orderRepository->save($order);

Which gave a more transparent error and cleared up any confusion immediately:

[2023-07-07T13:16:35.994933+00:00] MailChimpLogger.INFO: The entity that was requested doesn't exist. Verify the entity and try again. for order [7921]

While part of the problem was on my side, this should still never fail the setup:upgrade command.

Imagine a scenario where this passes in local but for some reason a record is missing in production and it fails there.

Maybe the site has an extension that deletes orders and does not delete the record from the mailchimp_ecommerce table. On top of not easily able to debug, it could be extremely stressful if you encounter this only in production.

I recommend a fix using the same method I used with the OrderRepositoryInterface. Not only does it give a more clear error message in the logs, it also allows the command to succeed.

[0xMatt]

I have created a PR for this.

[chrisastley]

Hi,

I tried the update with the PR applied but I'm getting issues with the patch on setup:upgrade.

We have approx 70k orders and get this error;

Module 'Ebizmarts_MailChimp': PHP Fatal error: Allowed memory size of 5368709120 bytes exhausted (tried to allocate 28672 bytes) in /var/www/vhosts/*****/htdocs/vendor/magento/framework/DB/Statement/Pdo/Mysql.php on line 90

Check https://getcomposer.org/doc/articles/troubleshooting.md#memory-limit-errors for more info on how to handle out of memory errors.{"messages":{"error":[{"code":500,"message":"Server internal error. See detai.....

As you can see I even tried temporarily increased the PHP limit to 5G.

In the end, I wrote the below SQL and commented out the code.

UPDATE sales_order as A INNER JOIN mailchimp_sync_ecommerce as B ON A.entity_id = B.related_id SET A.mailchimp_sync_error = B.mailchimp_sync_error, A.mailchimp_sent = B.mailchimp_sent WHERE B.type = 'ORD';

[Garde85]

I just tried to upgrade module and re-apply the patch with last fixes, but I have the same error.

Unable to apply data patch Ebizmarts\MailChimp\Setup\Patch\Data\Migrate452 for module Ebizmarts_MailChimp. Original exception message: Rolled back transaction has not been completed correctly.

If I comment these lines:

 foreach ($syncCollection as $item) {
        $orderId = $item->getRelatedId();
        try {
            $order = $this->orderRepository->get($orderId);
            if ($order && $order->getEntityId()==$orderId) {
                $order->setMailchimpSent($item->getMailchimpSent());
                $order->setMailchimpSyncError($item->getMailchimpSyncError());
                $this->orderRepository->save($order);
            } else {
                $this->helper->log("Order with id [$orderId] not found");
            }
        } catch (\Exception $e) {
            $this->helper->log($e->getMessage(). " for order [$orderId]");
        }
    }

patch is applied. Then the issue comes form query on sales_order table.

[sanderjongsma]

This migration is way to heavy for shops with a lot of orders. For a client we have about 100k sync ecommerce items with type ORD. And the complete order is loaded and saved with the repository inside a foreach, so this takes a very long time and will provide a lot of timeouts.

I would suggest fixing this the right way by not adding custom fields to the sales_order table but a separate table which is linked by the primary_key. In this way structural changes in the module and the database won't result in updating the sales_order table which can be very time consuming. This also applies to for example the quote, quote_item and sales_order_item tables.