Migration fails for Vitess/MySQL Database

[ignas-kucinas]

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.
• I have joined the Ory Community Slack.
• I am signed up to the Ory Security Patch Newsletter.

Ory Network Project

No response

Describe the bug

Migration 20211019000001000000_merge_authentication_request_tables.mysql.up.sql fail's, when running hydra migrate sql -e --yes on Vitess DB with error Error 1101 (42000): target: hydra_sb_main.0.master: vttablet: BLOB, TEXT, GEOMETRY or JSON column 'requested_scope' can't have a default value (errno 1101) (sqlstate 42000)

Using:
Vitess - v11
MySQL - v5.7

Reproducing the bug

1. Deploy new pod running on oryd/hydra:v2.3.0 image
2. Set DSN environment variable to connect to Vitess/MySQL Database
3. Run hydra migrate sql -e --yes

Relevant log output

------------ ERROR ------------
Could not apply migrations:
Error 1101 (42000): target: hydra_sb_main.0.master: vttablet: BLOB, TEXT, GEOMETRY or JSON column 'requested_scope' can't have a default value (errno 1101) (sqlstate 42000) (CallerID: hydra_sb_main): Sql: "create table hydra_oauth2_flow (\n\tlogin_challenge varchar(40) not null,\n\trequested_scope text not null default '[]',\n\tlogin_verifier varchar(40) not null,\n\tlogin_csrf varchar(40) not null,\n\tsubject varchar(255) not null,\n\trequest_url text not null,\n\tlogin_skip tinyint(1) not null,\n\tclient_id varchar(255) not null,\n\trequested_at timestamp not null default current_timestamp(),\n\tlogin_initialized_at timestamp null default null,\n\toidc_context json not null default '{}',\n\tlogin_session_id varchar(40) null,\n\trequested_at_audience text null default '[]',\n\tstate smallint not null,\n\tlogin_remember tinyint(1) not null default false,\n\tlogin_remember_for int(11) not null,\n\tlogin_error text null,\n\tacr text not null default '',\n\tlogin_authenticated_at timestamp null default null,\n\tlogin_was_used tinyint(1) not null default false,\n\tforced_subject_identifier varchar(255) not null default '',\n\tcontext json not null default '{}',\n\tamr text not null default '[]',\n\tconsent_challenge_id varchar(40) null,\n\tconsent_skip tinyint(1) not null default 0,\n\tconsent_verifier varchar(40) null,\n\tconsent_csrf varchar(40) null,\n\tgranted_scope text not null default '[]',\n\tgranted_at_audience text not null default '[]',\n\tconsent_remember tinyint(1) not null default false,\n\tconsent_remember_for int(11) null,\n\tconsent_handled_at timestamp null default null,\n\tconsent_error TEXT null,\n\tsession_access_token json not null default '{}',\n\tsession_id_token json not null default '{}',\n\tconsent_was_used tinyint(1),\n\tPRIMARY KEY (login_challenge),\n\tUNIQUE KEY hydra_oauth2_flow_login_verifier_idx (login_verifier),\n\tKEY hydra_oauth2_flow_cid_idx (client_id),\n\tKEY hydra_oauth2_flow_sub_idx (subject),\n\tKEY hydra_oauth2_flow_login_session_id_idx (login_session_id),\n\tUNIQUE KEY hydra_oauth2_flow_consent_challenge_idx (consent_challenge_id),\n\tKEY hydra_oauth2_flow_consent_verifier_idx (consent_verifier),\n\tKEY hydra_oauth2_flow_client_id_subject_idx (client_id, subject),\n\tconstraint hydra_oauth2_flow_client_id_fk foreign key (client_id) references hydra_client (id) on delete cascade,\n\tconstraint hydra_oauth2_flow_login_session_id_fk foreign key (login_session_id) references hydra_oauth2_authentication_session (id) on delete cascade\n)", BindVars: {}
error executing migrations/20211019000001000000_merge_authentication_request_tables.mysql.up.sql, sql: -- Migration generated by the command below; DO NOT EDIT.
-- hydra:generate hydra migrate gen
CREATE TABLE hydra_oauth2_flow
(
    `login_challenge` varchar(40) NOT NULL,
    `requested_scope` text NOT NULL DEFAULT ('[]'),
    `login_verifier` varchar(40) NOT NULL,
    `login_csrf` varchar(40) NOT NULL,
    `subject` varchar(255) NOT NULL,
    `request_url` text NOT NULL,
    `login_skip` tinyint(1) NOT NULL,
    `client_id` varchar(255) NOT NULL,
    `requested_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `login_initialized_at` timestamp NULL DEFAULT NULL,
    `oidc_context` json NOT NULL DEFAULT (('{}')),
    `login_session_id` varchar(40) NULL,
    `requested_at_audience` text NULL DEFAULT ('[]'),
    `state` smallint NOT NULL,
    `login_remember` tinyint(1) NOT NULL DEFAULT false,
    `login_remember_for` int(11) NOT NULL,
    `login_error` text NULL,
    `acr` text  NOT NULL DEFAULT (''),
    `login_authenticated_at` timestamp NULL DEFAULT NULL,
    `login_was_used` tinyint(1) NOT NULL DEFAULT false,
    `forced_subject_identifier` varchar(255) NOT NULL DEFAULT '',
    `context` json NOT NULL DEFAULT ('{}'),
    `amr` text NOT NULL DEFAULT ('[]'),
    `consent_challenge_id` varchar(40) NULL,
    `consent_skip` tinyint(1) NOT NULL DEFAULT 0,
    `consent_verifier` varchar(40) NULL,
    `consent_csrf` varchar(40) NULL,
    `granted_scope` text NOT NULL DEFAULT ('[]'),
    `granted_at_audience` text NOT NULL DEFAULT ('[]'),
    `consent_remember` tinyint(1) NOT NULL DEFAULT false,
    `consent_remember_for` int(11) NULL,
    `consent_handled_at` timestamp NULL DEFAULT NULL,
    `consent_error` TEXT NULL,
    `session_access_token` json DEFAULT ('{}') NOT NULL,
    `session_id_token` json DEFAULT ('{}') NOT NULL,
    `consent_was_used` tinyint(1),
    PRIMARY KEY (`login_challenge`),
    UNIQUE KEY `hydra_oauth2_flow_login_verifier_idx` (`login_verifier`),
    KEY `hydra_oauth2_flow_cid_idx` (`client_id`),
    KEY `hydra_oauth2_flow_sub_idx` (`subject`),
    KEY `hydra_oauth2_flow_login_session_id_idx` (`login_session_id`),
    CONSTRAINT `hydra_oauth2_flow_client_id_fk` FOREIGN KEY (`client_id`) REFERENCES `hydra_client` (`id`) ON DELETE CASCADE,
    CONSTRAINT `hydra_oauth2_flow_login_session_id_fk` FOREIGN KEY (`login_session_id`) REFERENCES `hydra_oauth2_authentication_session` (`id`) ON DELETE CASCADE,
    UNIQUE KEY `hydra_oauth2_flow_consent_challenge_idx` (`consent_challenge_id`),
    KEY `hydra_oauth2_flow_consent_verifier_idx` (`consent_verifier`),
    KEY `hydra_oauth2_flow_client_id_subject_idx` (`client_id`,`subject`)
);
ALTER TABLE hydra_oauth2_flow ADD CONSTRAINT hydra_oauth2_flow_chk CHECK (
      state = 128 OR
      state = 129 OR
      state = 1 OR
      (state = 2 AND (
          login_remember IS NOT NULL AND
          login_remember_for IS NOT NULL AND
          login_error IS NOT NULL AND
          acr IS NOT NULL AND
          login_was_used IS NOT NULL AND
          context IS NOT NULL AND
          amr IS NOT NULL
        )) OR
      (state = 3 AND (
          login_remember IS NOT NULL AND
          login_remember_for IS NOT NULL AND
          login_error IS NOT NULL AND
          acr IS NOT NULL AND
          login_was_used IS NOT NULL AND
          context IS NOT NULL AND
          amr IS NOT NULL
        )) OR
      (state = 4 AND (
          login_remember IS NOT NULL AND
          login_remember_for IS NOT NULL AND
          login_error IS NOT NULL AND
          acr IS NOT NULL AND
          login_was_used IS NOT NULL AND
          context IS NOT NULL AND
          amr IS NOT NULL AND
          consent_challenge_id IS NOT NULL AND
          consent_verifier IS NOT NULL AND
          consent_skip IS NOT NULL AND
          consent_csrf IS NOT NULL
        )) OR
      (state = 5 AND (
          login_remember IS NOT NULL AND
          login_remember_for IS NOT NULL AND
          login_error IS NOT NULL AND
          acr IS NOT NULL AND
          login_was_used IS NOT NULL AND
          context IS NOT NULL AND
          amr IS NOT NULL AND
          consent_challenge_id IS NOT NULL AND
          consent_verifier IS NOT NULL AND
          consent_skip IS NOT NULL AND
          consent_csrf IS NOT NULL
        )) OR
      (state = 6 AND (
          login_remember IS NOT NULL AND
          login_remember_for IS NOT NULL AND
          login_error IS NOT NULL AND
          acr IS NOT NULL AND
          login_was_used IS NOT NULL AND
          context IS NOT NULL AND
          amr IS NOT NULL AND
          consent_challenge_id IS NOT NULL AND
          consent_verifier IS NOT NULL AND
          consent_skip IS NOT NULL AND
          consent_csrf IS NOT NULL AND
          granted_scope IS NOT NULL AND
          consent_remember IS NOT NULL AND
          consent_remember_for IS NOT NULL AND
          consent_error IS NOT NULL AND
          session_access_token IS NOT NULL AND
          session_id_token IS NOT NULL AND
          consent_was_used IS NOT NULL
        ))
  );

Relevant configuration

replicaCount: 1
image:
  repository: container-image-builds/hydra
  tag: v2.3.0-1-7cde808
environment:
    LOG_FORMAT: json
    LOG_LEVEL: info
    URLS_SELF_ISSUER: ********

containerPort: 4444
additionalPorts:
  - name: admin
    containerPort: 4445
    servicePort: 4445
    protocol: TCP
networkPolicy: AllowExternal
service:
  type: ClusterIP
  port: 4444
containerCmd: ["sh"]
containerArgs:
  - -c
  - >-
    . /vault/secrets/secret-file && # loads required credentials to connect to VITESS DB
    export DSN="mysql://${VITESS_DB_USER}:${VITESS_DB_PASSWORD}@tcp(${VITESS_DB_HOST}:${VITESS_DB_PORT})/${VITESS_DB_MAIN}?parseTime=true" &&
    hydra migrate sql -e --yes &&
    hydra serve all --sqa-opt-out

Version

v2.3.0

On which operating system are you observing this issue?

Linux

In which environment are you deploying?

Kubernetes with Helm

Additional Context

No response

[aeneasr]

MySQL 5.7 is quite old and not supported, I think it‘s 5.8 or 8.0. Vitess is not supported. You can find what’s supported in the README