Django MySQL error when creating tables

Question

I am building a django app with a MySQL DB. When I run 'python manage.py migrate' for the first time, some tables are created well then some errors appear. The error brought out is:

django.db.utils.IntegrityError: (1215, 'Cannot add foreign key constraint')

When I run this MySQL command -

SHOW ENGINE INNODB STATUS\G,

I get this >>>

2015-02-17 14:33:17 7f10891cf700 Error in foreign key constraint of table movie_store/#sql-4f1_66:
 FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`):
Cannot resolve table name close to:
 (`id`)

The complete traceback is:

Creating tables...
    Creating table users
    Creating table merchant
    Creating table celery_taskmeta
    Creating table celery_tasksetmeta
    Creating table djcelery_intervalschedule
    Creating table djcelery_crontabschedule
    Creating table djcelery_periodictasks
    Creating table djcelery_periodictask
    Creating table djcelery_workerstate
    Creating table djcelery_taskstate
    Creating table post_office_email
    Creating table post_office_log
    Creating table post_office_emailtemplate
    Creating table post_office_attachment
    Running deferred SQL...
Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 338, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 330, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 390, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 441, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/commands/migrate.py", line 173, in handle
    created_models = self.sync_apps(connection, executor.loader.unmigrated_apps)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/commands/migrate.py", line 309, in sync_apps
    cursor.execute(statement)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 63, in execute
    return self.cursor.execute(sql)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/mysql/base.py", line 124, in execute
    return self.cursor.execute(query, args)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
django.db.utils.IntegrityError: (1215, 'Cannot add foreign key constraint')

Answer 1

This will works

python manage.py migrate auth
python manage.py migrate

The issue because of other migration run before the auth, so this will make sure "authtools"'s migration run first

Answer 2

I met this problem while I use:

$ python manage.py test

If you didn't make migrations for those models which has a field that is a Foreignkey to django.contrib.auth.models.User, it will cause that problem.

And if you enabled --keepdb you will find there is no auth_user table and some other django's admin table.

---

Let's trace the whole problem:

run:

$ python manage.py test --verbosity=3

You can see the foreigngkey constraint exception raised after

Running deferred SQL...

the deferred sql is similar to

"ALTER TABLE xxx ADD CONSTRAINT xx FOREIGN KEY (x) REFERENCES auth_user"

check the source code of django/core/management/commands/migrate.py:

for statement in deferred_sql:
    cursor.execute(statement)

The defered_sql comes from manifest.items() for loop,

and manifest comes from all_models,

and all_models comes from the app_config.label in app_labels.

this is the argument passed by

self.sync_apps(connection, executor.loader.unmigrated_apps)

Therefore, executor.loader.unmigrated_apps will contain the unmigrated_app's label, and if you happend to has a Foreignkey to Django's auth_user, it will cause Foreignkey constrain Error cause at the time, there is no table named auth_user.

---

solution:

suppose app is the module which contains those Foreignkey attributes class:

$ python manage.py migrate auth
$ python manage.py migrate
$ python manage.py makemigrations app

and, if you have other modules depend on the app, suppose the database tables have the same field with app module, you need:

$ python manage.py make migrate app --fake

Answer 3

Have you created migrations for all your apps? If not, you may well be hitting the problem that the database tables are being created in the wrong order, which will give you this error.

If you have an existing Django 1.7 project, then you need to create the initial migration files, and then fake the initial migration, as described here

https://docs.djangoproject.com/en/1.8/topics/migrations/#adding-migrations-to-apps

Create the migration with

$ python manage.py make migrations your_app_label

And then fake the application

$  python manage.py migrate --fake-initial your_app_label

Answer 4

i had the same problem with the foreign key 'author_id'.

The solution was to change the name from

author = models.ForeignKey(User, related_name='+')

to

writer = models.ForeignKey(User, related_name='+')

so try to change your name of the field to something different than

group