Opened 9 years ago

Closed 9 years ago

Last modified 9 years ago

#24311 closed Bug (wontfix)

Foreign key constraint error when syncing unmigrated models that relate to migrated models

Reported by: Collin Anderson Owned by: nobody
Component: Migrations Version: 1.8alpha1
Severity: Release blocker Keywords:
Cc: cmawebsite@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Change History (6)

comment:1 by Tim Graham, 9 years ago

I ran into a similar problem upgrading djangoproject.com to 1.8 because django-registration doesn't have migrations. My solution was to add migrations for it. As migrations will become compulsory in 1.9, I believe expanding the warning about having unmigrated apps depend on migrated apps might be sufficient to address this rather than add more code that's applicable to 1.8 only.

comment:2 by Tim Graham, 9 years ago

Summary: can't syncdb unmigrated custom user on mysqlForeign key constraint error when syncing unmigrated models that relate to migrated models

By the way, this affects any database that enforces foreign key constraints, not just MySQL.

In 1.7, did you get FK constraints for your scenario?

Last edited 9 years ago by Tim Graham (previous) (diff)

comment:3 by Collin Anderson, 9 years ago

mysql -e'drop database testcustomuser; create database testcustomuser character set utf8'
PYTHONPATH=~/django1.7 python3 ./manage.py migrate
mysqldump testcustomuser | grep admin_log_user
  CONSTRAINT `django_admin_log_user_id_53acb657f00068f0_fk_app_myuser_id` FOREIGN KEY (`user_id`) REFERENCES `app_myuser` (`id`),
Version 0, edited 9 years ago by Collin Anderson (next)

comment:4 by Markus Holtermann, 9 years ago

Resolution: wontfix
Status: newclosed

On 1.8 this is the SQL run by the syncdb code (last query fails):

CREATE TABLE `app_myuser` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `password` varchar(128) NOT NULL, `last_login` datetime(6) NULL, `is_superuser` bool NOT NULL, `username` varchar(30) NOT NULL UNIQUE, `first_name` varchar(30) NOT NULL, `last_name` varchar(30) NOT NULL, `email` varchar(254) NOT NULL, `is_staff` bool NOT NULL, `is_active` bool NOT NULL, `date_joined` datetime(6) NOT NULL) None
SELECT engine FROM information_schema.tables WHERE table_name = %s ['app_myuser']
CREATE TABLE `app_myuser_groups` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `myuser_id` integer NOT NULL, `group_id` integer NOT NULL, UNIQUE (`myuser_id`, `group_id`)) None
SELECT engine FROM information_schema.tables WHERE table_name = %s ['app_myuser_groups']
CREATE TABLE `app_myuser_user_permissions` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `myuser_id` integer NOT NULL, `permission_id` integer NOT NULL, UNIQUE (`myuser_id`, `permission_id`)) None
SELECT engine FROM information_schema.tables WHERE table_name = %s ['app_myuser_user_permissions']
    Running deferred SQL...
ALTER TABLE `app_myuser_groups` ADD CONSTRAINT `app_myuser_groups_myuser_id_2b41e8461df0328_fk_app_myuser_id` FOREIGN KEY (`myuser_id`) REFERENCES `app_myuser` (`id`) None
ALTER TABLE `app_myuser_groups` ADD CONSTRAINT `app_myuser_groups_group_id_7a5f5bcafc6997d9_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `au

On 1.7 the respective SQL is the following (no failures):

CREATE TABLE `app_myuser_groups` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `myuser_id` integer NOT NULL, `group_id` integer NOT NULL, UNIQUE (`myuser_id`, `group_id`)); None
CREATE TABLE `app_myuser_user_permissions` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `myuser_id` integer NOT NULL, `permission_id` integer NOT NULL, UNIQUE (`myuser_id`, `permission_id`)); None
CREATE TABLE `app_myuser` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `password` varchar(128) NOT NULL, `last_login` datetime NOT NULL, `is_superuser` bool NOT NULL, `username` varchar(30) NOT NULL UNIQUE, `first_name` varchar(30) NOT NULL, `last_name` varchar(30) NOT NULL, `email` varchar(75) NOT NULL, `is_staff` bool NOT NULL, `is_active` bool NOT NULL, `date_joined` datetime NOT NULL); None
ALTER TABLE `app_myuser_groups` ADD CONSTRAINT `myuser_id_refs_id_08272aba` FOREIGN KEY (`myuser_id`) REFERENCES `app_myuser` (`id`); None
ALTER TABLE `app_myuser_user_permissions` ADD CONSTRAINT `myuser_id_refs_id_becb7d62` FOREIGN KEY (`myuser_id`) REFERENCES `app_myuser` (`id`); None
CREATE INDEX `app_myuser_groups_f1d9e869` ON `app_myuser_groups` (`myuser_id`); None
CREATE INDEX `app_myuser_groups_5f412f9a` ON `app_myuser_groups` (`group_id`); None
CREATE INDEX `app_myuser_user_permissions_f1d9e869` ON `app_myuser_user_permissions` (`myuser_id`); None
CREATE INDEX `app_myuser_user_permissions_83d7f98b` ON `app_myuser_user_permissions` (`permission_id`); None

However, looking at the constraints for app_myuser_groups this reveals a missing constraint to auth_group.

CREATE TABLE `app_myuser_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `myuser_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `myuser_id` (`myuser_id`,`group_id`),
  KEY `app_myuser_groups_f1d9e869` (`myuser_id`),
  KEY `app_myuser_groups_5f412f9a` (`group_id`),
  CONSTRAINT `myuser_id_refs_id_08272aba` FOREIGN KEY (`myuser_id`) REFERENCES `app_myuser` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

In other words, 1.7 seems to silently ignore to add constraints to missing tables.

See the docs as well: https://docs.djangoproject.com/en/1.7/topics/migrations/#unmigrated-dependencies

comment:5 by Markus Holtermann, 9 years ago

Hint: the constraints to e.g. auth_group are created if auth has been migrated before:

$ python manage.py migrate auth
$ python manage.py migrate
$ echo "SHOW CREATE TABLE app_myuser_groups;" | mysql -u django -p django
Enter password:
Table   Create Table
app_myuser_groups       CREATE TABLE `app_myuser_groups` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `myuser_id` int(11) NOT NULL,\n  `group_id` int(11) NOT NULL,\n  PRIMARY KEY (`id`),\n  UNIQUE KEY `myuser_id` (`myuser_id`,`group_id`),\n  KEY `app_myuser_groups_f1d9e869` (`myuser_id`),\n  KEY `app_myuser_groups_5f412f9a` (`group_id`),\n  CONSTRAINT `group_id_refs_id_58abbaa5` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),\n  CONSTRAINT `myuser_id_refs_id_08272aba` FOREIGN KEY (`myuser_id`) REFERENCES `app_myuser` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8

comment:6 by Collin Anderson, 9 years ago

Cool. Thanks for looking into it.

Note: See TracTickets for help on using tickets.
Back to Top