This question already has an answer here:

Our server is creating a lot of tables and one of them is the Lock_ table.

We are using MySQL 5.5 on Percona. Also we are using utf8_general_ci and utf8.

It throws the following error when creating the following index on Lock_ table:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Here is the create-table query:

create table Lock_ (
    uuid_ VARCHAR(75) null,
    lockId LONG not null primary key,
    companyId LONG,
    userId LONG,
    userName VARCHAR(75) null,
    createDate DATE null,
    className VARCHAR(75) null,
    key_ VARCHAR(200) null,
    owner VARCHAR(255) null,
    inheritable BOOLEAN,
    expirationDate DATE null
);

Index query:

create unique index IX_DD635956 on Lock_ (className, key_, owner);

This problem is happening on one of our prod servers (previously it was standalone mysql and then later moved to percona, but the problem was present in both the editions), but this is working fine on our dev environment.

So is there a way we can update the prod environment's index length or something so that it does not throw this error?

Thanks

marked as duplicate by ypercubeᵀᴹ, Max Vernon, Paul White, RLF, RolandoMySQLDBA mysql Sep 15 '14 at 19:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Are you sure this create unique index IX_DD635956 ... statement throws the error and not something else? – ypercubeᵀᴹ Sep 15 '14 at 13:02
  • What is the charset of the table and of the columns? (add the SHOW CREATE TABLE Lock_ ; output in the question.) Also add the specific version (mysql or percona) that gives you this error. – ypercubeᵀᴹ Sep 15 '14 at 13:12
  • I cannot reproduce this error in the latest versions (neither 5.1 nor 5.5 nor 5.6) – ypercubeᵀᴹ Sep 15 '14 at 13:35
  • @ypercube ALTER TABLE ENGINE=MyISAM and there you have the error. Oh, I've seen the error message, I am wrong. – jynus Sep 15 '14 at 14:43
  • The only way I get this error - with this code - is if the charset is 'utf8mb4'. I don't have Percona to test right now but they are either not showing the code that produces the error or something is fishy is going on. – ypercubeᵀᴹ Sep 15 '14 at 14:53

I suspect the difference is a Unicode length issue. As you are not using a single-byte-per-character collation the total length of the fields in your key is 530 character (75+200+255) and not 530 bytes, and 530 characters could easily be more than 767 bytes depending on what language you are storing in those strings (in fact definitely will be if mysql stores unicode data always as two-bytes-per-character as MS SQL Server does in its NVARCHAR column type).

As to why it is working in Dev but not live:

  • If mysql doesn't use a fixed length encoding then it could be that existing data in some rows in production is long enough to break the 767 byte limit, but in Dev there are no such rows. The length() function (http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_length) returns the number of bytes, not characters, so you can scan for such rows by selecting where LENGTH(className)+LENGTH(key)+LENGTH(owner) > 767.
  • Otherwise it could be that your collation settings are not identical between dev and production, try verifying those.
  • 1
    MySQL does not use fixed length encoding. – ypercubeᵀᴹ Sep 15 '14 at 13:06

Your total desired index size for (className, key_, owner) is:

(75*3 + 1 + 1) + (200*3 + 2 + 1) + (255*3 + 2 + 1) = 1598 bytes 

InnoDB allows indexes up to 3500 bytes if each column only indexes up to 767 bytes. MyISAM only allows 1000 characters in total (changing the block size). Probably your production database is using MyISAM instead of InnoDB. Maybe you are using utf8mb4 in production? That would get you that error.

Also, have a look at why varchar(255) is a bad idea.

Not the answer you're looking for? Browse other questions tagged or ask your own question.