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.
Read me 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 your 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:
- 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. (See Dutch screenshot “Inzenden voor beoordeling”)
No permalink, just “?preview=true”
36 replies on “WPDB WP Core Database SQL index, primary keys fix script”
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
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
Thanks a lot. it is works . save my time
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 !
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_
Merci, Thank you from Paris ! its ok now, after a long time to search ….
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 );
and repairing it with native WP repair?
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:
foreach ( $tables as $table ) {
If you compare it with the free script: https://wpindexfixer.tools.managedwphosting.nl/wpindexfixer/ you will note a hughe difference.
Hi all,
First thank you for the tips.
Can any one tell how to add them to my website ? i have the same problem but dont know how to resolve it (where to put the queries) please
@Mood Contact your host where to add the queries. Most of the time it will be your WP database wich will be accessible via phpmyadmin.
Thank you for your reply.
I have access to the Myphpadmin platform could you tell me at which level I should make the modification.
My host is very slow and unhelpful.
in your database in the SQL tab. perhaps it’s time to move hosts?
you saved me, thank you a lot.
Thank you so much!
In case it’s useful to anyone .. phpMyAdmin has a config setting to ignore errors. Useful if some of the tables already have some indexes (I had put a few in manually).
Great!
Do share Steve, so I can adjust the queries 🙂
I get query errors running the script in PhpMyAdmin; #1067 – Foutieve standaard waarde voor ‘user_registered’.
Is there a fix for this? I would like to use this solution to fix our WP.
Hello/hallo Niels,
this is a database date field, and has nothing to do with the indexes. However, you need to focus on the default values AND the values allready in the database for all dates in the user table, and possibly also in the posts table (4 times).
After you fix that, you will be able to run the index fixer script.
Hello, thanks for the quick reply. Is there a tutorial available for the action you mentioned? I changed some values and got different errors. Sorry, i have no idea what settings to change to what value.
If you manage to set it to 1970-01-01 00:00 or something the like I would guess you will be fine
When we try to activate the plugin, WordPress returns a message saying that it can’t be activated because it caused a fatal error – but there’s nothing in error logs. Any idea?
Did you mean the paid version? You must read the instructions on the site carefully. It is not intended for normal plugin usage, it is a mu-plugin. Instructions are on the site, consult them with your webmaster.
This Hero deserve an Award.
THANK YOU SO MUCH BROTHER.
Sorry to come back to this.
It all worked and soleved my issue (blank page when trying to add post/page). However my site is very slow (take a lot of time) when adding a new post, image, or saving settings.
What could be the possible cause of it, and how to go about it???
Slow WP can have many many sources of origin .. You might ask your webhost if the server is having a high serverload for instance.
That’s an interesting article and work.. I have about 350MB of a WordPress DB, consists of 1.6m rows. More than 30k posts.. A big blog.
And although everything works ok, I’d still love to have my WordPress/SQL improved in terms of performance. So, would your re-indexing suggestion adds an extra performance to overall WordPress?
Thank you!
No, this fixer will only repair/add broken Core indexes. Add the ones that should be present in WP Core and perform some other fixes. Every site needs some basic indexes for it to work at all.
Your issue is a *performance* issue. You are better off reducing the amount of plugins or changing theme.
You might even look a different hosting. If you like we can make a demo/test copy of your site at our servers, ask for a free demo at https://www.managedwphosting.nl/jouw-wordpress-website-kan-sneller/
wow, well i am very suprised.
This is like witch craft.
1. i couldnt edit my wp options table in phpmyadmin
2. uploading error.
This sql code fixed all of them.
Am very happy , i donnt have much, i would hv sent u money. but i say i should show u appreciation by thanking you.
Thanks alot,,, i hv made research until i stumbled on ur comment in a website.
Hi, thanks for the script. My SQL knowledge is very limited and I run into errors like this one after I add the script to the sql tab in phpmyadmin and click on ‘start’:
Fout
SQL-query: Kopiëren
ALTER TABLE wp_links ADD PRIMARY KEY (link_id)
MySQL meldt: Documentatie
#1068 – Meerdere primaire zoeksleutels gedefinieerd
What does this mean and what should I do?
It does mean that the wp_links table is already having a primary key, you can safely ignore this.
If you want us to host your site and check all this, contact us via https://www.managedwphosting.nl/contact/
I did not understand the instructions well enough to know where in myphpadmin i am supposed to place this code.
ask your webhost.
if they are not willing: open phpmyadmin , click on your WordPress database name, and then on the SQL tab on top
and just paste the whole thing in?
I get this:
what am i doing wrong, forgive my lack of coding php or sql
9 errors were found during analysis.
Variable name was expected. (near “?” at position 1)
Unexpected character. (near “{” at position 493)
Unexpected character. (near “{” at position 575)
Unexpected beginning of statement. (near “?” at position 1)
Unexpected beginning of statement. (near “php” at position 2)
Unexpected beginning of statement. (near “Ensure” at position 303)
Unexpected beginning of statement. (near “that” at position 310)
Unexpected beginning of statement. (near “a” at position 315)
Unrecognized statement type. (near “specific” at position 317)
SQL query:
<?php add_filter( 'http_request_args', function ( $response, $url ) { if ( 0 === strpos( $url, 'https://api.wordpress.org/plugins/update-check' ) ) { $basename = plugin_basename( __FILE__ )
MySQL said: Documentation
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?php
add_filter( 'http_request_args', function ( $response, $url ) { if ( 0 === strpos( $url, 'https://api.wordpress.org/plugins/update-check' ) ) { $basename = plugin_basename( __FILE__ )
MySQL said: Documentation
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?php
/*
Plugin Name: WPDB WP Core Database SQL index, primary keys fix script' at line 1
I absolutely thank you for this! I fixed all my tables.
@Chris , you CANNOT paste pure PHP sourcecode in your database admin.
You must run it as mu-plugin ( wp-content/mu-plugins/here.php ) and copy paste the actual output in your database admin