Magento produces duplicate customer EAV entries instead of updating existing ones

In a recent Magento 1.8.0.0 project updating customer entities resulted in duplicate EAV entries instead of updating existing ones. Some of the symptoms of this problem were:

• Customer attributes not being updated, instead new duplicate entries produced
• Password reset not working, instead duplicate entries produced
• Login not working

In general, anything related to updating customer EAV entries caused duplicate entries. Furthermore, although new (duplicate) entries were generated still only the old values were taken into consideration. Thus, having a closer look at the corresponding database tables revealed that the unique indexes over entity_id and attribute_id for these tables were missing, e.g. customer_address_entity_datetime:

… which actually should look like this:

Due to the missing unique index over entity_id and attribute_id columns (here UNQ_CUSTOMER_ADDRESS_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID) entries were not updated but rather duplicated, as shown below for entity_id 37339 and attribute_id 29:

Apart from the missing unique index also the index for entity_id was missing too.

How to fix it

Note: Make sure to backup your database before adding the required indexes!

We need to add unique indexes over entity_id and attribute_id for all EAV tables. This includes tables ending in

1. _entity_datetime
2. _entity_decimal
3. _entity_int
4. _entity_text
5. _entity_varchar

But, since duplicate entries already exist we first need to clean up the corresponding tables.

Remove duplicate entries

In order to remove duplicates while keeping the newest values only we can use the SQL query for customer_address_entity_int, as shown below:

Again: Make sure to backup your database first!

DELETE caei1 
FROM customer_address_entity_int caei1,
customer_address_entity_int caei2 
WHERE caei1.value_id < caei2.value_id 
AND caei1.`entity_type_id` = caei2.`entity_type_id` 
AND caei1.`attribute_id` = caei2.`attribute_id` 
AND caei1.`entity_id` = caei2.`entity_id`;

In case you want to test it with one entity_id first simple append e.g.:

 
 ... AND caei1.`entity_id` = XYZ

Do this step for all related EAV tables.

Add unique indexes

Now that duplicates have been removed we can add the required unique indexes over entity_id and attribute_id for all related tables. For instance, the following query adds a unique index over entity_id and attribute_id for table customer_entity_varchar:

 
ALTER TABLE `magento`.`customer_entity_varchar` 
ADD UNIQUE `UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID` ( `entity_id` , `attribute_id` ) 

Furthermore, check that there exists an index for entity_id too (this was missing in this setup too):

ALTER TABLE `customer_entity_varchar` 
ADD INDEX `IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID` ( `entity_id` )

That’s it! Based on the added (and required) unique indexes Magento will automatically overwrite any existing EAV entrues, thus updating them instead of creating new ones.