Hi all. Finally going to move to hydra 2.0 (from 1.11), but I’m getting a migration error.

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;

The ‘nid’ column was just added by the previous migration: 20220210000001000001_nid.mysql.up.sql

ALTER TABLE `hydra_client` ADD COLUMN `nid` char(36);
ALTER TABLE `hydra_client` ADD CONSTRAINT `hydra_client_nid_fk_idx` FOREIGN KEY (`nid`) REFERENCES `networks` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE;

Running this on a mysql 8.0 database. Anyone encountered this?

This looks familiar. I think the user who reported the issue initially solved it by manually dropping the constraints. Though, for them it was a different FK.

I'll need to figure out how to apply migrations one at a time then, since the constraint is added during migration (the step just prior to the problematic one)

I also found this issue: https://github.com/ory/hydra/issues/3363 Are you able to set the

SQL_MODE

system variable? There was a similar issue in Kratos, with a solution: https://github.com/ory/kratos/issues/2167#issuecomment-1023556058

Great, thanks. That does look useful!

GH search can be moody at times. 😉

So, I can easily set the mysql global sql_mode to a value that makes the migration work on my local test system. Is it possible to set the session sql_mode through the DSN string? From quickly browsing the documentation, I can’t see a way to do this, but I’m mistrusting my searching skills today….

I am not too familiar with mysql unfortunately, so not sure.