1320946 - Differences in the reference_data_signatures schema between environments

Status: RESOLVED FIXED

(Tree Management :: Treeherder, defect, P2)

Description

[Ed Morley [:emorley]]

--- vagrant.sql	2016-11-25 17:22:53.439965200 +0000
+++ prod.sql	2016-11-25 17:31:49.775087600 +0000
...
 DROP TABLE IF EXISTS `reference_data_signatures`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `reference_data_signatures` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `name` varchar(255) COLLATE utf8_bin NOT NULL,
+  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
   `signature` varchar(50) COLLATE utf8_bin NOT NULL,
   `build_os_name` varchar(25) COLLATE utf8_bin NOT NULL,
   `build_platform` varchar(100) COLLATE utf8_bin NOT NULL,
   `build_architecture` varchar(25) COLLATE utf8_bin NOT NULL,
   `machine_os_name` varchar(25) COLLATE utf8_bin NOT NULL,
   `machine_platform` varchar(100) COLLATE utf8_bin NOT NULL,
   `machine_architecture` varchar(25) COLLATE utf8_bin NOT NULL,
-  `job_group_name` varchar(100) COLLATE utf8_bin NOT NULL,
-  `job_group_symbol` varchar(25) COLLATE utf8_bin NOT NULL,
+  `job_group_name` varchar(100) COLLATE utf8_bin DEFAULT NULL,
+  `job_group_symbol` char(25) COLLATE utf8_bin DEFAULT '?',
   `job_type_name` varchar(100) COLLATE utf8_bin NOT NULL,
-  `job_type_symbol` varchar(25) COLLATE utf8_bin NOT NULL,
-  `option_collection_hash` varchar(64) COLLATE utf8_bin NOT NULL,
-  `build_system_type` varchar(25) COLLATE utf8_bin NOT NULL,
+  `job_type_symbol` char(25) COLLATE utf8_bin DEFAULT '?',
+  `option_collection_hash` varchar(64) COLLATE utf8_bin DEFAULT NULL,
+  `build_system_type` varchar(25) COLLATE utf8_bin DEFAULT NULL,
   `repository` varchar(50) COLLATE utf8_bin NOT NULL,
-  `first_submission_timestamp` int(11) NOT NULL,
-  PRIMARY KEY (`id`),
+  `first_submission_timestamp` int(10) unsigned NOT NULL,
   UNIQUE KEY `<INDEX_NAME>` (`name`,`signature`,`build_system_type`,`repository`),
+  KEY `<INDEX_NAME>` (`id`),
   KEY `<INDEX_NAME>` (`signature`),
   KEY `<INDEX_NAME>` (`build_os_name`),
   KEY `<INDEX_NAME>` (`build_platform`),
   KEY `<INDEX_NAME>` (`build_architecture`),
   KEY `<INDEX_NAME>` (`machine_os_name`),
   KEY `<INDEX_NAME>` (`machine_platform`),
   KEY `<INDEX_NAME>` (`machine_architecture`),
   KEY `<INDEX_NAME>` (`job_group_name`),
   KEY `<INDEX_NAME>` (`job_group_symbol`),
   KEY `<INDEX_NAME>` (`job_type_name`),
   KEY `<INDEX_NAME>` (`job_type_symbol`),
   KEY `<INDEX_NAME>` (`option_collection_hash`),
   KEY `<INDEX_NAME>` (`build_system_type`),
   KEY `<INDEX_NAME>` (`repository`),
   KEY `<INDEX_NAME>` (`first_submission_timestamp`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 /*!40101 SET character_set_client = @saved_cs_client */;

Comment 1

[Ed Morley [:emorley]]

Attachment: SQL to run against prod

Tested against dev/stage and confirmed resulted in a schema matching Vagrant.

Comment 2

[Ed Morley [:emorley]]

Applied against prod.

Vagrant and prod schemas are now identical (the difference in `id` data type in comment 0 was already fixed by something else).

Comment 3

[Ed Morley [:emorley]]

(In reply to Ed Morley [:emorley] from comment #2)
> Vagrant and prod schemas are now identical

For this table, that is.