Closed
Bug 1320926
Opened 8 years ago
Closed 8 years ago
Differences in the job_{group,type} schema between environments
Categories
(Tree Management :: Treeherder, defect, P2)
Tree Management
Treeherder
Tracking
(Not tracked)
RESOLVED
FIXED
People
(Reporter: emorley, Assigned: emorley)
References
Details
Attachments
(1 file)
--- vagrant.sql 2016-11-25 17:22:53.439965200 +0000
+++ prod.sql 2016-11-25 17:31:49.775087600 +0000
...
--
-- Table structure for table `job_group`
--
DROP TABLE IF EXISTS `job_group`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `job_group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
- `symbol` varchar(10) COLLATE utf8_bin NOT NULL,
+ `symbol` char(25) COLLATE utf8_bin DEFAULT '?',
`name` varchar(100) COLLATE utf8_bin NOT NULL,
- `description` longtext COLLATE utf8_bin NOT NULL,
+ `description` text COLLATE utf8_bin,
PRIMARY KEY (`id`),
UNIQUE KEY `<INDEX_NAME>` (`name`,`symbol`),
KEY `<INDEX_NAME>` (`symbol`),
KEY `<INDEX_NAME>` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
Django field defaults are not enforced at the DB level, which is why the `DEFAULT '?'` not set in the Vagrant schema (the prod schema was from when we used the template .sql files of our own).
The completely different field length for `symbol` is pretty awful however.
Assignee | ||
Comment 1•8 years ago
|
||
--
-- Table structure for table `job_type`
--
DROP TABLE IF EXISTS `job_type`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `job_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
- `symbol` varchar(10) COLLATE utf8_bin NOT NULL,
+ `symbol` char(25) COLLATE utf8_bin DEFAULT '?',
`name` varchar(100) COLLATE utf8_bin NOT NULL,
- `description` longtext COLLATE utf8_bin NOT NULL,
+ `description` text COLLATE utf8_bin,
`job_group_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `<INDEX_NAME>` (`name`,`symbol`),
KEY `<INDEX_NAME>` (`job_group_id`),
KEY `<INDEX_NAME>` (`symbol`),
KEY `<INDEX_NAME>` (`name`),
CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`job_group_id`) REFERENCES `job_group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
Summary: Differences in the job_group schema between environments → Differences in the job_{group,type} schema between environments
Assignee | ||
Comment 2•8 years ago
|
||
The SQL to run against stage/prod/prototype would be something like:
ALTER TABLE treeherder.job_group
MODIFY `symbol` varchar(10) NOT NULL,
MODIFY `description` longtext NOT NULL;
ALTER TABLE treeherder.job_type
MODIFY `symbol` varchar(10) NOT NULL,
MODIFY `description` longtext NOT NULL;
However there are symbols that exceed 10 characters:
> SELECT symbol, LENGTH(symbol) AS len FROM treeherder.job_group ORDER BY len DESC LIMIT 5
+ ----------- + -------- +
| symbol | len |
+ ----------- + -------- +
| Fxup-release-localtest | 22 |
| Fxup-release-cdntest | 20 |
| Fxup-esr-localtest | 18 |
| Fxup-beta-cdntest | 17 |
| Fxup-esr-cdntest | 16 |
...
> SELECT symbol, LENGTH(symbol) AS len FROM treeherder.job_type ORDER BY len DESC LIMIT 5
+ ----------- + -------- +
| symbol | len |
+ ----------- + -------- +
| ja-JP-mac-45.1.1esr | 19 |
| ja-JP-mac-45.1.0esr | 19 |
| ja-JP-mac-45.1.0esr | 19 |
| ja-JP-mac-45.1.0esr | 19 |
| ja-JP-mac-45.1.1esr | 19 |
...
Cameron, do you know if manual changes were made to these tables to allow these longer lengths? Should we make the model larger, or are these longer strings above actually undesirable?
Flags: needinfo?(cdawson)
Comment 3•8 years ago
|
||
25 sure seems long for a "Symbol" but people have been doing this. I recall having to change the ``pulse-job.yml`` to accommodate 25 chars, because they're coming to us this way through TaskCluster and they don't have any kind of "rules" that are applied at the time of Task Definition creation.
I think we need to bump these up to 25, tbh. I apologize I didn't change the JobType and JobGroup symbol sizes at that time. Not sure how I missed this, but I sure did.
Flags: needinfo?(cdawson)
Comment 4•8 years ago
|
||
Assignee | ||
Updated•8 years ago
|
Attachment #8816630 -
Flags: review?(cdawson)
Updated•8 years ago
|
Attachment #8816630 -
Flags: review?(cdawson) → review+
Comment 5•8 years ago
|
||
Commit pushed to master at https://github.com/mozilla/treeherder
https://github.com/mozilla/treeherder/commit/167d5a9c3021b76e8a635fb618a8c3e2b07f4164
Bug 1320926 - Increase Job{Type,Group} symbol length to match production
Assignee | ||
Updated•8 years ago
|
Status: NEW → RESOLVED
Closed: 8 years ago
Resolution: --- → FIXED
Assignee | ||
Comment 6•8 years ago
|
||
So the PR that landed that updated the in-tree model will resolve both the length differences and the other deviations for `symbol`, however `description` still needs fixing, so I'll now run:
ALTER TABLE treeherder.job_group MODIFY `description` longtext NOT NULL;
ALTER TABLE treeherder.job_type MODIFY `description` longtext NOT NULL;
Assignee | ||
Comment 7•8 years ago
|
||
After both the PR and comment 6:
CREATE TABLE `job_group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`symbol` varchar(25) COLLATE utf8_bin NOT NULL,
`name` varchar(100) COLLATE utf8_bin NOT NULL,
`description` longtext COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_name_symbol` (`name`,`symbol`),
KEY `idx_symbol` (`symbol`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=306 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE `job_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`job_group_id` int(11) DEFAULT NULL,
`symbol` varchar(25) COLLATE utf8_bin NOT NULL,
`name` varchar(100) COLLATE utf8_bin NOT NULL,
`description` longtext COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_name_symbol` (`name`,`symbol`),
KEY `idx_symbol` (`symbol`),
KEY `idx_name` (`name`),
KEY `fk_job_type_job_group` (`job_group_id`),
CONSTRAINT `fk_job_type_job_group` FOREIGN KEY (`job_group_id`) REFERENCES `job_group` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29861 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
You need to log in
before you can comment on or make changes to this bug.
Description
•