MariaDB - Automigration fails

[jonasbadstuebner]

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

I have tried to set type to both "job" and "initContainer" in a newly installed cluster with an empty MariaDB-database. The error goes away if I add CURRENT_TIMESTAMP as default value for both updated_at and created_at in selfservice_profile_management_request_methods table on kratos database.

ALTER TABLE `selfservice_profile_management_request_methods` ALTER COLUMN `created_at` SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `selfservice_profile_management_request_methods` ALTER COLUMN `updated_at` SET DEFAULT CURRENT_TIMESTAMP;

Reproducing the bug

1. Install a new k8s cluster
2. Bring up a new MariaDB database
3. Install kratos with Helm chart v0.26.2
4. Set the kratos.automigration.enabled: true
5. (optional) Set the kratos.automigration.type: "initContainer"
6. Watch the logs of kratos-automigrate

Relevant log output

kratos-automigrate An error occurred while connecting to SQL: error executing migrations/sql/20200317160354000002_create_profile_request_forms.mysql.up.sql, sql: INSERT INTO selfservice_profile_management_request_methods (id, method, selfservice_profile_management_request_id, config) SELECT id, 'traits', id, form FROM selfservice_profile_management_requests;: Error 1364: Field 'created_at' doesn't have a default value

Relevant configuration

kratos:
  kratos:
    automigration:
      enabled: true
      type: "initContainer"

Version

v0.26.2

On which operating system are you observing this issue?

Linux

In which environment are you deploying?

Kubernetes with Helm

Additional Context

No response

[Demonsthere]

Hi there! This looks strange, as we use the migrations within the CI in this repo, as well as other use cases. I haven't use it with MariaDB though. Did you check if this is related to only this DB type?

Judging from our official docs mariaDB is not supported as a first class provider, and we don't have out of the box support for it 😞

[jonasbadstuebner]

Hey @Demonsthere ,

I updated the title of the issue, so others having this issue, could find it easier. It's probably not worth the effort to investigate too much further into this, as long as there are not thousands of people running into this issue. The fix I proposed is easy to execute on the database and no more issues occurred for me.

I did not test if this is only related to MariaDB. It should, however, be very similar to MySQL, since it is a fork of that. I will let you know, if I run into any more issues using MariaDB as in-place-replacement for MySQL, or if it is "unintentionally supported".

Could you kindly point me to the place where the migration files are located at? I did not find them. Maybe looking at the migration files would make it clear if this issue is MariaDB related or if this "should" fail on MySQL too. Because it should be easy to find out, whether and when the default value for these two columns is set in the migration process. If there is something seemingly off, even MySQL-wise, I would let you know and you can decide if this needs a fix.

Best regards, Jonas

[Demonsthere]

All migrations are inside the applications repo. For kratos please take a look here :). By the way, sine this is more app related, i would transfer the issue to the kratos repo, you might find more people with similar issues there. Or visit our slack channel :)

[aeneasr]

Hi there, MariaDB is not supported and we do not plan to add support for it. It's expensive to add database support due to the amount of work required when writing and testing migrations. Sorry!

[jonasbadstuebner]

Also for keto (on MariaDB) it was necessary to run

ALTER TABLE `ory_keto`.`keto_uuid_mappings` ADD CONSTRAINT keto_uuid_mappings_chk_1 CHECK (1 != 0);

[jonasbadstuebner]

Following this and adding sql_mode="" in the DSN fixed the problems for kratos. My DSN is now: mysql://$(DB_USER):$(DB_PASSWORD)@tcp($(DB_HOST):$(DB_PORT))/$(DB_NAME)?sql_mode='' Keto still needed help as mentioned above.

[designermonkey]

This error is still present in the 20200317160354000002_create_profile_request_forms migration also:

ory-kratos_migrate-1   | Error: error executing migrations/sql/20200317160354000002_create_profile_request_forms.mysql.up.sql, sql: INSERT INTO selfservice_profile_management_request_methods (id, method, selfservice_profile_management_request_id, config) SELECT id, 'traits', id, form FROM selfservice_profile_management_requests;: Error 1364 (HY000): Field 'created_at' doesn't have a default value
ory-kratos_migrate-1   | Usage:
ory-kratos_migrate-1   |   kratos migrate sql <database-url> [flags]
ory-kratos_migrate-1   | 
ory-kratos_migrate-1   | Flags:
ory-kratos_migrate-1   |   -c, --config strings   Path to one or more .json, .yaml, .yml, .toml config files. Values are loaded in the order provided, meaning that the last config file overwrites values from the previous config file.
ory-kratos_migrate-1   |   -h, --help             help for sql
ory-kratos_migrate-1   |   -e, --read-from-env    If set, reads the database connection string from the environment variable DSN or config file key dsn.
ory-kratos_migrate-1   |   -y, --yes              If set all confirmation requests are accepted without user interaction.
ory-kratos_migrate-1   | 
ory-kratos_migrate-1   | error executing migrations/sql/20200317160354000002_create_profile_request_forms.mysql.up.sql, sql: INSERT INTO selfservice_profile_management_request_methods (id, method, selfservice_profile_management_request_id, config) SELECT id, 'traits', id, form FROM selfservice_profile_management_requests;: Error 1364 (HY000): Field 'created_at' doesn't have a default value
o

@DrBu7cher's suggestion fixes it.

[jonasbadstuebner]

The error is a "won’t fix" because MariaDB is not officially supported :)

[digitive]

Although MariaDB is not officially supported, this bug should still be fixed and the fix is quite simple.

The cause is because in table selfservice_profile_management_requests the field created_at is set NOT NULL, but in service query the created_at is not specified.

INSERT INTO selfservice_profile_management_request_methods (id, method, selfservice_profile_management_request_id, config) SELECT id, 'traits', id, form FROM selfservice_profile_management_requests;

The fix is simple, just set the correct default value for created_at ( as well as updated_at ), like

ALTER TABLE `selfservice_profile_management_request_methods` ALTER COLUMN `created_at` SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `selfservice_profile_management_request_methods` ALTER COLUMN `updated_at` SET DEFAULT CURRENT_TIMESTAMP;

[jonasbadstuebner]

Does not make sense to fix a bug for something that is not supported - that is the whole point of not supporting it. If it does work as expected on all supported DBMS', this is not an issue.

Thank you anyway for posting the explanation for the problem and a fix.