Suppose I try to create a table with a primary key that’s varchar(500), and MySQL complains the key length is longer than the maximum of 1000 bytes. 500 is less than 1000. What’s happening? In this article I’ll explain why I, not MySQL, am wrong. Plus, I’ll show you a tasty (yet apparently harmless) bug in MySQL.
Here’s a statement that will fail on my server:
create table test(c varchar(250), d varchar(250), primary key(c,d)); ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
Why does it fail? Simple; my default character set is multi-byte:
show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+
While most characters will fit in one or two bytes, the utf8 encoding of Unicode, as implemented by MySQL can require up to 3 bytes per character, so MySQL must be pessimistic and assume the worst-case scenario of every character requiring 3 bytes. It’s easy to see this by trying to create a table with a single VARCHAR(334) primary key. It will fail, but VARCHAR(333) will succeed, because 3 * 333 is less than 1000 bytes.
Here’s a fun bug (bug #18927):
mysql> create table test(c varchar(250), d varchar(250),primary key(c,d)); ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes mysql> create table test(c varchar(334), d varchar(334), primary key(c,d)); ERROR 1071 (42000): Specified key was too long; max key length is 999 bytes
Sometimes it says 999, sometimes 1000. I have no idea why.
It may be a good idea to check the default character set to get the best performance out of a database. If there’s no need for multi-byte encodings, switching to a single-byte encoding might be significantly more efficient. It’s especially important to keep indexes as small as possible.
Technorati Tags:No Tags
MySQL has always been weird IMHO. However the problem gets more interesting if you convert your happily running DB into multi-byte from latin1. All of a sudden quite a number of indexes stop working.
However having keys of that size is quite weird in the first place. Maybe a key for a MD5 of 32 characters would be ok, still huge by itself. When I see a DB with such enormous key sizes, then I am sure something is badly designed anyways.
Just MHO,
Ries van twisk
Of course, huge keys like that are a terrible idea. I don’t remember now why this was happening. I think I had to convert some data that had been automatically imported as large character columns, even though most of them didn’t have much data.
What happens to the indexes when you convert to multi-byte? If you drop and recreate the indexes, will they work right again, or is there a bug with multi-byte indexes?
I got this error when I install Mambo with utf-8 as default in PHPMyAdmin. Does that mean that I can’t install Mambo when default is utf-8?
Nazar,
It looks like this statement in
installation/sql/mambo.sqlis causing the problem:That is definitely going to cause a problem, and my guess is the Mambo developers are unaware of it. They may also not know that they are duplicating indexes — and MySQL is not “smart enough to combine duplicate indexes.” Duplicate indexes really do get duplicated in every way, from CPU time to disk space.
You should be able to edit this statement and make the charset latin1 like this:
Well, I know this solution :) I can set also cp1251 encoding, etc.
My concern is that I’m going to create a 3-language website, that will use English, Russian, and Ukrainian languages. That’s why I need utf8 here…
I guess this is impossible for now. Am I right?
I don’t know any other workaround unless you can make the columns smaller, or remove the index completely. I have no idea whether Mambo really needs those columns to be 240 characters long, or whether they really need to be indexed. If the unique index is only there to ensure no duplicate columns exist, maybe that can be coded in the application logic instead. It is usually a very bad idea to make indexes on such long columns.
The problem appears to be the combined column’s length. For example, if the primary key is specified with more than 1 columns, then the total length is tested for this limit, and it’ll fail if it’s over 999.