“Duplicate Entry for Unique Key” Error when Migrating/Restoring Site
SearchWP creates five custom database tables in order to maintain its index. The structure of these tables is very important as the overall indexing and searching process heavily relies on some features of MySQL to operate properly.
One such feature is a dependence on character encoding supported by your installation of MySQL. When SearchWP creates its necessary custom database tables, it checks with your MySQL installation to see whether certain features are supported. Based on the feedback from MySQL, the database tables may be created in a slightly different way.
An important attribute of one specific database table is a UNIQUE key, which ensures that any data in that column is not repeated. This UNIQUE key is integral to the proper operation of SearchWP.
Potential issue for migration/restoring
Because SearchWP creates database tables based on the current MySQL environment, you can sometimes run into issues when restoring from backup or migrating the site to a different server with a different version of MySQL, resulting in the following error being displayed:
Duplicate entry '?' for key 'termunique'
This is due to either a difference in MySQL versions resulting in an incompatibility, or an issue with the export/backup (or potentially import) method used.
Resolution
The way to get around this issue is to exclude SearchWP’s database tables from the migration/restore process entirely.
Once the site has been migrated, you can visit the SearchWP settings page to have the database tables recreated according to the new environment, and the index will rebuild itself.
SearchWP’s database tables are as follows:
wp_swp_cfwp_swp_indexwp_swp_logwp_swp_taxwp_swp_terms
Please note that your table prefix likely differs from wp_ so you will need to keep that in mind.