WPDB WP Core Database SQL index, primary keys fix script

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..

We wrote a script that parses the build-in schedule.php from the most current version of WordPress ( @see wp-admin/includes/schema.php ) . We translate it in realtime all to some queries that are up to date. So the output will be up to date for all WordPress versions. It is not a fixed script output for a specific version, it will show the latest version of WP Core.
And .. 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. It will also enable auto increments.
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).

Call the free fixer script here. You only have to rename the database prefix to wp_ or whatever your table prefix is (see you wp-config.php for that).

Or buy the fixer script here to run it on your WordPress server, that will automatically set your database prefix to YOUR own prefix and it will do an extra check and show you all the tables without any keys.

This script will fix the following database symptoms and errors based on a missing primary key:

  1. PHP errors in your error log: “WordPress database error: [Duplicate entry ‘0’ for key ‘PRIMARY’]”
  2. 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”
  3. Not able to create new pages, all pages have the Publish button replaced with Submit for review. (See Dutch screenshot “Inzenden voor beoordeling”)
    No permalink, just “?preview=true”

8 Comments

Kurt

July 18, 2019 at 1:58 pm

Thank you very much. This fixed my media library. I couldn’t upload. setting wp_posts ID field and wp_postmeta meta ID field to auto increment

 Reply

Lisa Lawton

August 7, 2019 at 6:11 pm

you are a superhero – this worked like a dream for me after hours researching i came across your comment in stackexchange with this link. Thank you a million time

 Reply

Dony

October 6, 2019 at 3:38 pm

Thanks a lot. it is works . save my time

 Reply

Cindy

January 19, 2020 at 10:11 pm

Thank you soooo much !!!
I’ve lost so much time to find where was the problem, you help me to fix it so quickly.

In Notepad, Ctrl + F, select the prefix and remplace with your database prefix to save more time again.

Have a good day !

 Reply

Bruce Smith

March 5, 2020 at 9:41 pm

Wow – thank you so much. I just wasted hours looking for a solution and was finally able to find this link.
I ran this query using phpmyadmin and it fixed the problem completely – I was not able to upload pictures or create new posts. Thanks again for this solution – I really appreciate it.

P.S. I used Word to find/replace the prefix with wp_

 Reply

WALI91

May 1, 2020 at 11:17 pm

Merci, Thank you from Paris ! its ok now, after a long time to search ….

 Reply

kreso

October 12, 2020 at 11:25 pm

Hi, thanks for your post. Can you tell me is there any diff between your method to fix indexes and

`define( “WP_ALLOW_REPAIR”, true ); // in wp-config.php

and repairing it with native WP repair?

 Reply

    managedwphosting.nl

    October 13, 2020 at 6:16 am

    Hi @kreso,

    using WP_ALLOW_REPAIR is not the same.

    Using WP_ALLOW_REPAIR will (database-whise) only do a mysql ” REPAIR TABLE $table ” , to fix the corrupt files of the database table itself.

    Using REPAIR TABLE or even OPTIMIZE TABLE has no knowledge of missing keys nor duplicate zero (0) keys that prevent adding new primary keys or uniques. It will only try to fix
    broken files that actually hold your table data on the server disk.

    Reference : https://core.svn.wordpress.org/tags/5.5.1/wp-admin/maint/repair.php
    Look for these 2 lines and read beyond:
    // Loop over the tables, checking and repairing as needed.
    foreach ( $tables as $table ) {

    If you compare it with the free script: https://wpindexfixer.tools.managedwphosting.nl/wpindexfixer/ you will note a hughe difference.

     Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.