MySQL Bugs: #6604: mysql Error: 1071 (Specified key was too long; max key length is 1000 bytes)

Bug #6604	mysql Error: 1071 (Specified key was too long; max key length is 1000 bytes)
Submitted:	13 Nov 2004 12:28	Modified:	14 Nov 2004 18:54
Reporter:	Bent Vangli	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.1.7	OS:	Linux (Linux (Fedora Core 2))
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
When I try to execute the following SQL statement:

CREATE TABLE phpgw_lang (
lang varchar(5) NOT NULL DEFAULT '',
app_name varchar(100) NOT NULL DEFAULT 'common',
message_id varchar(255) NOT NULL DEFAULT '',
content text,
PRIMARY KEY(lang,app_name,message_id)
);

I got MySQL Error: 1071, Specified key was too long; max key length is 1000 bytes

When I shorten message_id to varchar(228) it works. varchar(229) doesn't. I am using UTF8 as default charset on my MySQL engine. I cannot see anything wrong with above statement. However, I probarly got this error because multibyte preferable UTF8 character set uses much more bytes than the number of characters.

Looking at the numbers, you find (5 + 100 + 228) * 3 = 999, which is less than 1000. and contrary (5 + 100 + 229) * 3 = 1002. I looks like MySQL are using a factor of 3, maybe hardcoded? But in normal western languages this is normally much less than 3 times, maybe more like 1.2 - 1.5.

I Had put this on Serious Severity because it disables the possibility to install eGroupWare from http://www.egroupware.org/ using MySQL 4.1.x.

With very best regards

Bent Vangli

PS! I do compile and install from source. If guided to sourcefiles involved, I may do some testing.

How to repeat:
Run above statement on a MySQL 4.1.7 with UTF8 as default charset on a Fedora Core 2 (Linux kernel 2.6). Proberly also on other OS using UTF8.

Suggested fix:
Suggestion 1: Add a runtime or compiler variable/setting allowing to increase this 1000 bytes limit,
or suggestion 2: Count characters only, and eventually discards bytes after 1000,
or suggestion 3: Add a runtime or compiler option allowing for adjusting the expected characters/bytes factor for UTF8 multibyte.

Hi,

Thanks for your bug report!

This is not a bug, it is a stated limitation.  From the manual page:
http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html

"Prefixes can be up to 255 bytes long (or 1000 bytes for MyISAM and InnoDB tables as of MySQL 
4.1.2). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE INDEX 
statements is interpreted as number of characters. Take this into account when specifying a 
prefix length for a column that uses a multi-byte character set."

You are correct, it is the utf8 that is causing the extra bytes.  Try creating the table this way:

CREATE TABLE phpgw_lang (
lang varchar(5) NOT NULL DEFAULT '',
app_name varchar(100) NOT NULL DEFAULT 'common',
message_id varchar(255) NOT NULL DEFAULT '',
content text,
PRIMARY KEY(lang,app_name(75),message_id(100))
);

This will index the first 75 and 100 chars from the columns which should work
and be faster in general as well.

Best Regards

Hi Mattthew

Thanks a lot. When I see your answer, it is so obvious that it's a shame I didn't see it my self. :-}.

It is then very easy to adjust the length of the prefix field taking into account the length of UTF8 characters.

MySQL rocks :-))

Best regards

Bent Vangli
Oslo, Norway

Hi,

I think quotes around the PRIMARY KEY are needed if we specify a number of characters.
Something like
CREATE TABLE phpgw_lang (
lang varchar(5) NOT NULL DEFAULT '',
app_name varchar(100) NOT NULL DEFAULT 'common',
message_id varchar(255) NOT NULL DEFAULT '',
content text,
PRIMARY KEY(lang,`app_name`(75),`message_id`(100))
);