Posted on by & filed under django, mysql, programming.

Yesterday I tried to deploy some code to a relatively new server. Unfortunately I was greeted with this:

What does that mean? First, we have to figure out what a key is. Stackoverflow is helpful for this sort of thing: http://stackoverflow.com/questions/924265/what-does-the-key-keyword-mean. So a key is an index. Great. We definitely have a lot of Foreign Keys and each table has a Primary Key, but we certainly don’t have 64 Foreign Keys + Primary Keys.

Now that we know that a key is really an index, how do we see what indices exist? Luckily the stack trace that produced the error above at least had a table it seemed to be having trouble with (table: penguin). Running this command shows us the indices:

Ah ha! There are the the 64 keys but why in the world are there 62 email indices? What could be adding an index to the email column during a deploy? Well, the traceback said that it failed during a run of Django’s syncdb, which creates new database tables to align with a model. A little code snooping (thank you git grep) yielded a piece of our code that hooked into the django post_syncdb signal. This was the offending code:

I learned that adding UNIQUE to an ALTER TABLE command will actually add a new index every time that command is run. Thus, we hit the MySQL max and could not add anymore and a deploy failed because of it.

Removing the UNIQUE index and ensuring it is run once on penguin table creation and not again later seemed the simplest solution to this problem.

Tags:

One Response to “Avoiding “Too many keys specified” in MySQL”