dbDelta Index Definition Spaces Duplicate Indexes

[charlestonsw]

Reference ticket #10404.

This is to decompose the original ticket into components. May be fixed in 4.4. Needs testing.

---

If the index definition contains spaces dbDelta will create duplicate indexes each time it is run.

No duplicates:

CREATE TABLE $wpdb->term_relationships (
 object_id bigint(20) unsigned NOT NULL default 0,
 term_taxonomy_id bigint(20) unsigned NOT NULL default 0,
 term_order int(11) NOT NULL default 0,
 PRIMARY KEY  (object_id,term_taxonomy_id),
 UNIQUE KEY reverse_pkey (term_taxonomy_id,object_id),
 KEY term_taxonomy_id (term_taxonomy_id)
) $charset_collate;");

Duplicates:

CREATE TABLE $wpdb->term_relationships (
 object_id bigint(20) unsigned NOT NULL default 0,
 term_taxonomy_id bigint(20) unsigned NOT NULL default 0,
 term_order int(11) NOT NULL default 0,
 PRIMARY KEY  (object_id,term_taxonomy_id),
 UNIQUE KEY reverse_pkey (term_taxonomy_id, object_id),
 KEY term_taxonomy_id (term_taxonomy_id)
) $charset_collate;");

Line of interest, note the space after the comma:

 UNIQUE KEY reverse_pkey (term_taxonomy_id, object_id),

Reported by: Denis-de-Bernardy

[ocean90]

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.