Make WordPress Core

Opened 23 months ago

Last modified 15 months ago

#56259 new enhancement

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

Reported by: harryfear's profile harryfear Owned by:
Milestone: Awaiting Review Priority: normal
Severity: minor Version:
Component: Database Keywords:
Focuses: Cc:

Description

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:

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.

Change History (2)

#1 @ramon fincken
15 months ago

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 ?

#2 @ramon fincken
15 months ago

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.

Note: See TracTickets for help on using tickets.