dbdelta duplicates indices instead of overwriting them.

[majelbstoat]

When using dbdelta to upgrade a plugin's tables, any UNIQUE keys on that table are duplicated, because they are added without being removed first. This seems to occur when the table structure is changed in some way, even when the SQL is identical to that outputted by phpMyAdmin.

​http://comox.textdrive.com/pipermail/wp-hackers/2006-April/006046.html for the original discussion.

I also had a problem where dbdelta tries to duplicate multiple primary keys as well. I'll do some more testing on this and see if I can narrow down the cause.

[ryanscheuermann]

drop non primary key indices first

[ryanscheuermann]

Keep in mind, when using dbdelta with multiple column keys, there can be no spaces after the commas when separating the columns, like so:

  KEY type_status_date (post_type,post_status,post_date,ID)

[majelbstoat]

I can confirm that this fixes the problem for me on 2.0.4 - no duplicate UNIQUE indices were created once the patch was applied. Haven't tested with creating an INDEX, but the patch looks sound.

The suggestion to remove the spaces from the multiple primary key also worked.

[ryan]

Removing the spaces worked for me. I'll commit that.

[ryan]

(In [4076]) Fix duplicate index error. Props ryanscheuermann. fixes #2695

[majelbstoat]

This doesn't fix the problem when a plugin uses dbdelta() like ​Gengo does. The UNIQUE index on the code field will be duplicated.

ryanscheuermann's patch fixed _that_ problem for me.

[ryan]

Dropping and then re-adding the key can take a while on large DBs. Is there another approach?

[majelbstoat]

We can just test to see if it exists and if it does, not bother to add it. I'll take a look at doing that.

[majelbstoat]

In fact, it looks like this is a non-issue. After looking through the dbdelta code, I found that the function does check for existing indices but is just quite picky about what it matches against.

Basically, instead of using INDEX, you have to use KEY. You also have to give a name to any indexes you create. You also need to specify KEY when creating a UNIQUE constraint.

So, changing my definitions from:

UNIQUE (code)
INDEX language_idx (language_id)

to:

UNIQUE KEY code (code)
KEY language_idx (language_id)

fixed the problem for me. Closing as invalid - plugin authors (myself included) should just be very precise in their table definitions.