search

ory / k8s

Kubernetes Helm Charts for the ORY ecosystem.
https://k8s.ory.sh/helm
Apache License 2.0
335 stars 258 forks source link

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" #537

Closed zagr0 closed 2 years ago

zagr0 commented 2 years ago

Preflight checklist

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

  1. do helm upgrade ory/hydra from 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 2 years ago

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 2 years ago

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 2 years ago

There's another user indicating that there is something for sure broken. Upstream issue is https://github.com/ory/hydra/issues/3346