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 63ebd28f6f08b_users
DELETE FROM 63ebd28f6f08b_users WHERE ID = 0; ALTER TABLE 63ebd28f6f08b_users ADD PRIMARY KEY (ID); ALTER TABLE 63ebd28f6f08b_users ADD KEY user_login_key (user_login); ALTER TABLE 63ebd28f6f08b_users ADD KEY user_nicename (user_nicename); ALTER TABLE 63ebd28f6f08b_users ADD KEY user_email (user_email); ALTER TABLE 63ebd28f6f08b_users MODIFY ID bigint(20) unsigned NOT NULL auto_increment;
— Starting with 63ebd28f6f08b_usermeta
DELETE FROM 63ebd28f6f08b_usermeta WHERE umeta_id = 0; ALTER TABLE 63ebd28f6f08b_usermeta ADD PRIMARY KEY (umeta_id); ALTER TABLE 63ebd28f6f08b_usermeta ADD KEY user_id (user_id); ALTER TABLE 63ebd28f6f08b_usermeta ADD KEY meta_key (meta_key(191)); ALTER TABLE 63ebd28f6f08b_usermeta MODIFY umeta_id bigint(20) unsigned NOT NULL auto_increment;
— Starting with 63ebd28f6f08b_posts
DELETE FROM 63ebd28f6f08b_posts WHERE ID = 0; ALTER TABLE 63ebd28f6f08b_posts ADD PRIMARY KEY (ID); ALTER TABLE 63ebd28f6f08b_posts ADD KEY post_name (post_name(191)); ALTER TABLE 63ebd28f6f08b_posts ADD KEY type_status_date (post_type,post_status,post_date,ID); ALTER TABLE 63ebd28f6f08b_posts ADD KEY post_parent (post_parent); ALTER TABLE 63ebd28f6f08b_posts ADD KEY post_author (post_author); ALTER TABLE 63ebd28f6f08b_posts MODIFY ID bigint(20) unsigned NOT NULL auto_increment;
— Starting with 63ebd28f6f08b_comments
DELETE FROM 63ebd28f6f08b_comments WHERE comment_ID = 0; ALTER TABLE 63ebd28f6f08b_comments ADD PRIMARY KEY (comment_ID); ALTER TABLE 63ebd28f6f08b_comments ADD KEY comment_post_ID (comment_post_ID); ALTER TABLE 63ebd28f6f08b_comments ADD KEY comment_approved_date_gmt (comment_approved,comment_date_gmt); ALTER TABLE 63ebd28f6f08b_comments ADD KEY comment_date_gmt (comment_date_gmt); ALTER TABLE 63ebd28f6f08b_comments ADD KEY comment_parent (comment_parent); ALTER TABLE 63ebd28f6f08b_comments ADD KEY comment_author_email (comment_author_email(10)); ALTER TABLE 63ebd28f6f08b_comments MODIFY comment_ID bigint(20) unsigned NOT NULL auto_increment;
— Starting with 63ebd28f6f08b_links
DELETE FROM 63ebd28f6f08b_links WHERE link_id = 0; ALTER TABLE 63ebd28f6f08b_links ADD PRIMARY KEY (link_id); ALTER TABLE 63ebd28f6f08b_links ADD KEY link_visible (link_visible); ALTER TABLE 63ebd28f6f08b_links MODIFY link_id bigint(20) unsigned NOT NULL auto_increment;
— Starting with 63ebd28f6f08b_options
DELETE FROM 63ebd28f6f08b_options WHERE option_id = 0; ALTER TABLE 63ebd28f6f08b_options ADD PRIMARY KEY (option_id); ALTER TABLE 63ebd28f6f08b_options ADD UNIQUE KEY option_name (option_name); ALTER TABLE 63ebd28f6f08b_options ADD KEY autoload (autoload); ALTER TABLE 63ebd28f6f08b_options MODIFY option_id bigint(20) unsigned NOT NULL auto_increment;
— Starting with 63ebd28f6f08b_postmeta
DELETE FROM 63ebd28f6f08b_postmeta WHERE meta_id = 0; ALTER TABLE 63ebd28f6f08b_postmeta ADD PRIMARY KEY (meta_id); ALTER TABLE 63ebd28f6f08b_postmeta ADD KEY post_id (post_id); ALTER TABLE 63ebd28f6f08b_postmeta ADD KEY meta_key (meta_key(191)); ALTER TABLE 63ebd28f6f08b_postmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;
— Starting with 63ebd28f6f08b_terms
DELETE FROM 63ebd28f6f08b_terms WHERE term_id = 0; ALTER TABLE 63ebd28f6f08b_terms ADD PRIMARY KEY (term_id); ALTER TABLE 63ebd28f6f08b_terms ADD KEY slug (slug(191)); ALTER TABLE 63ebd28f6f08b_terms ADD KEY name (name(191)); ALTER TABLE 63ebd28f6f08b_terms MODIFY term_id bigint(20) unsigned NOT NULL auto_increment;
— Starting with 63ebd28f6f08b_term_taxonomy
DELETE FROM 63ebd28f6f08b_term_taxonomy WHERE term_taxonomy_id = 0; ALTER TABLE 63ebd28f6f08b_term_taxonomy ADD PRIMARY KEY (term_taxonomy_id); ALTER TABLE 63ebd28f6f08b_term_taxonomy ADD UNIQUE KEY term_id_taxonomy (term_id,taxonomy); ALTER TABLE 63ebd28f6f08b_term_taxonomy ADD KEY taxonomy (taxonomy); ALTER TABLE 63ebd28f6f08b_term_taxonomy MODIFY term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment;
— Starting with 63ebd28f6f08b_term_relationships
DELETE FROM 63ebd28f6f08b_term_relationships WHERE object_id = 0; DELETE FROM 63ebd28f6f08b_term_relationships WHERE term_taxonomy_id = 0; ALTER TABLE 63ebd28f6f08b_term_relationships ADD PRIMARY KEY (object_id,term_taxonomy_id); ALTER TABLE 63ebd28f6f08b_term_relationships ADD KEY term_taxonomy_id (term_taxonomy_id);
— Starting with 63ebd28f6f08b_termmeta
DELETE FROM 63ebd28f6f08b_termmeta WHERE meta_id = 0; ALTER TABLE 63ebd28f6f08b_termmeta ADD PRIMARY KEY (meta_id); ALTER TABLE 63ebd28f6f08b_termmeta ADD KEY term_id (term_id); ALTER TABLE 63ebd28f6f08b_termmeta ADD KEY meta_key (meta_key(191)); ALTER TABLE 63ebd28f6f08b_termmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;
— Starting with 63ebd28f6f08b_commentmeta
DELETE FROM 63ebd28f6f08b_commentmeta WHERE meta_id = 0; ALTER TABLE 63ebd28f6f08b_commentmeta ADD PRIMARY KEY (meta_id); ALTER TABLE 63ebd28f6f08b_commentmeta ADD KEY comment_id (comment_id); ALTER TABLE 63ebd28f6f08b_commentmeta ADD KEY meta_key (meta_key(191)); ALTER TABLE 63ebd28f6f08b_commentmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;
