magento2: Magento_Sales module fails to upgrade (email has a wrong format)

It was related to null entry email in sales_order_address. resolved by

update `sales_order_address` SET email = 'foo@foo.com' where email is null;

@kamranmalikawan The solution from @gtlt worked for me, but it did not work at first. We are running Magento 2 Commerce and got their support to help me with a solution. Here is what I ended up doing:

1. I ran the following sql to help indentify which emails were incorrectly formatted on the table “sales_order_address”:

SELECT * FROM `sales_order_address` WHERE `email` NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$';

1. Similar to the above, I also checked the “quote_address” table just in case too:

SELECT * FROM `quote_address` WHERE `email` NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$';

1. and 2. above I had a handful of emails that were not email addresses at all and fixed these up. Having already done @gtlt solution on NULL values, I had already replaced my NULL emails with noemail@foo.com. It should work with a NULL value if all rows are correct, but I can’t be 100% sure. For me having a dummy email is ok.

2. I ran the following sql to make sure all my NULL values had dummy emails

UPDATE `sales_order_address` SET `email` = 'noemail@foo.com' WHERE `email` IS NULL

1. I had two emails which 1. and 2. didnt pick up with a <script> tag in it that might have been injected somewhere in Magento 1 days. I fixed these up as well.

After the above, everything upgraded and deployed smoothly. In short, just make sure all the data in the email column are valid email addresses. Hope this helps!

For missing email addresses, I’ve combined two approaches above to use the order email address where available and otherwise use a dummy address:

update sales_order_address a left join sales_order o on a.parent_id = o.entity_id set a.email = coalesce(o.customer_email, 'noemail@dummy.com') where a.email is null

But why do we have this issue while upgrading to 2.2.4. In my case all rows in sales_order_address table have null value in email column. Is the email column newly added in the upgrade? Setting dummy email to the record doesn’t seem correct. What is the correct way? @magento-engcom-team

@gtlt To check all email fields on special characters or other symbols, i did this. Because I got ‘Invalid email format’ and searched my ass off when i treid to setup:upgrade

I used the update query to update rows with multiple the same issues

SELECT * FROM quote_address WHERE email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$'; SELECT * FROM sales_order_addressWHEREemail NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$'; UPDATE quote_addressSETemail = REPLACE(email, '', ''); UPDATE sales_order_addressSETemail = REPLACE(email, '', '');

@magento-engcom-team @engcom-backlog-nazar @gtlt The problem persists in magento 2.2.5, issue appears to be inconsistent code:

In \Magento\Sales\Setup\UpgradeData::upgrade method:

if (version_compare($context->getVersion(), '2.0.8', '<')) {
            $this->state->emulateAreaCode(
                \Magento\Backend\App\Area\FrontNameResolver::AREA_CODE,
                [$this, 'fillQuoteAddressIdInSalesOrderAddress'],
                [$setup]
            );
        }

method \Magento\Sales\Setup\UpgradeData::fillQuoteAddressIdInSalesOrderAddress sets the quote_address_id in sales_order_address; the code saves to the database the entity \Magento\Sales\Model\Order\Address which in turn relies on \Magento\Sales\Model\Order\Address\Validator::validate to apply validation by the resource model.

It’s \Magento\Sales\Model\Order\Address\Validator that enforces the valid email check. However \Magento\Sales\Setup\UpgradeSchema does NOT change the sales_order_address.quote_address_id column, it IS STILL nullable.

So why is \Magento\Sales\Setup\UpgradeData::fillQuoteAddressIdInSalesOrderAddress necessary when the column it affects is still nullable? Is the column not supposed to be nullable under any condition?

Magento team please clarify. Thank you.

It appears to happen to people who have migrated from Magento 1. If you open it up as a ticket on the data migration tool repository he will say everything migrated correctly and it is a problem with the upgrade script.

@gtlt solution worked for me too. Just make sure you rm -rf var/cache/* rm -rf var/page_cache/* rm -rf generated/code/* Before running the magento setup:upgrade again

I was affected by this issue too.

Solution from gtlt resolved this issue. But I also agree with @webspeaks, setting a dummy email address should not be the correct solution.