hydra: FK constraint hydra_oauth2_access_challenge_id_fk violation

Preflight checklist

• I could not find a solution in the existing issues, docs, nor discussions.
• I agree to follow this project’s Code of Conduct.
• I have read and am following this repository’s Contribution Guidelines.
• This issue affects my Ory Network project.
• I have joined the Ory Community Slack.
• I am signed up to the Ory Security Patch Newsletter.

Describe the bug

I just tried to upgrade Hydra from 1.11.10 to 2.0.1 (tried upgrading to 2.0.0 first as well). However, I’m getting a FK constraint error when running the migrations.

ERROR: insert or update on table "hydra_oauth2_access" violates foreign key constraint "hydra_oauth2_access_challenge_id_fk" (SQLSTATE 23503)

How should I proceed?

Reproducing the bug

1. run Hydra 1.10 in Docker
2. update the image to 2.0.1 or 2.0.0
3. run hydra migrate sql -e --yes --config /etc/config/hydra/hydra.yml

Relevant log output

app-hydra-1  | Could not apply migrations:
app-hydra-1  | ERROR: insert or update on table "hydra_oauth2_access" violates foreign key constraint "hydra_oauth2_access_challenge_id_fk" (SQLSTATE 23503)
app-hydra-1  | error executing migrations/20211019000001000002_merge_authentication_request_tables.postgres.up.sql, sql: -- Migration generated by the command below; DO NOT EDIT.
app-hydra-1  | -- hydra:generate hydra migrate gen
app-hydra-1  |
app-hydra-1  | CREATE INDEX hydra_oauth2_flow_client_id_subject_idx ON public.hydra_oauth2_flow USING btree (client_id, subject);
app-hydra-1  | CREATE INDEX hydra_oauth2_flow_cid_idx ON public.hydra_oauth2_flow USING btree (client_id);
app-hydra-1  | CREATE INDEX hydra_oauth2_flow_login_session_id_idx ON public.hydra_oauth2_flow USING btree (login_session_id);
app-hydra-1  | CREATE INDEX hydra_oauth2_flow_sub_idx ON public.hydra_oauth2_flow USING btree (subject);
app-hydra-1  | CREATE UNIQUE INDEX hydra_oauth2_flow_consent_challenge_idx ON public.hydra_oauth2_flow USING btree (consent_challenge_id);
app-hydra-1  | CREATE UNIQUE INDEX hydra_oauth2_flow_login_verifier_idx ON public.hydra_oauth2_flow USING btree (login_verifier);
app-hydra-1  | CREATE INDEX hydra_oauth2_flow_consent_verifier_idx ON public.hydra_oauth2_flow USING btree (consent_verifier);
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_flow ADD CONSTRAINT hydra_oauth2_flow_pkey PRIMARY KEY (login_challenge);
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_flow ADD CONSTRAINT hydra_oauth2_flow_client_id_fk FOREIGN KEY (client_id) REFERENCES public.hydra_client(id) ON DELETE CASCADE;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_flow ADD CONSTRAINT hydra_oauth2_flow_login_session_id_fk FOREIGN KEY (login_session_id) REFERENCES public.hydra_oauth2_authentication_session(id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_access DROP CONSTRAINT hydra_oauth2_access_challenge_id_fk;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_access ADD CONSTRAINT hydra_oauth2_access_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES public.hydra_oauth2_flow(consent_challenge_id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_code DROP CONSTRAINT hydra_oauth2_code_challenge_id_fk;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_code ADD CONSTRAINT hydra_oauth2_code_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES public.hydra_oauth2_flow(consent_challenge_id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_oidc DROP CONSTRAINT hydra_oauth2_oidc_challenge_id_fk;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_oidc ADD CONSTRAINT hydra_oauth2_oidc_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES public.hydra_oauth2_flow(consent_challenge_id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_pkce DROP CONSTRAINT hydra_oauth2_pkce_challenge_id_fk;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_pkce ADD CONSTRAINT hydra_oauth2_pkce_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES public.hydra_oauth2_flow(consent_challenge_id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_refresh DROP CONSTRAINT hydra_oauth2_refresh_challenge_id_fk;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_refresh ADD CONSTRAINT hydra_oauth2_refresh_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES public.hydra_oauth2_flow(consent_challenge_id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  |
app-hydra-1  | github.com/ory/x/popx.NewMigrationBox.func1.1
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migration_box.go:158
app-hydra-1  | github.com/ory/x/popx.Migration.Run
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migration_info.go:34
app-hydra-1  | github.com/ory/x/popx.(*Migrator).UpTo.func1.2
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:146
app-hydra-1  | github.com/ory/x/popx.(*Migrator).isolatedTransaction
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:320
app-hydra-1  | github.com/ory/x/popx.(*Migrator).UpTo.func1
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:145
app-hydra-1  | github.com/ory/x/popx.(*Migrator).exec
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:564
app-hydra-1  | github.com/ory/x/popx.(*Migrator).UpTo
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:99
app-hydra-1  | github.com/ory/x/popx.(*Migrator).Up
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:85
app-hydra-1  | github.com/ory/hydra/persistence/sql.(*Persister).MigrateUp
app-hydra-1  |  /project/persistence/sql/persister_migration.go:48
app-hydra-1  | github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
app-hydra-1  |  /project/cmd/cli/handler_migrate.go:341
app-hydra-1  | github.com/spf13/cobra.(*Command).execute
app-hydra-1  |  /go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:872
app-hydra-1  | github.com/spf13/cobra.(*Command).ExecuteC
app-hydra-1  |  /go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:990
app-hydra-1  | github.com/spf13/cobra.(*Command).Execute
app-hydra-1  |  /go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:918
app-hydra-1  | github.com/ory/hydra/cmd.Execute
app-hydra-1  |  /project/cmd/root.go:118
app-hydra-1  | main.main
app-hydra-1  |  /project/main.go:31
app-hydra-1  | runtime.main
app-hydra-1  |  /usr/local/go/src/runtime/proc.go:250
app-hydra-1  | runtime.goexit
app-hydra-1  |  /usr/local/go/src/runtime/asm_arm64.s:1172
app-hydra-1  | this error should never be printed

Relevant configuration

No response

Version

2.0.1

On which operating system are you observing this issue?

macOS

In which environment are you deploying?

Docker Compose

Additional Context

No response