https://www.ory.com/ logo
Join Slack
Powered by
Hi, my team is attempting to upgrade our version o...
# talk-hydra
l
Hi, my team is attempting to upgrade our version of Hydra from 1.9.1 to 2.0.3. We have 3 environments: • Integration • Staging • Production We have been testing migration in turn against each environment's database, starting with integration.
The integration migration went off successfully, but during the staging environment's migration, we got hit with this error:
Copy code
ERROR: unsupported Unicode escape sequence (SQLSTATE 22P05)
error executing migrations/20211019000001000001_merge_authentication_request_tables.postgres.up.sql, sql: -- Migration generated by the command below; DO NOT EDIT.
Followed by this output:
Copy code
INSERT INTO hydra_oauth2_flow (
    state,
    login_challenge,
    requested_scope,
    login_verifier,
    login_csrf,
    subject,
    request_url,
    login_skip,
    client_id,
    requested_at,
    login_initialized_at,
    oidc_context,
    login_session_id,
    requested_at_audience,

    login_remember,
    login_remember_for,
    login_error,
    acr,
    login_authenticated_at,
    login_was_used,
this error should never be printed
    forced_subject_identifier,
    context,
    amr,

    consent_challenge_id,
    consent_verifier,
    consent_skip,
    consent_csrf,

    granted_scope,
    consent_remember,
    consent_remember_for,
    consent_error,
    session_access_token,
    session_id_token,
    consent_was_used,
    granted_at_audience,
    consent_handled_at
) SELECT
    case
        when hydra_oauth2_authentication_request_handled.error IS NOT NULL then 128
        when hydra_oauth2_consent_request_handled.error IS NOT NULL then 129
        when hydra_oauth2_consent_request_handled.was_used = true then 6
        when hydra_oauth2_consent_request_handled.challenge IS NOT NULL then 5
        when hydra_oauth2_consent_request.challenge IS NOT NULL then 4
        when hydra_oauth2_authentication_request_handled.was_used = true then 3
        when hydra_oauth2_authentication_request_handled.challenge IS NOT NULL then 2
        else 1
    end,
    hydra_oauth2_authentication_request.challenge,
    hydra_oauth2_authentication_request.requested_scope,
    hydra_oauth2_authentication_request.verifier,
    hydra_oauth2_authentication_request.csrf,
    hydra_oauth2_authentication_request.subject,
    hydra_oauth2_authentication_request.request_url,
    hydra_oauth2_authentication_request.skip,
    hydra_oauth2_authentication_request.client_id,
    hydra_oauth2_authentication_request.requested_at,
    hydra_oauth2_authentication_request.authenticated_at,
    cast(coalesce(hydra_oauth2_authentication_request.oidc_context, '{}') as jsonb),
    hydra_oauth2_authentication_request.login_session_id,
    hydra_oauth2_authentication_request.requested_at_audience,

    coalesce(hydra_oauth2_authentication_request_handled.remember, false),
    coalesce(hydra_oauth2_authentication_request_handled.remember_for, 0),
    hydra_oauth2_authentication_request_handled.error,
    coalesce(hydra_oauth2_authentication_request_handled.acr, ''),
    hydra_oauth2_authentication_request_handled.authenticated_at,
    coalesce(hydra_oauth2_authentication_request_handled.was_used, false),
    coalesce(hydra_oauth2_consent_request.forced_subject_identifier, hydra_oauth2_authentication_request_handled.forced_subject_identifier, ''),
    cast(coalesce(hydra_oauth2_authentication_request_handled.context, '{}') as jsonb),
    coalesce(hydra_oauth2_authentication_request_handled.amr, ''),

    hydra_oauth2_consent_request.challenge,
    hydra_oauth2_consent_request.verifier,
    coalesce(hydra_oauth2_consent_request.skip, false),
    hydra_oauth2_consent_request.csrf,

    coalesce(hydra_oauth2_consent_request_handled.granted_scope, '[]'),
    coalesce(hydra_oauth2_consent_request_handled.remember, false),
    coalesce(hydra_oauth2_consent_request_handled.remember_for, 0),
    hydra_oauth2_consent_request_handled.error,
    cast(coalesce(hydra_oauth2_consent_request_handled.session_access_token, '{}') as jsonb),
    cast(coalesce(hydra_oauth2_consent_request_handled.session_id_token, '{}') as jsonb),
    coalesce(hydra_oauth2_consent_request_handled.was_used, false),
    coalesce(hydra_oauth2_consent_request_handled.granted_at_audience, '[]'),
    hydra_oauth2_consent_request_handled.handled_at
FROM hydra_oauth2_authentication_request
LEFT JOIN hydra_oauth2_authentication_request_handled
ON hydra_oauth2_authentication_request.challenge = hydra_oauth2_authentication_request_handled.challenge
LEFT JOIN hydra_oauth2_consent_request
ON hydra_oauth2_authentication_request.challenge = hydra_oauth2_consent_request.login_challenge
LEFT JOIN hydra_oauth2_consent_request_handled
ON hydra_oauth2_consent_request.challenge = hydra_oauth2_consent_request_handled.challenge;

UPDATE hydra_oauth2_access AS t1
SET challenge_id = NULL
WHERE challenge_id IS NOT NULL
  AND NOT exists(SELECT NULL FROM hydra_oauth2_flow t2 WHERE t1.challenge_id = t2.consent_challenge_id);

UPDATE hydra_oauth2_code AS t1
SET challenge_id = NULL
WHERE challenge_id IS NOT NULL
  AND NOT exists(SELECT NULL FROM hydra_oauth2_flow t2 WHERE t1.challenge_id = t2.consent_challenge_id);

UPDATE hydra_oauth2_oidc AS t1
SET challenge_id = NULL
WHERE challenge_id IS NOT NULL
  AND NOT exists(SELECT NULL FROM hydra_oauth2_flow t2 WHERE t1.challenge_id = t2.consent_challenge_id);

UPDATE hydra_oauth2_refresh AS t1
SET challenge_id = NULL
WHERE challenge_id IS NOT NULL
  AND NOT exists(SELECT NULL FROM hydra_oauth2_flow t2 WHERE t1.challenge_id = t2.consent_challenge_id);

UPDATE hydra_oauth2_pkce AS t1
SET challenge_id = NULL
WHERE challenge_id IS NOT NULL
  AND NOT exists(SELECT NULL FROM hydra_oauth2_flow t2 WHERE t1.challenge_id = t2.consent_challenge_id);

<http://github.com/ory/x/popx.NewMigrationBox.func1.1|github.com/ory/x/popx.NewMigrationBox.func1.1>
	/go/pkg/mod/github.com/ory/[email protected]/popx/migration_box.go:161
<http://github.com/ory/x/popx.Migration.Run|github.com/ory/x/popx.Migration.Run>
	/go/pkg/mod/github.com/ory/[email protected]/popx/migration_info.go:37
<http://github.com/ory/x/popx.(*Migrator).UpTo.func1.2|github.com/ory/x/popx.(*Migrator).UpTo.func1.2>
	/go/pkg/mod/github.com/ory/[email protected]/popx/migrator.go:149
<http://github.com/ory/x/popx.(*Migrator).isolatedTransaction|github.com/ory/x/popx.(*Migrator).isolatedTransaction>
	/go/pkg/mod/github.com/ory/[email protected]/popx/migrator.go:323
<http://github.com/ory/x/popx.(*Migrator).UpTo.func1|github.com/ory/x/popx.(*Migrator).UpTo.func1>
	/go/pkg/mod/github.com/ory/[email protected]/popx/migrator.go:148
<http://github.com/ory/x/popx.(*Migrator).exec|github.com/ory/x/popx.(*Migrator).exec>
	/go/pkg/mod/github.com/ory/[email protected]/popx/migrator.go:567
<http://github.com/ory/x/popx.(*Migrator).UpTo|github.com/ory/x/popx.(*Migrator).UpTo>
	/go/pkg/mod/github.com/ory/[email protected]/popx/migrator.go:102
<http://github.com/ory/x/popx.(*Migrator).Up|github.com/ory/x/popx.(*Migrator).Up>
	/go/pkg/mod/github.com/ory/[email protected]/popx/migrator.go:88
<http://github.com/ory/hydra/persistence/sql.(*Persister).MigrateUp|github.com/ory/hydra/persistence/sql.(*Persister).MigrateUp>
	/project/persistence/sql/persister_migration.go:51
<http://github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL|github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL>
	/project/cmd/cli/handler_migrate.go:344
<http://github.com/spf13/cobra.(*Command).execute|github.com/spf13/cobra.(*Command).execute>
	/go/pkg/mod/github.com/spf13/[email protected]/command.go:916
<http://github.com/spf13/cobra.(*Command).ExecuteC|github.com/spf13/cobra.(*Command).ExecuteC>
	/go/pkg/mod/github.com/spf13/[email protected]/command.go:1044
<http://github.com/spf13/cobra.(*Command).Execute|github.com/spf13/cobra.(*Command).Execute>
	/go/pkg/mod/github.com/spf13/[email protected]/command.go:968
<http://github.com/ory/hydra/cmd.Execute|github.com/ory/hydra/cmd.Execute>
	/project/cmd/root.go:101
main.main
	/project/main.go:14
runtime.main
	/usr/local/go/src/runtime/proc.go:250
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1594
DB engine is PostGres 13.5.
l
have you considered upgrading from 1.9 to 1.10 or 1.11 and then 2.0?
l
We could consider it, are there no other options? The upgrade did go off without a hitch when we tried it another upper environment. We also had successful migrations from our local dev environments.
l
I'm honestly not sure. we're looking at making the upgrade to 2.0 as well and I've been tempted to migrate to 1.11 first, running the janitor process to clean up unneeded data, then continuing to 2.0 🤷
h
@little-fireman-5336 have you seen any issues with existing access tokens not working in 2.0? we had that in one project, and very wary to upgrade another critical instance
l
Figured it out by running the query that failed on my own, which gave more context for the error. Turned out to be some corrupt data left behind by a pen test. Once I ran down all the places with the bad data and fixed it, I was able to get through the 1.91 to 2.0.3 migration.
4 Views
Open in Slack
PreviousNext
Powered by