1320930 - Differences in the option_collection 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
...
 --
 -- Table structure for table `option_collection`
 --
 
 DROP TABLE IF EXISTS `option_collection`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `option_collection` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `option_collection_hash` varchar(40) COLLATE utf8_bin NOT NULL,
-  `option_id` int(11) NOT NULL,
+  `option_id` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `<INDEX_NAME>` (`option_collection_hash`,`option_id`),
   KEY `<INDEX_NAME>` (`option_id`),
   KEY `<INDEX_NAME>` (`option_collection_hash`),
   CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`option_id`) REFERENCES `option` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 /*!40101 SET character_set_client = @saved_cs_client */;

Comment 1

[Ed Morley [:emorley]]

Cameron, does this look correct to you (to run against stage/prod/prototype)?

ALTER TABLE treeherder.option_collection MODIFY `option_id` int(11) NOT NULL;

Comment 2

[Cameron Dawson [:camd]]

Yep, looks good.  :)

Comment 3

[Ed Morley [:emorley]]

Applied to prod+stage+dev.

Before:

CREATE TABLE `option_collection` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `option_collection_hash` varchar(40) COLLATE utf8_bin NOT NULL,
  `option_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_option_collection` (`option_collection_hash`,`option_id`),
  KEY `idx_option` (`option_id`),
  KEY `idx_option_collection_hash` (`option_collection_hash`),
  CONSTRAINT `fk_option` FOREIGN KEY (`option_id`) REFERENCES `option` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

After:

CREATE TABLE `option_collection` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `option_collection_hash` varchar(40) COLLATE utf8_bin NOT NULL,
  `option_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_option_collection` (`option_collection_hash`,`option_id`),
  KEY `idx_option` (`option_id`),
  KEY `idx_option_collection_hash` (`option_collection_hash`),
  CONSTRAINT `fk_option` FOREIGN KEY (`option_id`) REFERENCES `option` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COLLATE=utf8_bin