[Django]-Django MySQL error when creating tables

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

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

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

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

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/

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

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.

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

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

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.

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",
    }   
  }   
}