id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc,focuses 56259,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.",enhancement,new,normal,Awaiting Review,Database,,minor,,,,