auto_migrate! with belongs_to with a composite key works in one case but not the other #1

Open
jdodds opened this Issue Sep 2, 2011 · 4 comments

Comments

Projects
None yet
4 participants

jdodds commented Sep 2, 2011

I'm filing this here because I'm assuming that it's a bug with the adapter, but I'm not entirely sure. It works with sqlite3, but that may just be a coincidence.

Anyhow, I made a repo, https://github.com/jdodds/datamapper-bug , that demonstrates the behavior. I have a model with a composite primary key, and two models that refer to it with belongs_to ..., :key => true, while it has the corresponding has n declarations. Looking at the logs, it creates the first model's table correctly, but either fails to detect or ignores the composite key when creating the second model's table.

If you need any more info, let me know.

Contributor

solnic commented Sep 2, 2011

Could you check this against DM gems from master?

jdodds commented Sep 2, 2011

Just did, and updated the repo I made to reflect that. I see the same behavior.

Here's the relevant info from the logs:

[datamapper] Setting up the "development" environment:
[datamapper] Setting up :default repository: 'datamapper_bugs_development' on mysql
  SQL (0.096ms)  SET sql_auto_is_null = 0
  SQL (0.136ms)  SET SESSION sql_mode = 'ANSI,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,TRADITIONAL'
  SQL (0.282ms)  SHOW TABLES LIKE 'backup_picks'
  SQL (0.186ms)  SHOW TABLES LIKE 'picks'
  SQL (0.180ms)  DROP TABLE IF EXISTS `backup_picks`
  SQL (0.192ms)  SHOW TABLES LIKE 'backup_picks'
  SQL (12.310ms)  CREATE TABLE `backup_picks` (`preference` INTEGER NOT NULL, `person_id` INT(10) UNSIGNED NOT NULL, `user_team_user_id` INT(10) UNSIGNED NOT NULL, `user_team_league_id` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`person_id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
  SQL (8.652ms)  CREATE INDEX `index_backup_picks_person` ON `backup_picks` (`person_id`)
  SQL (9.633ms)  CREATE INDEX `index_backup_picks_user_team` ON `backup_picks` (`user_team_user_id`, `user_team_league_id`)
  SQL (0.150ms)  DROP TABLE IF EXISTS `picks`
  SQL (0.209ms)  SHOW TABLES LIKE 'picks'
  SQL (2.881ms)  CREATE TABLE `picks` (`user_team_user_id` INT(10) UNSIGNED NOT NULL, `user_team_league_id` INT(10) UNSIGNED NOT NULL, `person_id` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`user_team_user_id`, `user_team_league_id`, `person_id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
  SQL (9.370ms)  CREATE INDEX `index_picks_user_team` ON `picks` (`user_team_user_id`, `user_team_league_id`)
  SQL (8.308ms)  CREATE INDEX `index_picks_person` ON `picks` (`person_id`)
  SQL (0.174ms)  DROP TABLE IF EXISTS `user_teams`
  SQL (0.234ms)  SHOW TABLES LIKE 'user_teams'
  SQL (3.064ms)  CREATE TABLE `user_teams` (`name` VARCHAR(50) NOT NULL, `user_id` INT(10) UNSIGNED NOT NULL, `league_id` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`user_id`, `league_id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
  SQL (8.725ms)  CREATE INDEX `index_user_teams_user` ON `user_teams` (`user_id`)
  SQL (8.333ms)  CREATE INDEX `index_user_teams_league` ON `user_teams` (`league_id`)
  SQL (0.405ms)  SELECT COUNT(*) FROM `information_schema`.`table_constraints` WHERE `constraint_type` = 'FOREIGN KEY' AND `table_schema` = 'datamapper_bugs_development' AND `table_name` = 'backup_picks' AND `constraint_name` = 'backup_picks_user_team_fk'
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') REFERENCES `user_teams` () ON DELETE NO ACTION ON UPDATE NO ACTION' at line 1 (code: 1064, sql state: 42000, query: ALTER TABLE `backup_picks` ADD CONSTRAINT `backup_picks_user_team_fk` FOREIGN KEY () REFERENCES `user_teams` () ON DELETE NO ACTION ON UPDATE NO ACTION, uri: mysql:root:FIDDLEFADDLEIBEENCENSORED@localhostdatamapper_bugs_development?database=datamapper_bugs_development&path=datamapper_bugs_development&adapter=mysql&host=localhost&username=root&password=OHGAWDTHECENSORSHIP)

Is there a workaround for this aside from not using composite keys on join models? I'm running into it as well.

Here's a solution I found. Add the attribute :min => 1 to your foreign key dependent propery.

property :invoice_id, Integer, :min => 1, :index => true

http://datamapper.lighthouseapp.com/projects/20609/tickets/1236-sql-error-cant-create-table-during-migration-with-dm-constraints

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment