cancel
Showing results for 
Search instead for 
Did you mean: 

Schema Upgrade Error going from 2.2.10 to 2.3.3

Schema Upgrade Error going from 2.2.10 to 2.3.3

I ran into this problem late last year when testing upgrading from 2.2.9 to 2.3.3, but decided to just installed 2.2.10 at the time which didn't have the problem.

 

Now that I'm trying the upgrade to 2.3.3 again, I'm running into the same error on the 'Schema creation/updates' step of the upgrade command.

 

I'm not great with SQL statements for databases I didn't build. Any help would be appreciated.

 

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'CUSTOMER_ENTITY_EMAIL_WEBSITE_ID', query was: ALTER TABLE `customer_entity` MODIFY COLUMN `entity_id` int(10) UNSIGNED NOT NULL  AUTO_INCREMENT COMMENT "Entity ID", MODIFY COLUMN `website_id` smallint(5) UNSIGNED NULL   COMMENT "Website ID", MODIFY COLUMN `email` varchar(255) NULL  COMMENT "Email", MODIFY COLUMN `group_id` smallint(5) UNSIGNED NOT NULL DEFAULT 0  COMMENT "Group ID", MODIFY COLUMN `increment_id` varchar(50) NULL  COMMENT "Increment Id", MODIFY COLUMN `store_id` smallint(5) UNSIGNED NULL DEFAULT 0  COMMENT "Store ID", MODIFY COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT "Created At", MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "Updated At", MODIFY COLUMN `created_in` varchar(255) NULL  COMMENT "Created From", MODIFY COLUMN `prefix` varchar(40) NULL  COMMENT "Name Prefix", MODIFY COLUMN `firstname` varchar(255) NULL  COMMENT "First Name", MODIFY COLUMN `middlename` varchar(255) NULL  COMMENT "Middle Name/Initial", MODIFY COLUMN `lastname` varchar(255) NULL  COMMENT "Last Name", MODIFY COLUMN `suffix` varchar(40) NULL  COMMENT "Name Suffix", MODIFY COLUMN `password_hash` varchar(128) NULL  COMMENT "Password_hash", MODIFY COLUMN `rp_token` varchar(128) NULL  COMMENT "Reset password token", MODIFY COLUMN `default_billing` int(10) UNSIGNED NULL   COMMENT "Default Billing Address", MODIFY COLUMN `default_shipping` int(10) UNSIGNED NULL   COMMENT "Default Shipping Address", MODIFY COLUMN `taxvat` varchar(50) NULL  COMMENT "Tax/VAT Number", MODIFY COLUMN `confirmation` varchar(64) NULL  COMMENT "Is Confirmed", MODIFY COLUMN `gender` smallint(5) UNSIGNED NULL   COMMENT "Gender", ADD CONSTRAINT `CUSTOMER_ENTITY_EMAIL_WEBSITE_ID` UNIQUE KEY (`email`,`website_id`), ADD CONSTRAINT `CUSTOMER_ENTITY_WEBSITE_ID_STORE_WEBSITE_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `store_website` (`website_id`)  ON DELETE SET NULL, ADD CONSTRAINT `CUSTOMER_ENTITY_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`)  ON DELETE SET NULL

I've tried the following to fix it

ALTER TABLE customer_entity
DROP INDEX CUSTOMER_ENTITY_EMAIL_WEBSITE_ID;
ALTER TABLE customer_entity
DROP FOREIGN KEY CUSTOMER_ENTITY_STORE_ID_STORE_STORE_ID;
ALTER TABLE customer_entity
DROP FOREIGN KEY CUSTOMER_ENTITY_WEBSITE_ID_STORE_WEBSITE_WEBSITE_ID;

 

But that just leads me to a slightly different error. 

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'replacedemail@hotmail.com-1' for key 'CUSTOMER_ENTITY_EMAIL_WEBSITE_ID', query was: ALTER TABLE `customer_entity` MODIFY COLUMN `entity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Entity ID", MODIFY COLUMN `website_id` smallint(5) UNSIGNED NULL COMMENT "Website ID", MODIFY COLUMN `email` varchar(255) NULL COMMENT "Email", MODIFY COLUMN `group_id` smallint(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT "Group ID", MODIFY COLUMN `increment_id` varchar(50) NULL COMMENT "Increment Id", MODIFY COLUMN `store_id` smallint(5) UNSIGNED NULL DEFAULT 0 COMMENT "Store ID", MODIFY COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT "Created At", MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "Updated At", MODIFY COLUMN `created_in` varchar(255) NULL COMMENT "Created From", MODIFY COLUMN `prefix` varchar(40) NULL COMMENT "Name Prefix", MODIFY COLUMN `firstname` varchar(255) NULL COMMENT "First Name", MODIFY COLUMN `middlename` varchar(255) NULL COMMENT "Middle Name/Initial", MODIFY COLUMN `lastname` varchar(255) NULL COMMENT "Last Name", MODIFY COLUMN `suffix` varchar(40) NULL COMMENT "Name Suffix", MODIFY COLUMN `password_hash` varchar(128) NULL COMMENT "Password_hash", MODIFY COLUMN `rp_token` varchar(128) NULL COMMENT "Reset password token", MODIFY COLUMN `default_billing` int(10) UNSIGNED NULL COMMENT "Default Billing Address", MODIFY COLUMN `default_shipping` int(10) UNSIGNED NULL COMMENT "Default Shipping Address", MODIFY COLUMN `taxvat` varchar(50) NULL COMMENT "Tax/VAT Number", MODIFY COLUMN `confirmation` varchar(64) NULL COMMENT "Is Confirmed", MODIFY COLUMN `gender` smallint(5) UNSIGNED NULL COMMENT "Gender", ADD CONSTRAINT `CUSTOMER_ENTITY_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE SET NULL, ADD CONSTRAINT `CUSTOMER_ENTITY_WEBSITE_ID_STORE_WEBSITE_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `store_website` (`website_id`) ON DELETE SET NULL, ADD CONSTRAINT `CUSTOMER_ENTITY_EMAIL_WEBSITE_ID` UNIQUE KEY (`email`,`website_id`)

 

1 REPLY 1

Re: Schema Upgrade Error going from 2.2.10 to 2.3.3

HI @brent_plumme 

It looks like some duplicate entries in the database for the customers.

check once for below email for customer_entity table.

If you found duplicate records, then delete them and try.

Also check that your customer account sharing configuration is set to website or global.

I hope it will help you!