MySQL and index creation problem.

[restless.being@…]

My migration is defined like:

class Migration:
    
    def forwards(self):
        # Mock Models
        Site = db.mock_model(model_name='Site', db_table='django_site', db_tablespace='', pk_field_name='id', pk_field_type=models.AutoField)
        
        # Model 'Post'
        db.add_column('blog_post', 'site', models.ForeignKey(Site, related_name='posts'))
        db.create_index('blog_post', ['site_id','slug','date'], unique=True, db_tablespace='')

It works well with PostgreSQL but when I call it on MySQL it doesn't work:

./manage.py migrate blog 0002
 - Soft matched migration 0002 to 0002_fk_post_to_site.
Running migrations for blog:
 - Migrating forwards to 0002_fk_post_to_site.
 > blog: 0002_fk_post_to_site
   = ALTER TABLE `blog_post` ADD COLUMN `site_id` integer NOT NULL; []
   = CREATE UNIQUE INDEX blog_post_site_id_183e2048 ON blog_post (`site_id`,`slug`,`date`); []
Traceback (most recent call last):
  File "./manage.py", line 11, in ?
    execute_manager(settings)
  File "/home/user/virtualenvs/anenv/lib/python2.4/site-packages/django/core/management/__init__.py", line 340, in execute_manager
    utility.execute()
  File "/home/user/virtualenvs/anenv/lib/python2.4/site-packages/django/core/management/__init__.py", line 295, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/user/virtualenvs/anenv/lib/python2.4/site-packages/django/core/management/base.py", line 195, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/home/user/virtualenvs/anenv/lib/python2.4/site-packages/django/core/management/base.py", line 222, in execute
    output = self.handle(*args, **options)
  File "/home/user/repozytorium/hg/aproject/apps/south/management/commands/migrate.py", line 61, in handle
    load_inital_data = True,
  File "/home/user/repozytorium/hg/aproject/apps/south/migration.py", line 449, in migrate_app
    run_forwards(mapp, [mname], fake=fake, db_dry_run=db_dry_run, silent=silent)
  File "/home/user/repozytorium/hg/aproject/apps/south/migration.py", line 227, in run_forwards
    klass().forwards()
  File "/home/user/repozytorium/hg/aproject/apps/blog/migrations/0002_fk_post_to_site.py", line 13, in forwards
    db.create_index('blog_post', ['site_id','slug','date'], unique=True, db_tablespace='')
  File "/home/user/repozytorium/hg/aproject/apps/south/db/generic.py", line 300, in create_index
    self.execute(sql)
  File "/home/user/repozytorium/hg/aproject/apps/south/db/generic.py", line 33, in execute
    cursor.execute(sql, params)
  File "/home/user/virtualenvs/anenv/lib/python2.4/site-packages/django/db/backends/mysql/base.py", line 83, in execute
    return self.cursor.execute(query, args)
  File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1071, 'Specified key was too long; max key length is 765 bytes')

Foreign key to Site is created but new index isn't.

[andrew]

Unfortunately, this is a limitation of your MySQL; your installation seems to have the maximum key length set to (as it says) 765 bytes, and you're using three columns; if, for example, each of them is defined as being 256 bytes long, you'll end up making the index 3*256 = 768 bytes long, which is too long.

I think you can google for the error; I got ​http://bugs.mysql.com/bug.php?id=4541 as the first result, but I'm not sure how helpful that is. Whatever the source of it, it seems to happen to at least MediaWiki? as well, and it's not a South bug, so this ticket's closing.