--- 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.

-- -- 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 */;

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?

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.

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

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;

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