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

[gtlt]

Preconditions

1. Magento 2.2.3
2. PHP 7.1.16
3. MariaDB 10.2.9

Steps to reproduce

1. composer update 2.2.3 to 2.2.4
2. upgrade Magento_Sales data: current version - 2.0.7, required version - 2.0.9

Expected result

1. update ok

Actual result

1. upgrade logs :
   Module 'Magento_Sales':
   Upgrading data.. We can't save the address:
   Email has a wrong format

In which table should I fix emails ? I have email Null values with the following request

SELECT * FROM `sales_order_address` where email is NULL
SELECT * FROM `sales_order_address` where email is null
SELECT * FROM `quote_address` where email is null ORDER BY `created_at` DESC

[gtlt]

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;

[webspeaks]

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

[KM-HM]

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.

[kabadabra]

I am also affected by this issue:

Module 'Magento_Sales':
Upgrading data.. We can't save the address:
Email has a wrong format

Updating sales_order_address and quote_address with a fake email has not worked for me. Anyone else have another solution to get passed this? Other tables that might need to be checked/email faked as well?

[kamranmalikawan]

Any update on that i am facing same issue

Module 'Magento_Sales':
Upgrading data.. We can't save the address:
Address type doesn't match required options

Thanks,

[kamranmalikawan]

@kabadabra have you find any solution ??

[ComputerNorth]

I was affected by this issue too. Solution from gtlt resolved this issue. But I also agree that setting a dummy email address should not be the correct solution.

[hanego]

Based on gtlt solution, still not perfect but this query works too:
update sales_order_address a left join customer_entity b on a.customer_id = b.entity_id set a.email = b.email where a.email is null

[Schoeny1]

@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

[niners52]

After I fix the emails I get this error- Missing required argument $options of Magento\Eav\Model\Entity\Attribute\Source\Config. https://imgur.com/a/SqhERi1 It happens on the exact same step. Anyone else getting this?

[kabadabra]

@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,}$';

2. 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

4. 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!

[maderlock]

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

[salelsol]

Same issue:
Module 'Magento_Sales':
Upgrading data.. We can't save the address:
Email has a wrong format
but any solution works for me. Any more solution ??

[salelsol]

@kamranmalikawan; any solution works for me. Any more solution ??

[ghost]

Hi @gtlt I cannot reproduce your scenario, see attachments

[wojtekn]

@engcom-backlog-nazar I also spot that scenario. In database migrated from M1 we had:

• order records with customer_email = null
• order_adress records with email = null
• quote_address records with email = null

Also, we had many invalid entries in these fields eg.:

• email with spaces inside
• email with trailing spaces
• other address fields entered in email field by mistake eg. street or country
• completely invalid emails like emails with two @ characters, without any @, without domain etc.

I think this issue may be related to issue described in magefan/module-blog#169

In general, upgrade script uses very expensive logic to update one field in the order address records instead of just running one MySQL query. It fetches all addresses from the database, the loops trough them - loading order and quote objects for each and then saving the address. The side effect of this is that it runs order and quote address validations, what causes issue reported above.

[ghost]

@wojtekn , problems with data migration from 1.x must be reported in https://github.com/magento/data-migration-tool repository.

[ghost]

@wojtekn -> magefan/module-blog#169 -> Please follow these guidelines for proper tracking of your issue. You can report Commerce-related issues in one of two ways:
You can use the Support portal associated with your account
or
If you are a Partner reporting on behalf of a merchant, use the Partner portal.

GitHub is intended for Magento Open Source users to report on issues related to Open Source only. There are no account management services associated with GitHub.

[wojtekn]

@engcom-backlog-nazar what I said above is not really data migration problem, it's Magento 2 setup upgrade problem. Please review referenced issue.

Also, I'm not a bug reporter here, I just wanted to help as I felt you are trying to close the ticket with "we cannot reproduce" status.

[ghost]

@wojtekn In described scenario i have no error ,i try many times but have no errors

[niners52]

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.

[wojtekn]

@niners52 👍 😄

[ghost]

@niners52 seems like problem not in upgrade script, when you run upgrade script, its already have email is null, in my case when i upgrade magento to 2.2.4 i have no email is null. so have no error

[niners52]

Did you migrate a full Magento 1 database before you did that? I have opened several tickets on the data migration tool and the responses are never speedy, but if you want me to write this up and put it on the repository I can. The other option is that maybe you reach out to Victor since you work for the same company.

[ghost]

@niners52 No ->
Steps to reproduce
composer update 2.2.3 to 2.2.4
upgrade Magento_Sales data: current version - 2.0.7, required version - 2.0.9

[niners52]

That is great for you guys, but we don't live in a world with empty databases. The people on this thread have data. I had a ticket in with the Magento support that has my db if you want to talk to them.

[ghost]

@niners52 I have installed sample data with 45 customer and others data e.g 'orders,products'

[niners52]

I am sure your sample data wasn't migrated though. For me I have been working on my m2 site for about 6 months. I had 2.2.2 and migrated to 2.2.3 with no problems. It was the migration from 2.2.3 to 2.2.4 that this error happened. Look at what was changed in 2.2.4. There had to have been something that looks at addresses. Once you isolate that talk to Victor and see if he has any idea on why migrated data only would cause that error.

[ghost]

@gtlt Can you provide more information ?

[aniruddh-capital]

I would recommend using this query instead of dummy or wrong email addresses to keep the sales data accurate. I just resolved the issue on mine right now

update sales_order_address a left join sales_order_address b on a.parent_id = b.parent_id set a.email = b.email where a.email is null

@gtlt @maderlock @kabadabra @hanego @niners52

now this issue is most probably because the shipping address rows do not require an email address in the email column and only billing address row does
but the upgrade is asking for it as a mandatory now, I guess?

and to replicate the issue you have to place orders in 2.2.2 and then try to upgrade
@engcom-backlog-nazar

[rparsi]

@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.

[gtlt]

@engcom-backlog-nazar I don't have much more info, the last null entry creation date I spotted is in february 2017, so from an order made under M2 2.1.x (I migrated my data from M1 in late 2016 to ~2.1.3 I think).
Since it is a one query fix, I don't have time to dig into but IMO, like @rparsi said if the correct behavior is the upgrade's one, then we should have :

• fill null with query suggested by @hanego @maderlock and others
• if null entries remain then upgrade fails
• if ok the column must be upgraded to non nullable

[ghost]

Hi @gtlt HI @rparsi I'm tested with-> update sales_order_address set email=null parent_id=null address_id=null; update quote_address set email=null Then run setup upgrade-> debug point on

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

I'm not have an error, filed not updated if i only delete the adress_id,
the method fillQuoteAddressIdInSalesOrderAddress saves ids.

[ghost]

@gtlt thank you for you report. The fix for this issue will be available in 2.2.7 release #16570

[pietervanuhm]

@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, '', '');

[fwolfst]

Can happens on updates to 2.2.6, too.