After Updating Django, Migration Fails for oauth2_provider
2022-09-16 19:11 (2 years ago)
ytyng
Background
The migration for the oauth2_provider app included in Django's django-oauth-toolkit failed at 0004.
When running ./manage.py migrate oauth2_provider, an exception is thrown due to the inability to create the Foreign Key.
If the database migration stops midway, the DDL ends up in an incomplete state, making recovery quite tedious.
SHOW CREATE TABLE oauth2_provider_application;
Check if it shows
`id` bigint(20) NOT NULL AUTO_INCREMENT,
If it shows
`id` int(11) NOT NULL AUTO_INCREMENT,
then creating the FK constraint oauth2_provider_idtoken.application_id → oauth2_provider_application.id will fail because the data types don't match.
The likely cause is that in the past, when using an older version of Django, the migration for oauth2_provider was progressed up to 0003, resulting in oauth2_provider_application.id being created as int(11).
In recent versions of oauth2_provider, the id field is created as bigint(20), making it incompatible with the older int(11) for FK creation.
To enable FK creation, it's necessary to ALTER TABLE from int(11) to bigint(20). However, since FK constraints already exist between the existing ids, changing from int to bigint is a very tedious task.
Work Description
1. Ignore the fact that migration 0004 stopped midway due to an exception. Do not revert to 0003.
Trying to revert will only complicate things, so for now, forget about the migration.
2. Record the SHOW CREATE TABLE of all oauth2_provider tables as text.
It will likely look something like this.
show create table oauth2_provider_accesstoken; CREATE TABLE `oauth2_provider_accesstoken` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `token` varchar(255) NOT NULL, `expires` datetime(6) NOT NULL, `scope` longtext NOT NULL, `application_id` bigint(20) DEFAULT NULL, `user_id` int(11) DEFAULT NULL, `created` datetime(6) NOT NULL, `updated` datetime(6) NOT NULL, `source_refresh_token_id` bigint(20) DEFAULT NULL, `id_token_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `token` (`token`), UNIQUE KEY `source_refresh_token_id` (`source_refresh_token_id`), UNIQUE KEY `id_token_id` (`id_token_id`), KEY `oauth2_provider_accesstoken_user_id_6e4c9a65_fk_user_user_id` (`user_id`), CONSTRAINT `oauth2_provider_acce_id_token_id_85db651b_fk_oauth2_pr` FOREIGN KEY (`id_token_id`) REFERENCES `oauth2_provider_idtoken` (`id`), CONSTRAINT `oauth2_provider_acce_source_refresh_token_e66fbc72_fk_oauth2_pr` FOREIGN KEY (`source_refresh_token_id`) REFERENCES `oauth2_provider_refreshtoken` (`id`), CONSTRAINT `oauth2_provider_accesstoken_user_id_6e4c9a65_fk_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_user` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
show create table oauth2_provider_application; CREATE TABLE `oauth2_provider_application` ( `id` int(11) NOT NULL AUTO_INCREMENT, `client_id` varchar(100) NOT NULL, `redirect_uris` longtext NOT NULL, `client_type` varchar(32) NOT NULL, `authorization_grant_type` varchar(32) NOT NULL, `client_secret` varchar(255) NOT NULL, `name` varchar(255) NOT NULL, `user_id` int(11) NOT NULL, `skip_authorization` tinyint(1) NOT NULL, `algorithm` varchar(5) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `client_id` (`client_id`), KEY `oauth2_provider_application_9d667c2b` (`client_secret`), KEY `oauth2_provider_application_user_id_79829054_fk_user_user_id` (`user_id`), CONSTRAINT `oauth2_provider_application_user_id_79829054_fk_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_user` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
show create table oauth2_provider_grant; CREATE TABLE `oauth2_provider_grant` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code` varchar(255) NOT NULL, `expires` datetime(6) NOT NULL, `redirect_uri` longtext NOT NULL, `scope` longtext NOT NULL, `application_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `code_challenge` varchar(128) NOT NULL, `code_challenge_method` varchar(10) NOT NULL, `nonce` varchar(255) NOT NULL, `claims` longtext NOT NULL, PRIMARY KEY (`id`), KEY `oauth2_application_id_81923564_fk_oauth2_provider_application_id` (`application_id`), KEY `oauth2_provider_grant_user_id_e8f62af8_fk_user_user_id` (`user_id`), KEY `oauth2_provider_grant_c1336794` (`code`), CONSTRAINT `oauth2_application_id_81923564_fk_oauth2_provider_application_id` FOREIGN KEY (`application_id`) REFERENCES `oauth2_provider_application` (`id`), CONSTRAINT `oauth2_provider_grant_user_id_e8f62af8_fk_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_user` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
show create table oauth2_provider_idtoken; CREATE TABLE `oauth2_provider_idtoken` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `jti` char(32) NOT NULL, `expires` datetime(6) NOT NULL, `scope` longtext NOT NULL, `created` datetime(6) NOT NULL, `updated` datetime(6) NOT NULL, `application_id` bigint(20) DEFAULT NULL, `user_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `jti` (`jti`), KEY `oauth2_provider_idtoken_user_id_dd512b59_fk_user_user_id` (`user_id`), CONSTRAINT `oauth2_provider_idtoken_user_id_dd512b59_fk_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_user` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
show create table oauth2_provider_refreshtoken; CREATE TABLE `oauth2_provider_refreshtoken` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `token` varchar(255) NOT NULL, `access_token_id` bigint(20) DEFAULT NULL, `application_id` bigint(20) NOT NULL, `user_id` int(11) NOT NULL, `created` datetime(6) NOT NULL, `updated` datetime(6) NOT NULL, `revoked` datetime(6) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `access_token_id` (`access_token_id`), UNIQUE KEY `oauth2_provider_refreshtoken_token_revoked_af8a5134_uniq` (`token`,`revoked`), KEY `oauth2_provider_refreshtoken_user_id_da837fce_fk_user_user_id` (`user_id`), CONSTRAINT `oauth2_provider_refr_access_token_id_775e84e8_fk_oauth2_pr` FOREIGN KEY (`access_token_id`) REFERENCES `oauth2_provider_accesstoken` (`id`), CONSTRAINT `oauth2_provider_refreshtoken_user_id_da837fce_fk_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_user` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
3. Remove all Foreign Key Constraints between oauth2_provider tables
alter table oauth2_provider_accesstoken drop foreign key oauth2_provider_acce_id_token_id_85db651b_fk_oauth2_pr, drop foreign key oauth2_provider_acce_source_refresh_token_e66fbc72_fk_oauth2_pr;
alter table oauth2_provider_grant drop foreign key oauth2_application_id_81923564_fk_oauth2_provider_application_id;
alter table oauth2_provider_refreshtoken drop foreign key oauth2_provider_refr_access_token_id_775e84e8_fk_oauth2_pr, drop foreign key oauth2_provider_refreshtoken_user_id_da837fce_fk_user_user_id;
Remove them this way. Compare with the actual state of the tables while doing this.
4. Change int to bigint
alter table oauth2_provider_accesstoken modify id bigint auto_increment;
alter table oauth2_provider_application modify id bigint auto_increment;
alter table oauth2_provider_grant modify id bigint auto_increment, modify application_id bigint NOT NULL;
Do it similarly. This might not cover everything.
5. Recreate the FKs
Use the text created in step 2 to construct the alter table statements.
alter table oauth2_provider_accesstoken add CONSTRAINT `oauth2_provider_acce_id_token_id_85db651b_fk_oauth2_pr` FOREIGN KEY (`id_token_id`) REFERENCES `oauth2_provider_idtoken` (`id`), add CONSTRAINT `oauth2_provider_acce_source_refresh_token_e66fbc72_fk_oauth2_pr` FOREIGN KEY (`source_refresh_token_id`) REFERENCES `oauth2_provider_refreshtoken` (`id`);
alter table oauth2_provider_grant add CONSTRAINT `oauth2_application_id_81923564_fk_oauth2_provider_application_id` FOREIGN KEY (`application_id`) REFERENCES `oauth2_provider_application` (`id`);
alter table oauth2_provider_refreshtoken add CONSTRAINT `oauth2_provider_refr_access_token_id_775e84e8_fk_oauth2_pr` FOREIGN KEY (`access_token_id`) REFERENCES `oauth2_provider_accesstoken` (`id`), add CONSTRAINT `oauth2_provider_refreshtoken_user_id_da837fce_fk_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_user` (`id`);
6. Manually execute migration 0004
This is the content of 0004. If an error occurred and it failed, it means it succeeded partially. Everything is idempotent, so issuing the SQL again is not a problem.
ALTER TABLE `oauth2_provider_application` ALTER COLUMN `algorithm` DROP DEFAULT; CREATE TABLE `oauth2_provider_idtoken` (`id` bigint AUTO_INCREMENT NOT NULL PRIMARY KEY, `jti` char(32) NOT NULL UNIQUE, `expires` datetime(6) NOT NULL, `scope` longtext NOT NULL, `created` datetime(6) NOT NULL, `updated` datetime(6) NOT NULL, `application_id` bigint NULL, `user_id` integer NULL); ALTER TABLE `oauth2_provider_accesstoken` ADD COLUMN `id_token_id` bigint NULL UNIQUE , ADD CONSTRAINT `oauth2_provider_acce_id_token_id_85db651b_fk_oauth2_pr` FOREIGN KEY (`id_token_id`) REFERENCES `oauth2_provider_idtoken`(`id`); ALTER TABLE `oauth2_provider_grant` ADD COLUMN `nonce` varchar(255) DEFAULT '' NOT NULL; ALTER TABLE `oauth2_provider_grant` ALTER COLUMN `nonce` DROP DEFAULT; ALTER TABLE `oauth2_provider_grant` ADD COLUMN `claims` longtext NOT NULL; ALTER TABLE `oauth2_provider_idtoken` ADD CONSTRAINT `oauth2_provider_idto_application_id_08c5ff4f_fk_oauth2_pr` FOREIGN KEY (`application_id`) REFERENCES `oauth2_provider_application` (`id`); ALTER TABLE `oauth2_provider_idtoken` ADD CONSTRAINT `oauth2_provider_idtoken_user_id_dd512b59_fk_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_user` (`id`);
7. Mark migration 0004 as completed with a fake migration
./manage.py showmigrations oauth2_provider ./manage.py migrate oauth2_provider 0004 --fake ./manage.py showmigrations oauth2_provider
8. Complete the migration
./manage.py migrate oauth2_provider
In my case,
django.db.utils.OperationalError: (1054, "Unknown column 'oauth2_provider_application.created' in 'field list'")
occurred, so
ALTER TABLE oauth2_provider_application ADD COLUMN `created` datetime(6), ADD COLUMN `updated` datetime(6);
I executed this. Since created and updated are fields in the abstract class of oauth2_provider, similar columns need to be added to all tables of oauth2_provider.
Comments