WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 4 years ago

#34959 closed defect (bug) (fixed)

dbDelta index ASC or DESC creates duplicate

Reported by: mnelson4 Owned by: ocean90
Milestone: 4.6 Priority: normal
Severity: normal Version: 4.4
Component: Database Keywords:
Focuses: Cc:
PR Number:

Description

Similar to #10404,
using
KEY movie_type_idx (movie_type ASC), creates duplicate indexes, whereas using
KEY movie_type_idx (movie_type), does not.
Although specifying the order of the index apparently doesn't affect anything according to http://dev.mysql.com/doc/refman/5.7/en/create-index.html. Specifically it states

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

Change History (2)

#1 @ocean90
4 years ago

  • Milestone changed from Awaiting Review to 4.6

#2 @ocean90
4 years ago

  • Owner set to ocean90
  • Resolution set to fixed
  • Status changed from new to closed

In 37583:

Database: Normalize index definitions in dbDelta().

dbDelta() compares the index definitions against the result of SHOW INDEX FROM $table_name. This requires a specific format so indices are not unnecessarily re-created. This format wasn't ensured, until now.

  • Parse the raw index definition to extract the type, name and columns so a normalized definition can be built (#20263, #34873).
  • Standardize on uppercase types (#34871) and on 'KEY'. 'INDEX' is only a synonym for 'KEY'.
  • Escape index names with backticks (#20263).
  • Normalize columns: Ignore ASC and DESC definitions (#34959), remove whitespaces (#34869) and escape column names with backticks (#20263).
  • Add backticks to all index change queries (#20263).

Props ocean90, pento, kurtpayne.
Fixes #20263, #34869, #34871, #34873, #34959.

Note: See TracTickets for help on using tickets.