1

I had to run this command to alter a column in one of my table.

ALTER table XYZ modify value VARCHAR(1024);

When I run it, I get this error.

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

The original column size was 250. Here is the strange part. I have a 2nd MySQL server with identical configurations and it is doing 2 way replication with the 1st server. When I run this "alter table" command on my 2nd MySQL server, I didn't get this error. In fact, after the alter command finished running, this command was replicated to my primary mysql server without any error.

My two mysql servers are setup as active-active, but only one of them is being actively use. So to clarify, I got this "max key length" error on the actively used server. But it ran fine on the 2nd server that was not being use.

Does anyone have any idea why the command ran find on my 2nd server?

Thanks../Ken

user3780273
  • 31
  • 1
  • 3
  • You have 2 different version of mysql in servers and hence you are getting this http://stackoverflow.com/questions/13506832/what-is-the-mysql-varchar-max-size – Abhik Chakraborty Jun 26 '14 at 17:04

2 Answers2

3

You might need to adjust the index on this table.

DROP INDEX index_name ON tbl_name;
CREATE INDEX index_name ON tbl_name (col1,col2(100));

Where col2 is the value column from your example and 100 is the max characters to include in the index.

If you are unsure of the indexes on the table, try the following (replacing tbl_name with your table name).

SHOW INDEXES IN tbl_name;
HarmonicApps
  • 88
  • 7
0

I have also faced same issue when the encoding of the database is set to UTF8.

If you use latin1 then the largest column you can index is varchar(767), but if you use utf8 then the limit is varchar(255).

Try changing the value length to 255.

It might work.

Following link may guide you to the same.

http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

AKs
  • 1,727
  • 14
  • 18