Key longer than 255 chars in a UTF8 mysql database

Question

I need to store URLs longer than 255 characters in an existing Django/South/MySQL application. These URLs are used as foreign keys. The issue is that the collation is UTF8, so if I increase the max_length of the URLField to, say, 512, I am getting the following error:

'Specified key was too long; max key length is 767 bytes'

I guess the reason is that in UTF8, each character can potentially be 3 bytes long, so with even just 256 characters, I could get over the limit.

As the URL should only include ASCII characters, I tried to change the collation of the specific table to ASCII in a (data) migration, and it seemed to work well: after migrating, I can successfully change max_length to 512 in a subsequent schema migration and store longer URLs.

However when bootstrapping the database, I am having the issue that it tries to set up the database without going through migrations:

syncdb --all
migrate --fake

syncdb, of course fails because it again tries to create the foreign key which is too long (migration to adjust collation of the database is not run).

If I syncdb without --all, (to then run migrations without --fake), it fails saying that some of the authentication tables do not exist.

Any idea of the proper way to increase that max_length limit?

Answer 1

Best, and possibly only solution: Don't use urls as foreign keys at all.

Put the urls in a table with a surrogate key (auto increment integer, for instance). Use that key for foreign keys and just put a unique index on the url column to prevent duplicate urls.

Answer 2

A less invasive and less clean approach I took was to:

• Use indexes with prefix size. this can only be done using a raw SQL statement, in a migration.

• Since migrations are usually not run when setting up a new database, I made a management command which creates the indexes, this command must be run after syncdb and migrate:

  ./manage.py syncdb --all
  ./manage.py migrate --fake
  ./manage.py create_indexes
  

With myapp/management/commands/create_indexes.py being a management command that runs the raw SQL commands to create indexes with prefix.