[wp-trac] [WordPress Trac] #56259: Database Repair doesn't check for missing primary keys / A.I.

[WordPress Trac]

#56259: Database Repair doesn't check for missing primary keys / A.I.
-------------------------+-----------------------------
 Reporter:  harryfear    |      Owner:  (none)
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Database     |    Version:
 Severity:  minor        |   Keywords:
  Focuses:               |
-------------------------+-----------------------------
 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.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/56259>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform