SQL Migration error

[FlamingPaw]

Preflight checklist

• [X] I could not find a solution in the existing issues, docs, nor discussions.
• [X] I agree to follow this project's Code of Conduct.
• [X] I have read and am following this repository's Contribution Guidelines.
• [X] 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 am trying to upgrade from v1.11.10 to v2.0.3, and the sql migration tool is failing with errors from the MariaDB server.

Linux OS: Oracle Linux Server 9.1 MariaDB: 10.10.2-MariaDB MariaDB Server

Could not apply migrations: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json) WHERE redirect_uris <> ''; UPDATE hydra_client SET grant_types_json = ...' at line 4 error executing migrations/20220513000001000001_string_slice_json.mysql.up.sql, sql: -- Migration generated by the command below; DO NOT EDIT

Reproducing the bug

1. Download latest version of hydra over top of current binary. (bash <(curl https://raw.githubusercontent.com/ory/meta/master/install.sh) -d -b . hydra v2.0.3)
2. run ./hydra migrate sql "<DSN>"
3. Accept migration plan.
4. Fails with error.

Relevant log output

INFO[2022-12-20T14:09:26-08:00] No tracer configured - skipping tracing setup  audience=application service_name=Ory Hydra service_version=v2.0.3
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               Applied   
20211019000001000003   merge_authentication_request_tables               Applied   
20211019000001000004   merge_authentication_request_tables               Applied   
20211019000001000005   merge_authentication_request_tables               Applied   
20211019000001000006   merge_authentication_request_tables               Applied   
20211019000001000007   merge_authentication_request_tables               Applied   
20211019000001000008   merge_authentication_request_tables               Applied   
20211019000001000009   merge_authentication_request_tables               Applied   
20211019000001000010   merge_authentication_request_tables               Applied   
20211019000001000011   merge_authentication_request_tables               Applied   
20211019000001000012   merge_authentication_request_tables               Applied   
20211019000001000013   merge_authentication_request_tables               Applied   
20211019000001000014   merge_authentication_request_tables               Applied   
20211019000001000015   merge_authentication_request_tables               Applied   
20211019000001000016   merge_authentication_request_tables               Applied   
20211019000001000017   merge_authentication_request_tables               Applied   
20211019000001000018   merge_authentication_request_tables               Applied   
20211019000001000019   merge_authentication_request_tables               Applied   
20211019000001000020   merge_authentication_request_tables               Applied   
20211019000001000021   merge_authentication_request_tables               Applied   
20211019000001000022   merge_authentication_request_tables               Applied   
20211019000001000023   merge_authentication_request_tables               Applied   
20211019000001000024   merge_authentication_request_tables               Applied   
20211019000001000025   merge_authentication_request_tables               Applied   
20211019000001000026   merge_authentication_request_tables               Applied   
20211019000001000027   merge_authentication_request_tables               Applied   
20211019000001000028   merge_authentication_request_tables               Applied   
20211019000001000029   merge_authentication_request_tables               Applied   
20211019000001000030   merge_authentication_request_tables               Applied   
20211019000001000031   merge_authentication_request_tables               Applied   
20211019000001000032   merge_authentication_request_tables               Applied   
20211019000001000033   merge_authentication_request_tables               Applied   
20211019000001000034   merge_authentication_request_tables               Applied   
20211019000001000035   merge_authentication_request_tables               Applied   
20211019000001000036   merge_authentication_request_tables               Applied   
20211019000001000037   merge_authentication_request_tables               Applied   
20211019000001000038   merge_authentication_request_tables               Applied   
20211019000001000039   merge_authentication_request_tables               Applied   
20211226155900000000   grant_jwk_bearer                                  Applied   
20211226156000000000   dynamic_registration                              Applied   
20220210000001000000   nid                                               Applied   
20220210000001000001   nid                                               Applied   
20220210000001000002   nid                                               Applied   
20220210000001000003   nid                                               Applied   
20220210000001000004   nid                                               Applied   
20220210000001000005   nid                                               Applied   
20220210000001000006   nid                                               Applied   
20220210000001000007   nid                                               Applied   
20220210000001000008   nid                                               Applied   
20220210000001000009   nid                                               Applied   
20220210000001000010   nid                                               Applied   
20220210000001000011   nid                                               Applied   
20220210000001000012   nid                                               Applied   
20220210000001000013   nid                                               Applied   
20220210000001000014   nid                                               Applied   
20220210000001000015   nid                                               Applied   
20220210000001000016   nid                                               Applied   
20220210000001000017   nid                                               Applied   
20220210000001000018   nid                                               Applied   
20220210000001000019   nid                                               Applied   
20220210000001000020   nid                                               Applied   
20220210000001000021   nid                                               Applied   
20220210000001000022   nid                                               Applied   
20220210000001000023   nid                                               Applied   
20220210000001000024   nid                                               Applied   
20220210000001000025   nid                                               Applied   
20220210000001000026   nid                                               Applied   
20220210000001000027   nid                                               Applied   
20220210000001000028   nid                                               Applied   
20220210000001000029   nid                                               Applied   
20220210000001000030   nid                                               Applied   
20220210000001000031   nid                                               Applied   
20220210000001000032   nid                                               Applied   
20220210000001000033   nid                                               Applied   
20220210000001000034   nid                                               Applied   
20220210000001000035   nid                                               Applied   
20220210000001000036   nid                                               Applied   
20220210000001000037   nid                                               Applied   
20220210000001000038   nid                                               Applied   
20220210000001000039   nid                                               Applied   
20220210000001000040   nid                                               Applied   
20220210000001000041   nid                                               Applied   
20220210000001000042   nid                                               Applied   
20220210000001000043   nid                                               Applied   
20220210000001000044   nid                                               Applied   
20220210000001000045   nid                                               Applied   
20220210000001000046   nid                                               Applied   
20220210000001000047   nid                                               Applied   
20220210000001000048   nid                                               Applied   
20220210000001000049   nid                                               Applied   
20220210000001000050   nid                                               Applied   
20220210000001000051   nid                                               Applied   
20220210000001000052   nid                                               Applied   
20220210000001000053   nid                                               Applied   
20220210000001000054   nid                                               Applied   
20220210000001000055   nid                                               Applied   
20220210000001000056   nid                                               Applied   
20220210000001000057   nid                                               Applied   
20220210000001000058   nid                                               Applied   
20220210000001000059   nid                                               Applied   
20220210000001000060   nid                                               Applied   
20220210000001000061   nid                                               Applied   
20220210000001000062   nid                                               Applied   
20220210000001000063   nid                                               Applied   
20220210000001000064   nid                                               Applied   
20220210000001000065   nid                                               Applied   
20220210000001000066   nid                                               Applied   
20220210000001000067   nid                                               Applied   
20220210000001000068   nid                                               Applied   
20220210000001000069   nid                                               Applied   
20220210000001000070   nid                                               Applied   
20220210000001000071   nid                                               Applied   
20220210000001000072   nid                                               Applied   
20220210000001000073   nid                                               Applied   
20220210000001000074   nid                                               Applied   
20220210000001000075   nid                                               Applied   
20220210000001000076   nid                                               Applied   
20220210000001000077   nid                                               Applied   
20220210000001000078   nid                                               Applied   
20220210000001000079   nid                                               Applied   
20220328111500000000   support_any_subject_trusts                        Applied   
20220513000001000000   string_slice_json                                 Applied   
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   
20221109000010000000   fix_foreign_key                                   Pending   
20221109000010000001   fix_foreign_key                                   Pending   
To skip the next question use flag --yes (at your own risk).
Do you wish to execute this migration plan? [y/n]: y
Could not apply migrations:
Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json) WHERE redirect_uris <> '';
UPDATE hydra_client SET grant_types_json =  ...' at line 4
error executing migrations/20220513000001000001_string_slice_json.mysql.up.sql, sql: -- Migration generated by the command below; DO NOT EDIT.
-- hydra:generate hydra migrate gen

UPDATE hydra_client SET redirect_uris_json =  cast(concat('["' ,  REPLACE(redirect_uris,'|','","') ,  '"]') as json) WHERE redirect_uris <> '';
UPDATE hydra_client SET grant_types_json =  cast(concat('["' ,  REPLACE(grant_types,'|','","') ,  '"]') as json) WHERE grant_types <> '';
UPDATE hydra_client SET response_types_json =  cast(concat('["' ,  REPLACE(response_types,'|','","') ,  '"]') as json) WHERE response_types <> '';
UPDATE hydra_client SET audience_json =  cast(concat('["' ,  REPLACE(audience,'|','","') ,  '"]') as json) WHERE audience <> '';
UPDATE hydra_client SET allowed_cors_origins_json =  cast(concat('["' ,  REPLACE(allowed_cors_origins,'|','","') ,  '"]') as json) WHERE allowed_cors_origins <> '';
UPDATE hydra_client SET contacts_json =  cast(concat('["' ,  REPLACE(contacts,'|','","') ,  '"]') as json) WHERE contacts <> '';
UPDATE hydra_client SET request_uris_json =  cast(concat('["' ,  REPLACE(request_uris,'|','","') ,  '"]') as json) WHERE request_uris <> '';
UPDATE hydra_client SET post_logout_redirect_uris_json =  cast(concat('["' ,  REPLACE(post_logout_redirect_uris,'|','","') ,  '"]') as json) WHERE post_logout_redirect_uris <> '';

github.com/ory/x/popx.NewMigrationBox.func1.1
        /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migration_box.go:161
github.com/ory/x/popx.Migration.Run
        /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migration_info.go:37
github.com/ory/x/popx.(*Migrator).UpTo.func1.2
        /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:149
github.com/ory/x/popx.(*Migrator).isolatedTransaction
        /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:323
github.com/ory/x/popx.(*Migrator).UpTo.func1
        /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:148
github.com/ory/x/popx.(*Migrator).exec
        /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:567
github.com/ory/x/popx.(*Migrator).UpTo
        /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:102
github.com/ory/x/popx.(*Migrator).Up
        /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:88
github.com/ory/hydra/persistence/sql.(*Persister).MigrateUp
        /project/persistence/sql/persister_migration.go:51
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
        /project/cmd/cli/handler_migrate.go:344
github.com/spf13/cobra.(*Command).execute
        /go/pkg/mod/github.com/spf13/cobra@v1.6.1/command.go:916
github.com/spf13/cobra.(*Command).ExecuteC
        /go/pkg/mod/github.com/spf13/cobra@v1.6.1/command.go:1044
github.com/spf13/cobra.(*Command).Execute
        /go/pkg/mod/github.com/spf13/cobra@v1.6.1/command.go:968
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
this error should never be printed

Relevant configuration

No response

Version

v1.11.10

On which operating system are you observing this issue?

Linux

In which environment are you deploying?

Binary

Additional Context

No response

[aeneasr]

MariaDB is not officially supported by Ory Hydra. Supported databases are MySQL 8+, CockroachDB, PostgreSQL, SQLite

[FlamingPaw]

Using 8.0.31 - MySQL Community Server - GPL as the database server still fails to run the sql migration, be it a slightly different error.

INFO[2022-12-21T13:00:17-08:00] No tracer configured - skipping tracing setup  audience=application service_name=Ory Hydra service_version=v2.0.3
The following migration is planned:
Version                Name                                              Status    
20150101000001000000   networks                                          Pending   
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                         Pending   
20211004110002000000   change_client_primary_key                         Pending   
20211004110003000000   change_client_primary_key                         Pending   
20211011000001000000   change_jwk_primary_key                            Pending   
20211011000002000000   change_jwk_primary_key                            Pending   
20211011000003000000   change_jwk_primary_key                            Pending   
20211019000001000000   merge_authentication_request_tables               Pending   
20211019000001000001   merge_authentication_request_tables               Pending   
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   
20221109000010000000   fix_foreign_key                                   Pending   
20221109000010000001   fix_foreign_key                                   Pending   
To skip the next question use flag --yes (at your own risk).
Do you wish to execute this migration plan? [y/n]: y
Could not apply migrations:
Error 1832: Cannot change column 'nid': used in a foreign key constraint 'hydra_client_nid_fk_idx'
error executing migrations/20220210000001000002_nid.mysql.up.sql, sql: -- Migration generated by the command below; DO NOT EDIT.
-- hydra:generate hydra migrate gen

UPDATE hydra_client SET nid = (SELECT id FROM networks LIMIT 1);
ALTER TABLE hydra_client MODIFY `nid` char(36) NOT NULL;

github.com/ory/x/popx.NewMigrationBox.func1.1
    /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migration_box.go:161
github.com/ory/x/popx.Migration.Run
    /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migration_info.go:37
github.com/ory/x/popx.(*Migrator).UpTo.func1.2
    /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:149
github.com/ory/x/popx.(*Migrator).isolatedTransaction
    /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:323
github.com/ory/x/popx.(*Migrator).UpTo.func1
    /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:148
github.com/ory/x/popx.(*Migrator).exec
    /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:567
github.com/ory/x/popx.(*Migrator).UpTo
    /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:102
github.com/ory/x/popx.(*Migrator).Up
    /go/pkg/mod/github.com/ory/x@v0.0.520/popx/migrator.go:88
github.com/ory/hydra/persistence/sql.(*Persister).MigrateUp
    /project/persistence/sql/persister_migration.go:51
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
    /project/cmd/cli/handler_migrate.go:344
github.com/spf13/cobra.(*Command).execute
    /go/pkg/mod/github.com/spf13/cobra@v1.6.1/command.go:916
github.com/spf13/cobra.(*Command).ExecuteC
    /go/pkg/mod/github.com/spf13/cobra@v1.6.1/command.go:1044
github.com/spf13/cobra.(*Command).Execute
    /go/pkg/mod/github.com/spf13/cobra@v1.6.1/command.go:968
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
this error should never be printed

[FlamingPaw]

Moving back to MariaDB, I was able to run the manual migrations specified over at https://github.com/ory/hydra/issues/3387 and that worked.

[aeneasr]

The error log with the nid fk constraint looks like a different problem. Do you have a reproducible case for that?

[rauanmayemir]

I have this issue on PostgreSQL.

I used a single database for both hydra and kratos, so now that hydra also decided to have a networks table, it's clashing with kratos's table.

[aeneasr]

Yes, you will need to use separate databases or schemas if you run all services on the same database instance.

[aeneasr]

Closing as per https://github.com/ory/hydra/issues/3387#issuecomment-1343961568