SQL Migration error #3391
Description
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 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
- 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) - run
./hydra migrate sql "<DSN>" - Accept migration plan.
- 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 printedRelevant 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