Error adding index UNIQUE key to category_product table

[Wajalot]

Hi. I just updated succesfully from 1.4.6.2 to 1.4.7. However an error came out.

 

"ALTER TABLE `ps_category_product` ADD UNIQUE `category_product_index` (`id_category`,`id_product`)

(1062) Duplicate entry '342-17927' for key 'category_product_index'"

 

I checked the database and deleted that line as it was duplicated. Then checking again looking for more duplicates i realised there are thousands of them. Store seems to work fine, however I guess missing that index key the searching action won't be as fast as expected.

 

How can I delete, using mysql sentence into the phpmyadmin or any other way, all the duplicates lines?

 

Thanks in advance!

[bellini13]

you could try this approach, but first, stop reading this, go take a full database backup, and then come back.

 

now continue...

1) using phpmyadmin, export the entire ps_category_product table to a .sql file and store on your pc

2) truncate the ps_category_product table, so that it deletes all data. do not delete the table

3) add the unique index that is failing

4) import the .sql from step 1, choose the ignore errors option. this will add all records back to the table, except the duplicates

 

my store is currently on 1.4.6.2, and i have this index on my table already, without duplicates. so 1.4.7 did not introduce this index. i'm curious what happened there.

[Wajalot]

Thanks mate. I already followed your steps. I didn't find the option to skip interruption for errors using sql. However, I think it depends on phpmyadmin version, for CSV files could do it. now seems fine.

 

I checked the upgrade sql file into the prestashop 1.4.7.0 package and one of the instructions is the addition of that index key, so, it's quite odd you already have it using the 1.4.6.2 version.

[bellini13]

the index has always existed, even going back to v1.3.7 of prestashop, so it does not surprise me that I have it.

 

what surprises me is that you didn't have it in your 1.4.6.2 instance. perhaps you did an upgrade previously to v1.4.6.2?