hydra-automigrate job fails on 0.25.6 -> 0.26.0 upgrade: table "hydra_oauth2_access" violates foreign key constraint "hydra_oauth2_access_challenge_id_fk"
Closed this issue · 3 comments
zagr0 commented
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
When you upgrade hydra helm chart deployment from 0.25.6 -> 0.26.0 the auto migration job fails with error:
table "hydra_oauth2_access" violates foreign key constraint "hydra_oauth2_access_challenge_id_fk"
Reproducing the bug
- do
helm upgrade ory/hydrafrom 0.25.6 to 0.26.0
Relevant log output
time=2022-11-02T13:45:39Z level=info msg=No tracer configured - skipping tracing setup audience=application service_name=Ory Hydra service_version=v2.0.1
The following migration is planned:
Version Name Status
20150101000001000000 networks Applied
20190100000001000000 client Applied
20190100000002000000 client Applied
20190100000003000000 client Applied
20190100000004000000 client Applied
20190100000005000000 client Applied
20190100000006000000 client Applied
20190100000007000000 client Applied
20190100000008000000 client Applied
20190100000009000000 client Applied
20190100000010000000 client Applied
20190100000011000000 client Applied
20190100000012000000 client Applied
20190100000013000000 client Applied
20190100000014000000 client Applied
20190200000001000000 jwk Applied
20190200000002000000 jwk Applied
20190200000003000000 jwk Applied
20190200000004000000 jwk Applied
20190300000001000000 consent Applied
20190300000002000000 consent Applied
20190300000003000000 consent Applied
20190300000004000000 consent Applied
20190300000005000000 consent Applied
20190300000006000000 consent Applied
20190300000007000000 consent Applied
20190300000008000000 consent Applied
20190300000009000000 consent Applied
20190300000010000000 consent Applied
20190300000011000000 consent Applied
20190300000012000000 consent Applied
20190300000013000000 consent Applied
20190300000014000000 consent Applied
20190400000001000000 oauth2 Applied
20190400000002000000 oauth2 Applied
20190400000003000000 oauth2 Applied
20190400000004000000 oauth2 Applied
20190400000005000000 oauth2 Applied
20190400000006000000 oauth2 Applied
20190400000007000000 oauth2 Applied
20190400000008000000 oauth2 Applied
20190400000009000000 oauth2 Applied
20190400000010000000 oauth2 Applied
20190400000011000000 oauth2 Applied
20200521071434000000 consent Applied
20200527215731000000 client Applied
20200527215732000000 client Applied
20200819163013000000 add_client_id_subject_idx_to_access_and_refresh Applied
20200913192340000000 initial_sqlite Applied
20201110104000000000 drop_uq_oauth2 Applied
20201116133000000000 set_null_time Applied
20210928155900000000 support_amr_claim Applied
20210928175900000000 client_custom_token_ttl Applied
20211004110001000000 change_client_primary_key Applied
20211004110002000000 change_client_primary_key Applied
20211004110003000000 change_client_primary_key Applied
20211011000001000000 change_jwk_primary_key Applied
20211011000002000000 change_jwk_primary_key Applied
20211011000003000000 change_jwk_primary_key Applied
20211019000001000000 merge_authentication_request_tables Applied
20211019000001000001 merge_authentication_request_tables Applied
20211019000001000002 merge_authentication_request_tables Pending
20211019000001000003 merge_authentication_request_tables Pending
20211019000001000004 merge_authentication_request_tables Pending
20211019000001000005 merge_authentication_request_tables Pending
20211019000001000006 merge_authentication_request_tables Pending
20211019000001000007 merge_authentication_request_tables Pending
20211019000001000008 merge_authentication_request_tables Pending
20211019000001000009 merge_authentication_request_tables Pending
20211019000001000010 merge_authentication_request_tables Pending
20211019000001000011 merge_authentication_request_tables Pending
20211019000001000012 merge_authentication_request_tables Pending
20211019000001000013 merge_authentication_request_tables Pending
20211019000001000014 merge_authentication_request_tables Pending
20211019000001000015 merge_authentication_request_tables Pending
20211019000001000016 merge_authentication_request_tables Pending
20211019000001000017 merge_authentication_request_tables Pending
20211019000001000018 merge_authentication_request_tables Pending
20211019000001000019 merge_authentication_request_tables Pending
20211019000001000020 merge_authentication_request_tables Pending
20211019000001000021 merge_authentication_request_tables Pending
20211019000001000022 merge_authentication_request_tables Pending
20211019000001000023 merge_authentication_request_tables Pending
20211019000001000024 merge_authentication_request_tables Pending
20211019000001000025 merge_authentication_request_tables Pending
20211019000001000026 merge_authentication_request_tables Pending
20211019000001000027 merge_authentication_request_tables Pending
20211019000001000028 merge_authentication_request_tables Pending
20211019000001000029 merge_authentication_request_tables Pending
20211019000001000030 merge_authentication_request_tables Pending
20211019000001000031 merge_authentication_request_tables Pending
20211019000001000032 merge_authentication_request_tables Pending
20211019000001000033 merge_authentication_request_tables Pending
20211019000001000034 merge_authentication_request_tables Pending
20211019000001000035 merge_authentication_request_tables Pending
20211019000001000036 merge_authentication_request_tables Pending
20211019000001000037 merge_authentication_request_tables Pending
20211019000001000038 merge_authentication_request_tables Pending
20211019000001000039 merge_authentication_request_tables Pending
20211226155900000000 grant_jwk_bearer Applied
20211226156000000000 dynamic_registration Applied
20220210000001000000 nid Pending
20220210000001000001 nid Pending
20220210000001000002 nid Pending
20220210000001000003 nid Pending
20220210000001000004 nid Pending
20220210000001000005 nid Pending
20220210000001000006 nid Pending
20220210000001000007 nid Pending
20220210000001000008 nid Pending
20220210000001000009 nid Pending
20220210000001000010 nid Pending
20220210000001000011 nid Pending
20220210000001000012 nid Pending
20220210000001000013 nid Pending
20220210000001000014 nid Pending
20220210000001000015 nid Pending
20220210000001000016 nid Pending
20220210000001000017 nid Pending
20220210000001000018 nid Pending
20220210000001000019 nid Pending
20220210000001000020 nid Pending
20220210000001000021 nid Pending
20220210000001000022 nid Pending
20220210000001000023 nid Pending
20220210000001000024 nid Pending
20220210000001000025 nid Pending
20220210000001000026 nid Pending
20220210000001000027 nid Pending
20220210000001000028 nid Pending
20220210000001000029 nid Pending
20220210000001000030 nid Pending
20220210000001000031 nid Pending
20220210000001000032 nid Pending
20220210000001000033 nid Pending
20220210000001000034 nid Pending
20220210000001000035 nid Pending
20220210000001000036 nid Pending
20220210000001000037 nid Pending
20220210000001000038 nid Pending
20220210000001000039 nid Pending
20220210000001000040 nid Pending
20220210000001000041 nid Pending
20220210000001000042 nid Pending
20220210000001000043 nid Pending
20220210000001000044 nid Pending
20220210000001000045 nid Pending
20220210000001000046 nid Pending
20220210000001000047 nid Pending
20220210000001000048 nid Pending
20220210000001000049 nid Pending
20220210000001000050 nid Pending
20220210000001000051 nid Pending
20220210000001000052 nid Pending
20220210000001000053 nid Pending
20220210000001000054 nid Pending
20220210000001000055 nid Pending
20220210000001000056 nid Pending
20220210000001000057 nid Pending
20220210000001000058 nid Pending
20220210000001000059 nid Pending
20220210000001000060 nid Pending
20220210000001000061 nid Pending
20220210000001000062 nid Pending
20220210000001000063 nid Pending
20220210000001000064 nid Pending
20220210000001000065 nid Pending
20220210000001000066 nid Pending
20220210000001000067 nid Pending
20220210000001000068 nid Pending
20220210000001000069 nid Pending
20220210000001000070 nid Pending
20220210000001000071 nid Pending
20220210000001000072 nid Pending
20220210000001000073 nid Pending
20220210000001000074 nid Pending
20220210000001000075 nid Pending
20220210000001000076 nid Pending
20220210000001000077 nid Pending
20220210000001000078 nid Pending
20220210000001000079 nid Pending
20220328111500000000 support_any_subject_trusts Applied
20220513000001000000 string_slice_json Pending
20220513000001000001 string_slice_json Pending
20220513000001000002 string_slice_json Pending
20220513000001000003 string_slice_json Pending
20220513000001000004 string_slice_json Pending
20220513000001000005 string_slice_json Pending
20220513000001000006 string_slice_json Pending
20220513000001000007 string_slice_json Pending
20220513000001000008 string_slice_json Pending
20220513000001000009 string_slice_json Pending
20220513000001000010 string_slice_json Pending
20220916000010000000 hydra_oauth2_flow Pending
Could not apply migrations:
ERROR: insert or update on table "hydra_oauth2_access" violates foreign key constraint "hydra_oauth2_access_challenge_id_fk" (SQLSTATE 23503)
error executing migrations/20211019000001000002_merge_authentication_request_tables.postgres.up.sql, sql: -- Migration generated by the command below; DO NOT EDIT.
-- hydra:generate hydra migrate gen
CREATE INDEX hydra_oauth2_flow_client_id_subject_idx ON public.hydra_oauth2_flow USING btree (client_id, subject);
CREATE INDEX hydra_oauth2_flow_cid_idx ON public.hydra_oauth2_flow USING btree (client_id);
CREATE INDEX hydra_oauth2_flow_login_session_id_idx ON public.hydra_oauth2_flow USING btree (login_session_id);
CREATE INDEX hydra_oauth2_flow_sub_idx ON public.hydra_oauth2_flow USING btree (subject);
CREATE UNIQUE INDEX hydra_oauth2_flow_consent_challenge_idx ON public.hydra_oauth2_flow USING btree (consent_challenge_id);
CREATE UNIQUE INDEX hydra_oauth2_flow_login_verifier_idx ON public.hydra_oauth2_flow USING btree (login_verifier);
this error should never be printed
CREATE INDEX hydra_oauth2_flow_consent_verifier_idx ON public.hydra_oauth2_flow USING btree (consent_verifier);
ALTER TABLE ONLY public.hydra_oauth2_flow ADD CONSTRAINT hydra_oauth2_flow_pkey PRIMARY KEY (login_challenge);
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;
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;
ALTER TABLE ONLY public.hydra_oauth2_access DROP CONSTRAINT hydra_oauth2_access_challenge_id_fk;
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;
ALTER TABLE ONLY public.hydra_oauth2_code DROP CONSTRAINT hydra_oauth2_code_challenge_id_fk;
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;
ALTER TABLE ONLY public.hydra_oauth2_oidc DROP CONSTRAINT hydra_oauth2_oidc_challenge_id_fk;
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;
ALTER TABLE ONLY public.hydra_oauth2_pkce DROP CONSTRAINT hydra_oauth2_pkce_challenge_id_fk;
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;
ALTER TABLE ONLY public.hydra_oauth2_refresh DROP CONSTRAINT hydra_oauth2_refresh_challenge_id_fk;
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;
github.com/ory/x/popx.NewMigrationBox.func1.1
/go/pkg/mod/github.com/ory/x@v0.0.486/popx/migration_box.go:158
github.com/ory/x/popx.Migration.Run
/go/pkg/mod/github.com/ory/x@v0.0.486/popx/migration_info.go:34
github.com/ory/x/popx.(*Migrator).UpTo.func1.2
/go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:146
github.com/ory/x/popx.(*Migrator).isolatedTransaction
/go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:320
github.com/ory/x/popx.(*Migrator).UpTo.func1
/go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:145
github.com/ory/x/popx.(*Migrator).exec
/go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:564
github.com/ory/x/popx.(*Migrator).UpTo
/go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:99
github.com/ory/x/popx.(*Migrator).Up
/go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:85
github.com/ory/hydra/persistence/sql.(*Persister).MigrateUp
/project/persistence/sql/persister_migration.go:48
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
/project/cmd/cli/handler_migrate.go:341
github.com/spf13/cobra.(*Command).execute
/go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:872
github.com/spf13/cobra.(*Command).ExecuteC
/go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:990
github.com/spf13/cobra.(*Command).Execute
/go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:918
github.com/ory/hydra/cmd.Execute
/project/cmd/root.go:118
main.main
/project/main.go:31
runtime.main
/usr/local/go/src/runtime/proc.go:250
runtime.goexit
/usr/local/go/src/runtime/asm_amd64.s:1594
### Relevant configuration
_No response_
### Version
0.26.0
### On which operating system are you observing this issue?
Linux
### In which environment are you deploying?
Kubernetes with Helm
### Additional Context
_No response_
zagr0 commented
the bad thing is that when I rollback to 0.25.6 I'm getting:
time=2022-11-02T14:39:17Z level=fatal msg=Could not ensure that signing keys for "hydra.openid.id-token" exists. If you are running against a persistent SQL database this is most likely because your "secrets.system" ("SECRETS_SYSTEM" environment variable) is not set or changed. When running with an SQL database backend you need to make sure that the secret is set and stays the same, unless when doing key rotation. This may also happen when you forget to run "hydra migrate sql".. audience=application error=map[message:unable to fetch records: sql: Scan error on column index 3, name "pk": converting driver.Value type string ("08eed7fe-68b3-47eb-8a46-94397d81e34d") to a int: invalid syntax] service_name=Ory Hydra service_version=v1.11.8
we use external secret to provide cookie and systems hydra secrets:
secret:
enabled: false
nameOverride: hydra-secrets
aeneasr commented
Another user had a similar problem and the root cause that they were running custom clean up jobs which caused this problem. To me it looks like this is the same problem
aeneasr commented
There's another user indicating that there is something for sure broken. Upstream issue is ory/hydra#3346