bug: hydra v2.0.3 migration fails on MariaDB

jonasbadstuebner commented 1 year ago

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.
[ ] 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

Hydra v2.0.2 automigration fails with

hydra-automigrate Error 1050: Table 'networks' already exists
hydra-automigrate error executing migrations/20150101000001000000_networks.mysql.up.sql, sql: -- Migration generated by the command below; DO NOT EDIT.
hydra-automigrate -- hydra:generate hydra migrate gen
hydra-automigrate CREATE TABLE `networks` (
hydra-automigrate   `id` char(36) NOT NULL,
hydra-automigrate   PRIMARY KEY(`id`),
hydra-automigrate   `created_at` DATETIME NOT NULL,
hydra-automigrate   `updated_at` DATETIME NOT NULL
hydra-automigrate );
[...]

I dropped the table but this did not work, since the automigration creates it twice, I think. When this error is thrown, the table exists again already. I looked into the failing migration but I'm missing the background information if there was any change to the migrations ("last pushed 3 months ago"). Maybe we could add an IF NOT EXISTS ref to the CREATE TABLE statement? This would be an easy fix, but I don't know if this is desired behaviour then. This would give a warning, if the table exists, but would not fail.

Reproducing the bug

Run hydra v1.11.8
Upgrade to hydra v2.0.2 (these are the 2 versions I used, which I expect to not break if I upgrade, since I didn't see a note for that anyhwere and the tables in my case were exactly the same structure etc. in both versions)
Watch automigration break

Relevant log output

hydra-automigrate Error 1050: Table 'networks' already exists
hydra-automigrate error executing migrations/20150101000001000000_networks.mysql.up.sql, sql: -- Migration generated by the command below; DO NOT EDIT.
hydra-automigrate -- hydra:generate hydra migrate gen
hydra-automigrate CREATE TABLE `networks` (
hydra-automigrate   `id` char(36) NOT NULL,
hydra-automigrate   PRIMARY KEY(`id`),
hydra-automigrate   `created_at` DATETIME NOT NULL,
hydra-automigrate   `updated_at` DATETIME NOT NULL
hydra-automigrate );
[...]

Relevant configuration

No response

Version

v2.0.2

On which operating system are you observing this issue?

Linux

In which environment are you deploying?

Kubernetes with Helm

Additional Context

No response

aeneasr commented 1 year ago

Did the table networks exist in your DB prior to upgrading?

jonasbadstuebner commented 1 year ago

It didn't. And I dropped the whole database and tried again, still the same issue. The networks table is created, but right after the migration fails.

aeneasr commented 1 year ago

That sounds like there's an operational problem, for example multiple migrations running at the same time or something.

jonasbadstuebner commented 1 year ago

I had the previous version of hydra running, but this should not break the migration, right? I removed my hydra deployment and will try to deploy the v2.0.2 manually.

jonasbadstuebner commented 1 year ago

This did not change anything. Only the v2.0.2 pod ran the automigration, nothing else was accessing hydras database. Still - this did not work. Can you tell me, how the migrations are done? Are they only taken from the migrations/ folder? What can I do to find the source of this issue?

aeneasr commented 1 year ago

Are you using mariadb?

jonasbadstuebner commented 1 year ago

Yes - and I know that it is not officially supported - but can you tell me where it breaks? Since This execution should work the exact same no matter if I'm using MySQL or MariaDB...right?

jonasbadstuebner commented 1 year ago

If you give me this information, I am going to write docs and a PR to give basic MariaDB-Support. But I cannot do that, if I don't know where it breaks it's neck or why it is not just creating a table.

jonasbadstuebner commented 1 year ago

I found out that RANDOM_BYTES is not existent for MariaDB prior to version 10.10.0. So for the migrations to work on MariaDB, this function would have to be replaced, but would not break the functionality on MySQL. As the author of the new "breaking" migrations wrote here, the stackoverflow thread contains a generic SQL solution, ready to be copy pasted from below. I have the "working parts" of the failing migrations commented out.

-- Create the table
--CREATE TABLE `networks` (
--  `id` char(36) NOT NULL,
--  PRIMARY KEY(`id`),
--  `created_at` DATETIME NOT NULL,
--  `updated_at` DATETIME NOT NULL
--);
-- Insert first network with random ID
INSERT INTO networks (id, created_at, updated_at) VALUES (
(SELECT LOWER(CONCAT(
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), 
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), '-',
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), '-', 
    '4',
    LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'), '-', 
    HEX(FLOOR(RAND() * 4 + 8)), 
    LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'), '-', 
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'),
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'),
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'))))
, '2013-10-07 08:23:19', '2013-10-07 08:23:19');

-- to skip the migration in the future:
INSERT INTO `schema_migration` (`version`, `version_self`) VALUES ('20150101000001000000', '0');

There are more failing migrations, the fixes are similar: 20211004110001000000_change_client_primary_key

-- ALTER TABLE hydra_client CHANGE COLUMN pk pk_deprecated INT UNSIGNED;
-- ALTER TABLE hydra_client ADD COLUMN pk CHAR(36);
-- UUIDv4 generation based on https://stackoverflow.com/a/66868340/12723442
UPDATE hydra_client SET pk = (
SELECT LOWER(CONCAT(
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), 
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), '-',
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), '-', 
    '4',
    LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'), '-', 
    HEX(FLOOR(RAND() * 4 + 8)), 
    LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'), '-', 
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'),
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'),
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0')))
);
-- previously skipped commands (because of failure)
ALTER TABLE hydra_client DROP PRIMARY KEY, ADD PRIMARY KEY (pk);
ALTER TABLE hydra_client ADD KEY (pk_deprecated);
ALTER TABLE hydra_client CHANGE COLUMN pk_deprecated pk_deprecated INT UNSIGNED;

-- to skip the migration in the future:
INSERT INTO `schema_migration` (`version`, `version_self`) VALUES ('20211004110001000000', '0');

20211011000001000000_change_jwk_primary_key

-- ALTER TABLE hydra_jwk CHANGE COLUMN pk pk_deprecated INT UNSIGNED;
-- ALTER TABLE hydra_jwk ADD COLUMN pk CHAR(36);
-- UUIDv4 generation based on https://stackoverflow.com/a/66868340/12723442
UPDATE hydra_jwk SET pk = (
SELECT LOWER(CONCAT(
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), 
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), '-',
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), '-', 
    '4',
    LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'), '-', 
    HEX(FLOOR(RAND() * 4 + 8)), 
    LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'), '-', 
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'),
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'),
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0')))
);
-- previously skipped commands (because of failure)
ALTER TABLE hydra_jwk ALTER pk DROP DEFAULT;
ALTER TABLE hydra_jwk DROP PRIMARY KEY, ADD PRIMARY KEY (pk);
ALTER TABLE hydra_jwk ADD KEY (pk_deprecated);
ALTER TABLE hydra_jwk CHANGE COLUMN pk_deprecated pk_deprecated INT UNSIGNED AUTO_INCREMENT;

-- to skip the migration in the future:
INSERT INTO `schema_migration` (`version`, `version_self`) VALUES ('20211011000001000000', '0');

MariaDB handles JSON differently than MySQL does. So here we need some other workaround: 20220513000001000001_string_slice_json.mysql.up.sql

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

-- to skip the migration in the future:
INSERT INTO `schema_migration` (`version`, `version_self`) VALUES ('20220513000001000001', '0');

20220513000001000007_string_slice_json.mysql.up.sql

UPDATE hydra_oauth2_flow SET requested_scope_json = JSON_VALUE(json_object('tmp',concat('["' ,  REPLACE(requested_scope,'|','","') ,  '"]')),'$.tmp') WHERE requested_scope <> ('[]');
UPDATE hydra_oauth2_flow SET requested_at_audience_json = JSON_VALUE(json_object('tmp',concat('["' ,  REPLACE(requested_at_audience,'|','","') ,  '"]')),'$.tmp') WHERE requested_at_audience <> ('[]');
UPDATE hydra_oauth2_flow SET amr_json = JSON_VALUE(json_object('tmp',concat('["' ,  REPLACE(amr,'|','","') ,  '"]')),'$.tmp') WHERE amr <> ('[]');
UPDATE hydra_oauth2_flow SET granted_scope_json = JSON_VALUE(json_object('tmp',concat('["' ,  REPLACE(granted_scope,'|','","') ,  '"]')),'$.tmp') WHERE granted_scope <> ('[]');
UPDATE hydra_oauth2_flow SET granted_at_audience_json = JSON_VALUE(json_object('tmp',concat('["' ,  REPLACE(granted_at_audience,'|','","') ,  '"]')),'$.tmp') WHERE granted_at_audience <> ('[]');

-- to skip the migration in the future:
INSERT INTO `schema_migration` (`version`, `version_self`) VALUES ('20220513000001000007', '0');

After every block of SQL queries, just restart the deployment and the init-container should break at the next of the blocks I provided. Check that with kubectl logs -f -n ory deployments/hydra -c hydra-automigrate. After all of that, you should see Successfully applied migrations! and Hydra is starting.

jonasbadstuebner commented 1 year ago

I close this, since MariaDB is not officially supported. And if needed, I open and link more issues regarding MariaDB in the future, until maybe I open a PR to support MariaDB. I would volunteer to own that part of the code then.

dan2kx commented 1 year ago

I'm getting this exact issue in mysql 8.0 upgrading from 1.10.6 to 2.0.3 in my dev build

ory / hydra