Database errors during WP upgrade

  • Resolved Grzegorz.Janoszka

    (@grzegorzjanoszka)


    3 years, 2 months ago

    I have just upgraded my WP from 5.8.X to 5.9. In the error log there are sh*tload of errors. Apparently the WP upgrade script runs some DB commands like:

    ALTER TABLE wp_usermeta ADD PRIMARY KEY (umeta_id)
    ALTER TABLE wp_termmeta ADD PRIMARY KEY (meta_id)
    ALTER TABLE wp_termmeta ADD KEY meta_key
    ALTER TABLE wp_commentmeta ADD PRIMARY KEY (meta_id)
    ALTER TABLE wp_commentmeta ADD KEY comment_id
    ALTER TABLE wp_comments ADD PRIMARY KEY (comment_ID)
    ALTER TABLE wp_comments ADD KEY comment_approved_date_gmt

    and so on and so on. They all fail with the error message:
    Duplicate key name.

    I thought this plugin was tested, but it seems basic things fail ??

Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Author OllieJones

    (@olliejones)

    Thanks for reporting this defect!

    It looks like the 5.8.3 -> 5.9 upgrade process tries to restore its own standard keys if they aren’t present.

    I will investigate. (This plugin is turning out to be a very ambitious undertaking for a solo developer.)

    Could you please upload your site’s metadata, if possible after the upgrade. Visit the About tab and upload it from there.

    Plugin Author OllieJones

    (@olliejones)

    The upgrade process from WordPress 5.8.3 to 5.9 works OK, even though it throws some error-log messages. It attempts to create standard indexes. When it fails it throws error log messages.

    Because most of this plugin’s indexes use the same name as the standard indexes, the high-performance indexes remain in place. But, the standard indexes dropped by this plugin (because their columns are incorporated in to the tables’ primary keys) get recreated. So there are some redundant indexes on the tables after the version upgrade.

    Fix (after upgrading to 5.9):
    With wp-cli give the command wp index-mysql enable --all.
    Or, visit the Tools / Index MySQL dashboard page and use the Convert Keys Now.

    Details:

    For example, in wp_comments this plugin uses option_name for the primary key and so drops the UNIQUE key called option_name. The 5.9 update process adds it back, but leaves the high-performance keys unaltered.

    I’m investigating a fix to this problem.

    Plugin Author OllieJones

    (@olliejones)

    It doesn’t seem possible to filter the SQL DDL statements issued by the core update process.

    https://wordpress.stackexchange.com/questions/401827/version-upgrade-can-my-plugin-filter-the-sql-statements-issued-during-a-databas

    So, now the plugin prompts with a nag message when it detects a recent core version update. The operation of restoring high-performance keys after the version update is as simple as dropping redundant indexes from four tables and takes very little time.

    Plugin Author OllieJones

    (@olliejones)

    The latest version of the plugin, 1.4.4, should deal correctly with the 5.9 upgrade, and the 6.0 upgrade.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Database errors during WP upgrade’ is closed to new replies.