[wp-trac] [WordPress Trac] #10404: dbDelta creates duplicate indexes when index definition contains spaces

[WordPress Trac]

#10404: dbDelta creates duplicate indexes when index definition contains spaces
-------------------------------+-----------------------------
 Reporter:  Denis-de-Bernardy  |       Owner:
     Type:  enhancement        |      Status:  new
 Priority:  normal             |   Milestone:  Future Release
Component:  Database           |     Version:  2.8.1
 Severity:  normal             |  Resolution:
 Keywords:  has-patch          |
-------------------------------+-----------------------------

Comment (by charlestonsw):

 When using dbDelta you MUST use an uppercase KEY qualifier on indices.
 If you use lowercase the query string comparison will not match the
 database meta data which comes back with an uppercase "KEY".   The loop
 that compares the index portion of the SQL statement is case sensitive.

 The patch file above: 10404.patch_case_insensitive_key_matching has phpDoc
 updates, some comments, and most importantly turns the index comparison
 into a case insensitive operation.

 The next issue I am investigating is the way the query string is built.
 In my specific instance I am using this to build a key:


 {{{
 create table x (
 id mediumint(8) not null autoincrement,
 KEY (id)
 )
 }}}


 That will never match the index comparison check as the database meta data
 return info that dbDelta builds into the following string:


 {{{
 KEY id (id)
 }}}


 Even with the string insensitive search the resulting KEY (ID) does not
 match KEY ID (ID).

 I can fix this by updating my create table command to match the meta data
 exactly by replaceing KEY (id) in my data definition with KEY id (id), but
 IMO dbDelta should be a little more forgiving.   Otherwise there are a lot
 of plugins creating extra indices and thus server load on millions of
 WordPress installations out there.  The "Too many keys specified; max 64
 keys allowed" message is far too prevalent.

--
Ticket URL: <http://core.trac.wordpress.org/ticket/10404#comment:10>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software