Notable differences: * Foreign key constraint missing from all environments apart from Vagrant * Charset/collation wierdness on SCL3 (though being thrown away soon) * Vagrant `modified` is missing the `DEFAULT CURRENT_TIMESTAMP` Cameron, could you take a look? -- Django model: class UserExclusionProfile(models.Model): user = models.ForeignKey(User, related_name="exclusion_profiles") exclusion_profile = models.ForeignKey(ExclusionProfile, blank=True, null=True) is_default = models.BooleanField(default=True, db_index=True) class Meta: db_table = 'user_exclusion_profile' unique_together = ('user', 'exclusion_profile') # SCL3 prod / Heroku stage / Heroku prototype: CREATE TABLE `exclusion_profile` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_bin NOT NULL, `is_default` tinyint(1) NOT NULL, `flat_exclusion` longtext COLLATE utf8_bin NOT NULL, `author_id` int(11) NOT NULL, `modified` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `<REMOVED>` (`name`), KEY `<REMOVED>` (`name`), KEY `<REMOVED>` (`is_default`), KEY `<REMOVED>` (`author_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; # SCL3 stage: CREATE TABLE `exclusion_profile` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `is_default` tinyint(1) NOT NULL, `flat_exclusion` longtext, `author_id` int(11) NOT NULL, `modified` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `<REMOVED>` (`name`), KEY `<REMOVED>` (`name`), KEY `<REMOVED>` (`is_default`), KEY `<REMOVED>` (`author_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # Vagrant: CREATE TABLE `exclusion_profile` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `is_default` tinyint(1) NOT NULL, `flat_exclusion` longtext NOT NULL, `author_id` int(11) NOT NULL, `modified` datetime(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `<REMOVED>` (`name`), KEY `<REMOVED>` (`is_default`), KEY `<REMOVED>` (`author_id`), CONSTRAINT `<REMOVED>` FOREIGN KEY (`author_id`) REFERENCES `auth_user` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Oh, and: * Unnecessary extra index on `name` for all environments apart from Vagrant (since dupes primary key)

(In reply to Ed Morley [:emorley] from comment #0) > Django model: Correction, this is the relevant Django model: class ExclusionProfile(models.Model): name = models.CharField(max_length=255, unique=True) is_default = models.BooleanField(default=False, db_index=True) exclusions = models.ManyToManyField(JobExclusion, related_name="profiles") flat_exclusion = JSONField(blank=True, default={}) author = models.ForeignKey(User, related_name="exclusion_profiles_authored", db_index=True) modified = models.DateTimeField(auto_now=True)

> * Vagrant `modified` is missing the `DEFAULT CURRENT_TIMESTAMP` Ah ok so the field was added manually in bug 1194824 comment 8 with a DEFAULT that isn't generated by the model. Django instead sets the timestamp app-side, when using `models.DateTimeField(auto_now=True)`.

Thanks for finding all this! I'll address these after the Horoku migration, as we discussed in IRC.

Now that the other environments have been recreated, the differences are: --- vagrant.sql 2016-11-25 17:22:53.439965200 +0000 +++ prod.sql 2016-11-25 17:31:49.775087600 +0000 ... -- -- Table structure for table `exclusion_profile` -- DROP TABLE IF EXISTS `exclusion_profile`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `exclusion_profile` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_bin NOT NULL, `is_default` tinyint(1) NOT NULL, `flat_exclusion` longtext COLLATE utf8_bin NOT NULL, - `modified` datetime(6) NOT NULL, + `modified` datetime DEFAULT CURRENT_TIMESTAMP, `author_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `<INDEX_NAME>` (`name`), + KEY `<INDEX_NAME>` (`name`), KEY `<INDEX_NAME>` (`author_id`), - KEY `<INDEX_NAME>` (`is_default`), - CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`author_id`) REFERENCES `auth_user` (`id`) + KEY `<INDEX_NAME>` (`is_default`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; The following should bring prod in line with the Django model (Vagrant): ALTER TABLE treeherder.exclusion_profile MODIFY `modified` datetime(6) NOT NULL DROP KEY `idx_name` ADD CONSTRAINT `exclusion_profile_author_id_f7428f1305c8a06_fk_auth_user_id` FOREIGN KEY (`author_id`) REFERENCES `auth_user` (`id`); (Constraint name copied from the one auto-generated by Django in Vagrant) Cameron, does this look ok to you?

Oh with missing commas added: ALTER TABLE treeherder.exclusion_profile MODIFY `modified` datetime(6) NOT NULL, DROP KEY `idx_name`, ADD CONSTRAINT `exclusion_profile_author_id_f7428f1305c8a06_fk_auth_user_id` FOREIGN KEY (`author_id`) REFERENCES `auth_user` (`id`);

This is interesting. I would have expected the model field for ExclusionProfile of: modified = models.DateTimeField(auto_now=True) to produce the one we have on prod, but I guess it doesn't. Presumably, The django ORM handles setting that value rather than letting the DB do it. Looks like calls to Model.save() update it, but calls to QuerySet.update() do not. Anyway, this looks good to me. Thanks!

Applied to prod+stage+dev. Before: CREATE TABLE `exclusion_profile` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_bin NOT NULL, `is_default` tinyint(1) NOT NULL, `flat_exclusion` longtext COLLATE utf8_bin NOT NULL, `author_id` int(11) NOT NULL, `modified` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `idx_name` (`name`), KEY `idx_is_default` (`is_default`), KEY `idx_author` (`author_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin After: CREATE TABLE `exclusion_profile` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_bin NOT NULL, `is_default` tinyint(1) NOT NULL, `flat_exclusion` longtext COLLATE utf8_bin NOT NULL, `author_id` int(11) NOT NULL, `modified` datetime(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `idx_is_default` (`is_default`), KEY `idx_author` (`author_id`), CONSTRAINT `exclusion_profile_author_id_f7428f1305c8a06_fk_auth_user_id` FOREIGN KEY (`author_id`) REFERENCES `auth_user` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin