#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
Test app: https://github.com/collinanderson/testcustomuser/commit/544eca4061f068d90e4c17a4af99a982bd6c641f
Traceback: https://gist.github.com/collinanderson/9df14bbeed50c0000ea6
works fine on 1.7
works on sqlite
Change History (6)
comment:1 by , 10 years ago
comment:2 by , 10 years ago
Summary: | can't syncdb unmigrated custom user on mysql → Foreign 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?
comment:3 by , 10 years ago
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`),
comment:4 by , 10 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
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 , 10 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
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.