Upgrade to Magento 2.2.4 - Magento_Sales UpgradeData.php - Memory Limit Exceeded / MySQL server has gone away

[adammprost]

Issue
When upgrading from 2.2.3 EE to 2.2.4 EE on a site with 4GB of existing data, running bin/magento setup:upgrade fails during the Magento_Sales UpgradeData.php script's fillQuoteAddressIdInSalesOrderAddress() method.

• Allowed memory size of X exceeded is thrown with a memory_limit of 2GB
• Increasing the memory_limit to 4GB allows the method to run for about 20 minutes until MySQL has gone away is thrown.

The problem is that the way the fillQuoteAddressIdInSalesOrderAddress() method is constructed it's looping through a fair amount of sales_order_address records (~600,000 in our case) and loading both the order and quote objects on every iteration. That consumes a lot of memory.

Solution
Rather than looping and loading full objects, the update should be done with a single query. See my suggestion here ->
module-sales-upgrade-performance-refactor.txt

[xantek-imc]

I hit this issue. The solution that worked for me was to is model->clearInstance(); to free memory.

$orderAddress->save();
$orderAddress->clearInstance();
$order->clearInstance();
$quote->clearInstance();

[rauberdaniel]

I created a SQL statement that does exactly what the fillQuoteAddressIdInSalesOrderAddress method in module-sales/Setup/UpgradeData.php does:

UPDATE sales_order_address AS oa LEFT JOIN sales_order AS o ON oa.parent_id = o.entity_id LEFT JOIN quote_address AS qa ON o.quote_id = qa.quote_id AND oa.address_type = qa.address_type SET oa.quote_address_id = qa.address_id WHERE oa.quote_address_id IS NULL AND qa.quote_id IS NOT NULL;

Executing this SQL statement should only take several seconds even for large databases. After this, you can simply add return; to the first line of the fillQuoteAddressIdInSalesOrderAddress method to prevent massive memory and time consumption in the setup:upgrade step.

[wojtekn]

@rauberdaniel FYI, someone fixed it for 2.2.7:

magento/magento2#16570

[henk-hypershop]

@rauberdaniel Great! This saved me a lot of time for databases with large order numbers. Thanks!