Database Repair doesn't check for missing primary keys / A.I.

[harryfear]

Hi amazing WP community,

This is the second time I've come across this issue in the wild.

The built-in DB repair functionality (repair.php) doesn't attempt to verify or repair missing primary key / autoincrement attributes of the core wp_posts table (and possibly other important core tables). This affects fringe/edge cases where this core table has been corrupted due to third-party plugins or other user misfortune.

We just need add some SQL to check for this health database attribute, such as:

SELECT COLUMN_NAME 
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'wp_posts' 
  AND CONSTRAINT_NAME = 'PRIMARY';

SELECT COLUMN_NAME 
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'wp_options' 
  AND CONSTRAINT_NAME = 'PRIMARY';

(Adapted from source: ​https://stackoverflow.com/a/52288527/8800423)

These should each return 1 result each.

As part of 'repair', we could have auto-fix SQL to re-enable the auto-increment / primary key. There is obviously sometimes a 'market' for that fix:

​https://webshop.mijnpress.nl/shop/plugins/wpdb-wp-core-database-sql-index-primary-keys-fix-script/ (commercial; no association)

There are quite a few edge/fringe cases recorded that this could help:

• ​https://wordpress.stackexchange.com/questions/353713/wordpress-database-problem-wp-posts-primary-key
• ​https://wordpress.stackexchange.com/questions/237076/resetting-auto-increment-primary-key
• ​https://www.alexgeorgiou.gr/repair-auto_increment-primary-key-wordpress-mysql/
• ​https://wordpress.stackexchange.com/questions/329162/is-it-possible-to-fix-repair-all-index-primary-key-unique-and-all-other-stru
• ​https://wordpress.stackexchange.com/questions/225641/wordpress-database-lost-auto-increment

Additionally, in a sense this is a core functionality break if these database attributes are not present because posts will fail to create in the DB.

Perhaps the detection and fix shouldn't be on advanced user’s manual use of repair.php.

In which case, running a regular check with mosts WP requests would be non-performant. So perhaps this could be included as a CRON'd/infrequent health check within the WP Site Health scope?

N.B. The popular non-core "WP-DBManager" plugin does not (at latest version 2.80.8) check for (let alone repair) this issue either.

[ramon fincken]

I am the owner of the site you referred to @harryfear , we have a full working free script that will use the latest schema.php at ​https://wpindexfixer.tools.managedwphosting.nl/

See this explanation about repair.php versus actually re-adding keys: ​https://wpindexfixer.tools.managedwphosting.nl/#comment-11

The source of disappearing indexes in most of the times is database migrations gone wrong where the indexes are added in the very last lines of the SQL commands. When the migration encounters even one error , the whole execution stops. leaving you with perfectly valid data, but no indexes.

Does this help in answering your question ?

[ramon fincken]

note: the issue with persons posting working solutions on stackoverflow .. is that the minute WP Core has either new tables or new columns .. their solution is outdated.

Relying on schema.php is the best solution.