WordPress upgrade failure? Check your AUTO_INCREMENT settings

Recently one of the websites I maintain, which runs WordPress, had not been updated in a while. When I checked, it said the usual:

Update WordPress
Another update is currently in progress.

As you can imagine, there wasn’t another update in progress. Anyone who uses a search engine lately will see hundreds, if not thousands, of AI slop sites recommend installing a plugin that isn’t even compatible with WordPress to remove core_updater.lock, which wasn’t even present in the options table.

Turns out, whomever restored this database forgot to re-enable the AUTO_INCREMENT setting on important WordPress tables, including the options table.

(It also turns out that due to a magnificent example of assumptions, all rows that WordPress inserts into the options table magically get an ID of zero, overwriting whatever else was there. 10/10, folks. Really good design.)

Anyway, the fix was to re-enable AUTO_INCREMENT on the important tables. This query might help you.

    SELECT c.TABLE_NAME, c.COLUMN_NAME, c.COLUMN_TYPE, c.IS_NULLABLE
    FROM information_schema.COLUMNS c
    WHERE c.TABLE_SCHEMA = DATABASE()
      AND c.TABLE_NAME LIKE %s
      AND c.COLUMN_KEY = 'PRI'
      AND c.DATA_TYPE IN ('int','bigint','mediumint','smallint','tinyint')
      AND c.EXTRA NOT LIKE '%%auto_increment%%'
      AND (
        SELECT COUNT(*) FROM information_schema.COLUMNS c2
        WHERE c2.TABLE_SCHEMA = c.TABLE_SCHEMA
          AND c2.TABLE_NAME   = c.TABLE_NAME
          AND c2.COLUMN_KEY   = 'PRI'
      ) = 1