MySQL + InnoDB + UTF-8

home 

 

Once upon a time I stumbled on a very difficult to find "issue" with MySQL using InnoDB : An index size cannot be more than 767 bytes long.

This particular limit is quite easy to understand : the developers have only allocated so much space for the index. But here is where I got caught : when you tell MySQL the size of your TEXT column indexes, you give it a number of characters. I had given MySQL an index size of 512 for a TEXT column I intended to search often.

Unfortunately, InnoDB seems to reserve 3 bytes for every UTF-8 character. So my 512 character index used 1536 bytes and blew up InnoDB's prefix size limit. I would have found out my mistake faster if I had been given an error code different than :

ERROR 2013 (HY000): Lost connection to MySQL server during query

To add insult to injury, this error only occured when I made queries on the problematic table. I could continue to use the other tables without any problem.

To fix my problem, I simply removed the oversized index from my table. It would have been nice to get an error message at the time of the table creation.