I got this error when I changed the hosting of one of my WordPress websites:
WordPress database error: [Duplicate entry '0' for key 'wp_postmeta.PRIMARY']
INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (100, '_last_editor_used_jetpack', 'block-editor')And I assume something’s got overlapped during the migration where the AUTO_INCREMENT values are wrongly registered by WordPress engine.
To fix this, just by reading the error message, I needed to update the AUTO_INCREMENT on the wp_postmeta table (after finding out the latest ID in wp_postmeta):
> SELECT MAX(meta_id) FROM wp_postmeta;
> ALTER TABLE wp_postmeta AUTO_INCREMENT=XXX;
What also happened to me weirdly was that table wp_postmeta had also lost the indexing on meta_id column, so I had to also run the following:
> ALTER TABLE wp_postmeta MODIFY COLUMN meta_id INT UNSIGNED NOT NULL AUTO_INCREMENT DEFAULT 0;
> ALTER TABLE wp_postmeta ADD INDEX meta_id (meta_id);
Well, there you go. Hope you found this useful!