Opened 6 years ago

Closed 6 years ago

Last modified 6 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

Change History (6)

comment:1 Changed 6 years ago by Tim Graham

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 Changed 6 years ago by Tim Graham

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 6 years ago by Tim Graham (previous) (diff)

comment:3 Changed 6 years ago by Collin Anderson

Yes.

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`),
Last edited 6 years ago by Collin Anderson (previous) (diff)

comment:4 Changed 6 years ago by Markus Holtermann

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 Changed 6 years ago by Markus Holtermann

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 Changed 6 years ago by Collin Anderson

Cool. Thanks for looking into it.

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