Python 创建表时Django MySQL错误

Django MySQL error when creating tables

提问by Denny

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:

我正在构建一个带有 MySQL 数据库的 django 应用程序。当我第一次运行 'python manage.py migrate' 时，一些表创建得很好，然后出现了一些错误。带出的错误是：

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

django.db.utils.IntegrityError: (1215, '不能添加外键约束')

When I run this MySQL command -

当我运行这个 MySQL 命令时 -

SHOW ENGINE INNODB STATUS\G,

显示引擎 INNODB 状态\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')

回答by James Lin

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

遇到了同样的问题，结果是 Django 以某种方式升级到v1.8，立即降级为v1.7工作。

回答by user4924681

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.

在 Django 1.8 中，syncdb 命令已被删除，而不是 syncdb try manage.py migrate 命令然后将创建表。之后你必须创建超级用户，使用超级用户创建命令或运行 manage.py syncdb，它会工作。

回答by Tobit

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

我对外键“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

回答by Rachel

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

如果您有一个现有的 Django 1.7 项目，那么您需要创建初始迁移文件，然后伪造初始迁移，如下所述

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

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

回答by hiennt

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

由于在身份验证之前运行其他迁移而导致的问题，因此这将确保首先运行“authtools”的迁移

回答by PhoebeB

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:

如果上述方法不起作用并且您不需要 Innodb，则默认为 MYISAM 有效，因为它没有相同级别的参照完整性：

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

回答by Gil Margolin

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.

外键必须与外表的主键类型相同。我在外部表中有一个 bigint，而 django 不知道如何自动创建一个 bigint 外键。

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

通过运行调查迁移失败的原因：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')

django.db.utils.IntegrityError: (1215, '不能添加外键约束')

回答by carton.swing

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 Foreignkeyto django.contrib.auth.models.User, it will cause that problem.

如果您没有为其中有一个字段是这些模型使迁移外键来django.contrib.auth.models.User，就会造成这个问题。

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

如果您启用，--keepdb您会发现没有auth_user表和其他一些 django 的管理表。

---

---

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...

运行延迟 SQL...

the deferred sql is similar to

延迟的 sql 类似于

"ALTER TABLE xxxADD CONSTRAINT xxFOREIGN KEY (x) REFERENCES auth_user"

“更改表xxx添加约束外xx键 ( x) 引用auth_user”

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

检查django/core/management/commands/migrate.py的源代码：

for statement in deferred_sql:
    cursor.execute(statement)

The defered_sqlcomes from manifest.items()for loop,

将defered_sql来自manifest.items()for循环，

and manifestcomes from all_models,

并且manifest来自all_models，

and all_modelscomes from the app_config.label in app_labels.

而all_models来自于app_config.label app_labels。

this is the argument passed by

这是通过的参数

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

Therefore, executor.loader.unmigrated_appswill 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.

因此，executor.loader.unmigrated_apps会包含unmigrated_app的标签，如果你碰巧有一个Foreignkey到Django的auth_user，就会导致Foreignkey constrain 错误，导致当时没有名为auth_user的表。

---

---

solution:

解决方案：

suppose appis the module which contains those Foreignkey attributes class:

假设app是包含这些外键属性类的模块：

$ 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 appmodule, you need:

并且，如果您有其他模块依赖于app，假设数据库表与app模块具有相同的字段，您需要：

$ python manage.py make migrate app --fake

回答by Lingster

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.

我在尝试在 BitBucket/Pipelines 中设置 CI 时遇到了同样的错误。问题是我没有将迁移文件夹提交到我们的 git 存储库中，因为管道每次都从头开始重建所有内容，单元测试无法启动。

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.

我的测试将在运行 makemigration/migrate 步骤并确保我们的非测试代码正常工作后工作。

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/

可以在此处找到有关 django 迁移的更多详细信息：https: //docs.djangoproject.com/en/1.11/topics/migrations/

回答by Tony S Yu

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 answeraddresses, but takes a different approach to solve the problem.

我在针对 MySQL 数据库对 Django 库运行测试时遇到了类似的问题（以避免Django 2.2 与 CentOS 7 不兼容）。这类似于carton.swing 的回答地址的问题，但采取了不同的方法来解决问题。

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

关于我正在测试的库的一些说明：

• The librarydoes not define any models, but the test suitedefines modelsfor testing purposes
• The test suite depends on foreign keys to models it does not define, specifically the auth.Usermodel.

• 该库没有定义任何模型，但测试套件定义了用于测试目的的模型。
• 测试套件依赖于它没有定义的auth.User模型的外键，特别是模型。

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

在查看了旧的 Django 票证后，我注意到了这个回复：

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

您的问题是未迁移的应用程序（身份验证令牌）依赖于（具有 FK 到）迁移的应用程序（主）。这是众所周知的禁忌。

(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.Usersupplied 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-migrationsflag), which fixed the issue.

（我找不到任何其他关于这是“众所周知的禁忌”的引用。）由于测试套件定义了一些模型，并且还使用auth.User了 Django 提供的使用，我猜是测试运行器（pytest在这种情况下）已迁移测试套件中定义但未迁移的模型auth.User。为了测试这一点，我关闭了我的测试套件上的迁移（对于pytest-django，这只需要添加一个--no-migrations标志），从而解决了这个问题。