CODEWITHSUNDEEP
pythonmysqldjango
Denny
Denny

Reputation: 1759

Django MySQL error when creating tables

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')

Upvotes: 35

Views: 25522

Answers (11)

Yong
Yong

Reputation: 974

Ran into a similar situation whereby there's

django.db.utils.OperationalError: (1824, "Failed to open the referenced table 'auth_user'")

at first, which I resolved by making sure I have all the migrations done properly, every app has its corresponding migrations folder and also __init__.py file within it.

After which I had the similar issue with another table in the database that raised another error and that's preventing me from starting the test. I solved it by removing all the records in django_migrations table, deleting all the migration files (if you are ok with it). Then I run

python manage.py makemigrations

python manage.py migrate --fake

All the nonsense of the migrations dealt with :)

Upvotes: 0

Tony S Yu
Tony S Yu

Reputation: 3163

I ran into a similar issue while running tests on a Django library against a MySQL database (to avoid Django 2.2 incompatibilities with CentOS 7). This is similar to the problem carton.swing's answer addresses, but takes a different approach to solve the problem.

A couple of notes about the library I'm testing:

  • The library does not define any models, but the test suite defines models for testing purposes
  • The test suite depends on foreign keys to models it does not define, specifically the auth.User model.

After looking into an old Django ticket, I noticed this response:

your problem is that an unmigrated app (authtoken) depends (has a FK to) a migrated app (main). This is a well-known no-no.

(I couldn't find any other references to this being a "well-known no-no".) Since the test suite defines some models, and also uses auth.User supplied by Django, I guessed that the test runner (pytest, in this case) migrated models defined in the test suite but did not migrate auth.User. To test this, I turned off migrations on my test suite (for pytest-django, this just required adding a --no-migrations flag), which fixed the issue.

Upvotes: 0

Lingster
Lingster

Reputation: 1087

I had the same error whilst trying to setup CI in BitBucket/Pipelines. The problem was that I had not committed the migrations folder into our git repository, as Pipelines rebuilds everything from scratch each time, the unit tests were failing to start.

The migrations folder is created when you execute:

python manage.py makemigrations
python manage.py makemigrations <module_name>

My tests would work after running the makemigrations/migrate steps and ensuring that our non-test code was working.

It seems that:

python manage.py test

will try to generate the migrations, if they don't currently exist, but it can't always get the dependencies right, hence you need to ensure that you commit the auto-generated code in the migrations folder to your source code repository.

More details about django migrations can be found here: https://docs.djangoproject.com/en/1.11/topics/migrations/

Upvotes: 2

carton.swing
carton.swing

Reputation: 1707

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

Upvotes: 11

Gil Margolin
Gil Margolin

Reputation: 2119

Make sure both foreign key and primary key are of the same type.

Foreign key has to be the same type as the primary key of the foreign table. I had a bigint in the foreign table and django did not know how to create a foreign key that was a bigint automatically.

Investigated why the migration was failing by running: python manage.py sqlmigrate {app_name} {migration_name}

Run this in the workbench and you will see the real error behind the failure.

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

Upvotes: 1

PhoebeB
PhoebeB

Reputation: 8570

If the above does not work and you do not need Innodb, defaulting to MYISAM works as it does not have the same level of referential integrity:

DATABASES = { 
  'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': 'name',                      
    'USER': 'user',     
    'PASSWORD': 'password',
    'OPTIONS': {
           "init_command": "SET storage_engine=MYISAM",
    }   
  }   
}

Upvotes: -3

hiennt
hiennt

Reputation: 890

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

Upvotes: 40

Rachel
Rachel

Reputation: 2894

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

Upvotes: 9

Tobit
Tobit

Reputation: 406

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

Upvotes: 4

user4924681
user4924681

Reputation: 1

In Django 1.8 syncdb command has been removed, instead of syncdb try manage.py migrate command then tables will create. after that you have to create superuser use superuser creating command or run manage.py syncdb, It will work.

Upvotes: -3

James Lin
James Lin

Reputation: 26528

Had the same problem, turned out that somehow Django got upgraded to v1.8, promptly downgraded to v1.7 worked.

Upvotes: -1

Related Questions