Skip to content

Models that circularly reference each other can not be instantiated by Persistent #24

Closed
thejerf opened this Issue Oct 2, 2011 · 11 comments

5 participants

@thejerf
thejerf commented Oct 2, 2011

The exact manifestation of the errors seem to vary, but the following tends to do "something bad":

ThingA
    thingb ThingBId Maybe
ThingB
    thinga ThingAId Maybe

Note that if the database is already correctly set up everything seems to work, so the workaround is to manually migrate the DB.

I think it suffices to reorder the steps taken to migrate to:

  1. Remove all no-longer-valid foreign key constraints. (Which I think is done first since this errors out and correctly demands the human do it themselves.)
  2. Perform all table structure changes.
  3. Add all missing foreign key constraints.
@meteficha
Yesod Web Framework member

The problem is that currently the migration code simply doesn't care about any relationships. It really needs an overhaul to be able to deal with more complicated cases.

@gregwebs
Yesod Web Framework member

For workarounds, you can use the printMigration function to get the migration Yesod was going to produce and then manually change it.

@snoyberg
Yesod Web Framework member

This should now be fixed for PostgreSQL: as @thejerf recommended, we now create tables and then apply constraints. @meteficha I think a similar technique would work for MySQL, if it was suffering from the same issue.

@gregwebs
Yesod Web Framework member

Is this issue still open just for MySQL? Is it related to #124 ?

@gregwebs
Yesod Web Framework member

@meteficha is this issue still open for MySQL?

@meteficha
Yesod Web Framework member

I'm not sure. Sadly, I don't have the resources to invest in fixing this issue right now.

@gregwebs gregwebs added the MySQL label Aug 4, 2014
@snoyberg
Yesod Web Framework member

I believe @gregwebs's fixed this recently, closing.

@snoyberg snoyberg closed this Aug 29, 2014
@gregwebs
Yesod Web Framework member

I don't touch migrations and that seems to be part of the problem here.

@gregwebs gregwebs reopened this Aug 29, 2014
@gregwebs
Yesod Web Framework member

This may actually be fixed with the latest versions of persistent. If not, a specific report of queries that create an error would be much appreciated.

@MaxGabriel
Yesod Web Framework member

Is this issue just about migration queries? If so, that's definitely working (Using Stackage Nightly 2015/05/22), so potentially this issue can be closed.

Using the following model file:

ThingA
    thingb ThingBId Maybe
ThingB
    thinga ThingAId Maybe

Causes these valid migrations:

Starting devel application
Migrating: CREATe TABLE `thing_a`(`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,`thingb` BIGINT NULL REFERENCES `thing_b`)
24/May/2015:20:51:34 -0700 [Debug#SQL] CREATe TABLE `thing_a`(`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,`thingb` BIGINT NULL REFERENCES `thing_b`); [] @(<unknown>:<unknown> <unknown>:0:0)
Migrating: CREATe TABLE `thing_b`(`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,`thinga` BIGINT NULL REFERENCES `thing_a`)
Migrating: ALTER TABLE `thing_a` ADD CONSTRAINT `thing_a_thingb_fkey` FOREIGN KEY(`thingb`) REFERENCES `thing_b`(`id`)
Migrating: ALTER TABLE `thing_b` ADD CONSTRAINT `thing_b_thinga_fkey` FOREIGN KEY(`thinga`) REFERENCES `thing_a`(`id`)
Devel application launched: http://localhost:3000
24/May/2015:20:51:34 -0700 [Debug#SQL] CREATe TABLE `thing_b`(`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,`thinga` BIGINT NULL REFERENCES `thing_a`); [] @(<unknown>:<unknown> <unknown>:0:0)
24/May/2015:20:51:34 -0700 [Debug#SQL] ALTER TABLE `thing_a` ADD CONSTRAINT `thing_a_thingb_fkey` FOREIGN KEY(`thingb`) REFERENCES `thing_b`(`id`); [] @(<unknown>:<unknown> <unknown>:0:0)
24/May/2015:20:51:34 -0700 [Debug#SQL] ALTER TABLE `thing_b` ADD CONSTRAINT `thing_b_thinga_fkey` FOREIGN KEY(`thinga`) REFERENCES `thing_a`(`id`); [] @(<unknown>:<unknown> <unknown>:0:0)

Yielding this MySQL schema:

mysql> SHOW CREATE TABLE thing_a\G
*************************** 1. row ***************************
       Table: thing_a
Create Table: CREATE TABLE `thing_a` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `thingb` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `thing_a_thingb_fkey` (`thingb`),
  CONSTRAINT `thing_a_thingb_fkey` FOREIGN KEY (`thingb`) REFERENCES `thing_b` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE thing_b\G
*************************** 1. row ***************************
       Table: thing_b
Create Table: CREATE TABLE `thing_b` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `thinga` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `thing_b_thinga_fkey` (`thinga`),
  CONSTRAINT `thing_b_thinga_fkey` FOREIGN KEY (`thinga`) REFERENCES `thing_a` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
@gregwebs
Yesod Web Framework member

Yes, seems to be working now. Thanks for investigating

@gregwebs gregwebs closed this May 25, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.