Lost your WordPress table indexes, keys and auto_increment fields ? How to re add your primary keys? Lost all that when moving databases?
You could use some searching on the web and and up with a whole lot of manual work copying from pages with outdated database queries. Including guessing/estimating of your next autoincrement value.
This script will fix the following database symptoms and errors based on a missing primary key:
- PHP errors in your error log: “WordPress database error: [Duplicate entry ‘0’ for key ‘PRIMARY’]”
- You trying to re-add the primary key with increment resulting in “#1075 – Incorrect table definition; there can be only one auto column and it must be defined as a key”
- Not able to create new pages, all pages have the Publish button replaced with Submit for review.
No permalink, just “?preview=true”
This will re-add indexes, keys, primary indexes and enable auto-increment.
Read carefully: if you really do have primary keys (ask your webhost or webmaster) right now this script is not for you.
We even have fixes for you to delete rows where your primary key is lost and the “primary key” values are zero (0). The script will remove those corrupted rows.
The script will give you DELETE and MODIFY/ALTER table queries. You must copy paste them into your phpmyadmin or database commandline.
Warning: this is tested on mysql and mariadb for a single WP Core site (not a multisite), it might not work on your site, so make a backup first.
This will only fix your WordPress Core tables (users, posts etc..) it will not fix any other non-WP-Core tables (such as GravityForms or any other plugin or custom tables).
These are the queries for WP version 6.1.1
You need to replace 63ebd28f6f08b_ with your site prefix. This could be wp_ or something else. @see https://wordpress.org/support/article/editing-wp-config-php/#table_prefix where to find your own prefix.
-- Starting with 64722001ab277_users
DELETE FROM 64722001ab277_users WHERE ID = 0;
ALTER TABLE 64722001ab277_users ADD PRIMARY KEY (ID);
ALTER TABLE 64722001ab277_users ADD KEY user_login_key (user_login);
ALTER TABLE 64722001ab277_users ADD KEY user_nicename (user_nicename);
ALTER TABLE 64722001ab277_users ADD KEY user_email (user_email);
ALTER TABLE 64722001ab277_users MODIFY ID bigint(20) unsigned NOT NULL auto_increment;
-- Starting with 64722001ab277_usermeta
DELETE FROM 64722001ab277_usermeta WHERE umeta_id = 0;
ALTER TABLE 64722001ab277_usermeta ADD PRIMARY KEY (umeta_id);
ALTER TABLE 64722001ab277_usermeta ADD KEY user_id (user_id);
ALTER TABLE 64722001ab277_usermeta ADD KEY meta_key (meta_key(191));
ALTER TABLE 64722001ab277_usermeta MODIFY umeta_id bigint(20) unsigned NOT NULL auto_increment;
-- Starting with 64722001ab277_posts
DELETE FROM 64722001ab277_posts WHERE ID = 0;
ALTER TABLE 64722001ab277_posts ADD PRIMARY KEY (ID);
ALTER TABLE 64722001ab277_posts ADD KEY post_name (post_name(191));
ALTER TABLE 64722001ab277_posts ADD KEY type_status_date (post_type,post_status,post_date,ID);
ALTER TABLE 64722001ab277_posts ADD KEY post_parent (post_parent);
ALTER TABLE 64722001ab277_posts ADD KEY post_author (post_author);
ALTER TABLE 64722001ab277_posts MODIFY ID bigint(20) unsigned NOT NULL auto_increment;
-- Starting with 64722001ab277_comments
DELETE FROM 64722001ab277_comments WHERE comment_ID = 0;
ALTER TABLE 64722001ab277_comments ADD PRIMARY KEY (comment_ID);
ALTER TABLE 64722001ab277_comments ADD KEY comment_post_ID (comment_post_ID);
ALTER TABLE 64722001ab277_comments ADD KEY comment_approved_date_gmt (comment_approved,comment_date_gmt);
ALTER TABLE 64722001ab277_comments ADD KEY comment_date_gmt (comment_date_gmt);
ALTER TABLE 64722001ab277_comments ADD KEY comment_parent (comment_parent);
ALTER TABLE 64722001ab277_comments ADD KEY comment_author_email (comment_author_email(10));
ALTER TABLE 64722001ab277_comments MODIFY comment_ID bigint(20) unsigned NOT NULL auto_increment;
-- Starting with 64722001ab277_links
DELETE FROM 64722001ab277_links WHERE link_id = 0;
ALTER TABLE 64722001ab277_links ADD PRIMARY KEY (link_id);
ALTER TABLE 64722001ab277_links ADD KEY link_visible (link_visible);
ALTER TABLE 64722001ab277_links MODIFY link_id bigint(20) unsigned NOT NULL auto_increment;
-- Starting with 64722001ab277_options
DELETE FROM 64722001ab277_options WHERE option_id = 0;
ALTER TABLE 64722001ab277_options ADD PRIMARY KEY (option_id);
ALTER TABLE 64722001ab277_options ADD UNIQUE KEY option_name (option_name);
ALTER TABLE 64722001ab277_options ADD KEY autoload (autoload);
ALTER TABLE 64722001ab277_options MODIFY option_id bigint(20) unsigned NOT NULL auto_increment;
-- Starting with 64722001ab277_postmeta
DELETE FROM 64722001ab277_postmeta WHERE meta_id = 0;
ALTER TABLE 64722001ab277_postmeta ADD PRIMARY KEY (meta_id);
ALTER TABLE 64722001ab277_postmeta ADD KEY post_id (post_id);
ALTER TABLE 64722001ab277_postmeta ADD KEY meta_key (meta_key(191));
ALTER TABLE 64722001ab277_postmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;
-- Starting with 64722001ab277_terms
DELETE FROM 64722001ab277_terms WHERE term_id = 0;
ALTER TABLE 64722001ab277_terms ADD PRIMARY KEY (term_id);
ALTER TABLE 64722001ab277_terms ADD KEY slug (slug(191));
ALTER TABLE 64722001ab277_terms ADD KEY name (name(191));
ALTER TABLE 64722001ab277_terms MODIFY term_id bigint(20) unsigned NOT NULL auto_increment;
-- Starting with 64722001ab277_term_taxonomy
DELETE FROM 64722001ab277_term_taxonomy WHERE term_taxonomy_id = 0;
ALTER TABLE 64722001ab277_term_taxonomy ADD PRIMARY KEY (term_taxonomy_id);
ALTER TABLE 64722001ab277_term_taxonomy ADD UNIQUE KEY term_id_taxonomy (term_id,taxonomy);
ALTER TABLE 64722001ab277_term_taxonomy ADD KEY taxonomy (taxonomy);
ALTER TABLE 64722001ab277_term_taxonomy MODIFY term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment;
-- Starting with 64722001ab277_term_relationships
DELETE FROM 64722001ab277_term_relationships WHERE object_id = 0;
DELETE FROM 64722001ab277_term_relationships WHERE term_taxonomy_id = 0;
ALTER TABLE 64722001ab277_term_relationships ADD PRIMARY KEY (object_id,term_taxonomy_id);
ALTER TABLE 64722001ab277_term_relationships ADD KEY term_taxonomy_id (term_taxonomy_id);
-- Starting with 64722001ab277_termmeta
DELETE FROM 64722001ab277_termmeta WHERE meta_id = 0;
ALTER TABLE 64722001ab277_termmeta ADD PRIMARY KEY (meta_id);
ALTER TABLE 64722001ab277_termmeta ADD KEY term_id (term_id);
ALTER TABLE 64722001ab277_termmeta ADD KEY meta_key (meta_key(191));
ALTER TABLE 64722001ab277_termmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;
-- Starting with 64722001ab277_commentmeta
DELETE FROM 64722001ab277_commentmeta WHERE meta_id = 0;
ALTER TABLE 64722001ab277_commentmeta ADD PRIMARY KEY (meta_id);
ALTER TABLE 64722001ab277_commentmeta ADD KEY comment_id (comment_id);
ALTER TABLE 64722001ab277_commentmeta ADD KEY meta_key (meta_key(191));
ALTER TABLE 64722001ab277_commentmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;
ONE SHOT
DELETE FROM wp_termmeta WHERE meta_id=0;
DELETE FROM wp_terms WHERE term_id=0;
DELETE FROM wp_term_taxonomy WHERE term_taxonomy_id=0;
DELETE FROM wp_commentmeta WHERE meta_id=0;
DELETE FROM wp_comments WHERE comment_ID=0;
DELETE FROM wp_links WHERE link_id=0;
DELETE FROM wp_options WHERE option_id=0;
DELETE FROM wp_postmeta WHERE meta_id=0;
DELETE FROM wp_users WHERE ID=0;
DELETE FROM wp_posts WHERE ID=0;
DELETE FROM wp_usermeta WHERE umeta_id=0;
ALTER TABLE wp_termmeta ADD PRIMARY KEY(meta_id);
ALTER TABLE wp_terms ADD PRIMARY KEY(term_id);
ALTER TABLE wp_term_taxonomy ADD PRIMARY KEY(term_taxonomy_id);
ALTER TABLE wp_commentmeta ADD PRIMARY KEY(meta_id);
ALTER TABLE wp_comments ADD PRIMARY KEY(comment_ID);
ALTER TABLE wp_links ADD PRIMARY KEY(link_id);
ALTER TABLE wp_options ADD PRIMARY KEY(option_id);
ALTER TABLE wp_postmeta ADD PRIMARY KEY(meta_id);
ALTER TABLE wp_users ADD PRIMARY KEY(ID);
ALTER TABLE wp_posts ADD PRIMARY KEY(ID);
ALTER TABLE wp_usermeta ADD PRIMARY KEY(umeta_id);
ALTER TABLE wp_termmeta CHANGE meta_id meta_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_terms CHANGE term_id term_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_term_taxonomy CHANGE term_taxonomy_id term_taxonomy_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_commentmeta CHANGE meta_id meta_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_comments CHANGE comment_ID comment_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_links CHANGE link_id link_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_options CHANGE option_id option_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_postmeta CHANGE meta_id meta_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_users CHANGE ID ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_posts CHANGE ID ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_usermeta CHANGE umeta_id umeta_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
