#2695 closed defect (bug) (invalid)
dbdelta duplicates indices instead of overwriting them.
Reported by: | majelbstoat | Owned by: | ryan |
---|---|---|---|
Milestone: | Priority: | normal | |
Severity: | normal | Version: | 2.1 |
Component: | Administration | Keywords: | dbdelta duplicate indices |
Focuses: | Cc: |
Description
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.
Attachments (1)
Change History (12)
#1
@
18 years ago
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)
#3
@
18 years ago
- Keywords bg|commit added
- Version changed from 2.0.2 to 2.0.4
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.
#7
@
18 years ago
- Resolution fixed deleted
- Status changed from closed to reopened
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.
#8
@
18 years ago
Dropping and then re-adding the key can take a while on large DBs. Is there another approach?
#9
@
18 years ago
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.
#10
@
18 years ago
- Keywords bg|commit removed
- Resolution set to invalid
- Status changed from reopened to closed
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.
drop non primary key indices first