Magento 2 Customer Addresses with Invalid Region Data

[Zoe Hell]

When these customers want to make a purchase again, they have difficulties checking out. They see the message about their address not being valid and have to go to their account to checkout from there. You can also get some checkout errors if you use some custom payment or shipping methods.

It is not very user-friendly since it requires more time than your customers have. They can leave without buying anything that is not what any store owner would want.

What is the solution?

[LitCommerce Expert]

Run the following SQL query:

SELECT value FROM core_config_data WHERE path LIKE 'general/region/state_required' 

You will get the country codes separated by commas, like in this example: AU,BG,BR,CA,CH,CN,CO,EE,ES,HR,IN,IT,LT,LV,MX,PL,RO,US,UY

Then, you need to convert them into country codes in quotes separated by commas: 'AU','BG','BR','CA','CH','CN','CO','EE','ES','HR','IN','IT','LT','LV','MX','PL','RO','US','UY'

Once you have the country codes in quotes, you have to run 3 more queries. In the first one you need to use the country codes you got earlier:

DELETE FROM customer_address_entity WHERE country_id IN ('AU','BG','BR','CA','CH','CN','CO','EE','ES','HR','IN','IT','LT','LV','MX','PL','RO','US','UY') AND region IS NULL or region_id is NULL;
UPDATE customer_entity SET default_billing = null WHERE default_billing IS NOT NULL AND default_billing NOT IN (SELECT entity_id from customer_address_entity);
UPDATE customer_entity SET default_shipping = null WHERE default_shipping IS NOT NULL AND default_shipping NOT IN (SELECT entity_id from customer_address_entity);