Closed
Bug 1304078
Opened 8 years ago
Closed 8 years ago
Differences in the exclusion_profile schema between environments
Categories
(Tree Management :: Treeherder, defect, P1)
Tree Management
Treeherder
Tracking
(Not tracked)
RESOLVED
FIXED
People
(Reporter: emorley, Assigned: emorley)
References
Details
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;
Flags: needinfo?(cdawson)
Assignee | ||
Comment 1•8 years ago
|
||
Oh, and:
* Unnecessary extra index on `name` for all environments apart from Vagrant (since dupes primary key)
Assignee | ||
Comment 2•8 years ago
|
||
(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)
Assignee | ||
Comment 3•8 years ago
|
||
> * 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)`.
Blocks: 1194824
Comment 4•8 years ago
|
||
Thanks for finding all this! I'll address these after the Horoku migration, as we discussed in IRC.
Assignee: nobody → cdawson
Status: NEW → ASSIGNED
Flags: needinfo?(cdawson)
Assignee | ||
Comment 5•8 years ago
|
||
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?
Flags: needinfo?(cdawson)
Assignee | ||
Updated•8 years ago
|
Assignee: cdawson → emorley
Assignee | ||
Comment 6•8 years ago
|
||
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`);
Comment 7•8 years ago
|
||
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!
Flags: needinfo?(cdawson)
Assignee | ||
Comment 8•8 years ago
|
||
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
Status: ASSIGNED → RESOLVED
Closed: 8 years ago
Resolution: --- → FIXED
You need to log in
before you can comment on or make changes to this bug.
Description
•