WordPress.org

Make WordPress Core

Opened 5 years ago

Last modified 7 months ago

#30795 reopened defect (bug)

Upgrade bug affecting indexing

Reported by: MattyRob Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 2.1
Component: Database Keywords:
Focuses: Cc:
PR Number:

Description

Every time WordPress is updated I was getting an entry in my error_log as follows:

WordPress database error Duplicate key name 'type_status_date' for query ALTER TABLE wp_posts ADD KEY type_status_date (post_type,post_status,post_date,ID) made by wp_upgrade, make_db_current_silent, dbDelta

After some heavy back tracking through the code, this is coming from a mismatch between the reported indexing of my database and what WordPress is expecting. in particular to the 'post_status' part. It seems WordPress expects the 'SubPart to be NULL, only in my main site is was '1'.

So, my index was reporting as:
KEY type_status_date (post_type,post_status(1),post_date,ID)
Rather than:
KEY type_status_date (post_type,post_status,post_date,ID)

This means the upgrade code is called on every update and fails on every update also.

I've managed to force through a fixed on my own site by dropping the index and recreating it using SQL in PHPMyAdmin.

Googling for the above error message it would seem I'm not the only one affected.

Change History (7)

#1 @nacin
5 years ago

  • Component changed from Upgrade/Install to Database
  • Focuses performance removed
  • Keywords reporter-feedback added
  • Version changed from 4.1 to 2.1

Very odd. :)

This index has been there like that since [4076] (8 years ago) and dates back to [3678], [3690].

What MySQL version? Any other environment information? How old is the install? Can you reproduce this in any way?

#2 @MattyRob
5 years ago

Current MySQL is 5.5.40, PHP 5.4.30 running on Apache 2.2.27.

WordPress install is originally from June 2005 - so back when WordPress was 1.5.x. Regular updates ever since.

I wouldn't have a clue where to start in reproducing this without having to recreate 10 years of blogging and upgrading. Certainly the issue has not resurfaced in the last few days.

Interestingly, there were other indexes on some tables that were not part of the current schema, I dropped those at the time without taking note of what they were.

As I said in the original report though, I'm clearly not alone in having this as others sites that have allowed creation and crawling of the error_log files are showing the same error message.

#3 follow-up: @chriscct7
4 years ago

  • Keywords needs-testing added

Could not replicate this. Installed copy of WordPress 2.0 and then manually updated through all major versions to 4.4.1. Also didn't see any extra indexes (though there were a few extra rows like post_category in the posts table, but that looks expected).

#4 in reply to: ↑ 3 @MattyRob
4 years ago

  • Keywords dev-feedback close added; reporter-feedback needs-testing removed

Replying to chriscct7:

Could not replicate this. Installed copy of WordPress 2.0 and then manually updated through all major versions to 4.4.1. Also didn't see any extra indexes (though there were a few extra rows like post_category in the posts table, but that looks expected).

Thanks for having a try at recreating. Unfortunately I would presume that the PHP and MySQL environments were also change during the 10+ years I've been using WordPress so while your attempt is welcome it almost certainly isn't a duplicate of my sites history.

I haven't seen any further issues but there are at least 2 other individuals with the issue who have posted to the WordPress forums:
https://wordpress.org/support/topic/stuck-upgrading-database-after-37-updated
https://wordpress.org/support/topic/could-someone-help-me-interpret-this-error-message

I'm not sure we'll even find the cause for this and it dose seem to be a rare issue considering the number of sites using WordPress. Perhaps this ticket should sit for a while and be considered for closure.

#5 @pento
3 years ago

  • Keywords dev-feedback close removed
  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Duplicate of #31869.

This bug would've been fixed by [32108].

#6 @hubertnguyen
8 months ago

  • Resolution duplicate deleted
  • Status changed from closed to reopened

I just got this error on my development system after upgrading to WP 5.1 (from 5.0). I googled, and found this page.

WordPress database error: [Duplicate key name 'type_status_date']
ALTER TABLE wp_posts ADD KEY type_status_date (post_type,post_status,post_date,ID)

PHP Version 7.2.5-1+ubuntu16.04.1+deb.sury.org+1
mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $

Everything seems to work, but it's an odd message to see. I thought I'd share.

#7 @SergeyBiryukov
7 months ago

  • Milestone set to Awaiting Review
Note: See TracTickets for help on using tickets.