dbDelta doesn't allow for automatically truncated indexes

Description

When a table is created with too large an index, MySQL will automatically truncate it. For example:

CREATE TABLE foo (
a VARCHAR(255) CHARACTER SET utf8mb4,
INDEX (a)
);

MySQL will automatically truncate the index on a to be 191 characters.

When dbDelta() checks SHOW INDEX FROM foo, it'll see that the index is 191 characters, when this wasn't specified in the CREATE TABLE statement, so it'll try and recreate the index that already exists.

This bug has probably existed since forever, but will become more prominent now that utf8mb4 is a thing.

Change History (22)

When the character set is utf8mb4, 191 becomes a magic number, and is treated the same as an empty subpart. Magic numbers are kind of eww.

Remove the subparts from the both the original CREATE TABLE and the generated index definition - we're really only interested in the index existing, not the size of it. This may change in the future, though.

Remove the subparts from the both the original CREATE TABLE and the generated index definition - we're really only interested in the index existing, not the size of it. This may change in the future, though.

Yeah, I think what needs to happen is the check for whether an index exists needs to be split from the check of whether it is the same length. If the lengths are different, the index may need to be updated (unless the current length is the default for the character set being used, and no length is specified). I'm not an expert on how MySQL indexes work, however. :-)

31869.diff​ adds a second check for columns that have an index length in the DB, but not in the CREATE TABLE statement. It throws a _doing_it_wrong() warning for any that it finds, but doesn't try to add the existing index.

@DrewAPicture - ping for a new string added in this patch. This patch (or something like it) needs to happen in 4.2, but I'll leave it to your discretion as to how/if we handle a new string after string freeze.

@DrewAPicture - ping for a new string added in this patch. This patch (or something like it) needs to happen in 4.2, but I'll leave it to your discretion as to how/if we handle a new string after string freeze.

Let's hold on to the new string until a little bit closer to RC1. I'm sure we'll have other string changes, and I'd kind of like to bundle them together.

I'm happy with the state of the the string in 31869.3.diff​ - who knew that a one word change could make such a difference?

My concern with the functionality of the patch is that it really only handles one specific case - when the CREATE TABLE statement doesn't specify a subpart, but MySQL shows that there is a subpart (most likely because it had to trim an index that was too long).

Cases is doesn't handle:

When the CREATE TABLE has a subpart specified (say, you manually specified a subpart of 255, but MySQL trims it down because of utf8mb4) that differs from the subpart that MySQL shows.

When the CREATE TABLE has a subpart specified, but MySQL does not (I'm not sure how this would happen).

I've discussed this with @dd32, and given it some more thought. I think that our best route is remove the _doing_it_wrong() message, and just treat an undefined index subpart as being okay, if it turns out MySQL does have an index subpart.

I've changed the fix so that we can easily expand the set of "equivalent" index definitions, if more crop up.