1304339 - Differences in the job_detail table schema between environments

Status: RESOLVED FIXED

(Tree Management :: Treeherder, defect, P1)

Description

[Ed Morley [:emorley]]

The schema is different in all five environments :-(

Differences:
* SCL3 stage has indexes on `title` and `value` not present in any other environment.
* The field `value` is NOT NULL on Vagrant/Heroku stage/Heroku prototype, but DEFAULT NULL on SCL3 stage/SCL3 prod.
* On Heroku prototype, the foreign key constraint has an ON DELETE CASCADE, but other environments do not.
* Charset/collation varies (bug 1303767)

Looks like several of these are leftovers from bug 1278711 (and the heroku prototype difference is from bug 1284432).


# SCL3 prod:

CREATE TABLE `job_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(70) COLLATE utf8_bin DEFAULT NULL,
  `value` varchar(125) COLLATE utf8_bin DEFAULT NULL,
  `url` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  `job_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `<REMOVED>` (`title`,`value`,`job_id`),
  KEY `<REMOVED>` (`job_id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


# SCL3 stage:

CREATE TABLE `job_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(70) COLLATE utf8_bin DEFAULT NULL,
  `value` varchar(125) COLLATE utf8_bin DEFAULT NULL,
  `url` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  `job_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `<REMOVED>` (`title`,`value`,`job_id`),
  KEY `<REMOVED>` (`job_id`),
  KEY `<REMOVED>` (`title`),
  KEY `<REMOVED>` (`value`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


# Heroku stage:

CREATE TABLE `job_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(70) COLLATE utf8_bin DEFAULT NULL,
  `value` varchar(125) COLLATE utf8_bin NOT NULL,
  `url` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  `job_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `<REMOVED>` (`title`,`value`,`job_id`),
  KEY `<REMOVED>` (`job_id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


# Heroku prototype:

CREATE TABLE `job_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(70) DEFAULT NULL,
  `value` varchar(125) NOT NULL,
  `url` varchar(512) DEFAULT NULL,
  `job_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `<REMOVED>` (`title`,`value`,`job_id`),
  KEY `<REMOVED>` (`job_id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


# Vagrant:

CREATE TABLE `job_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(70) DEFAULT NULL,
  `value` varchar(125) NOT NULL,
  `url` varchar(512) DEFAULT NULL,
  `job_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `<REMOVED>` (`title`,`value`,`job_id`),
  KEY `<REMOVED>` (`job_id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Comment 1

[Ed Morley [:emorley]]

Now that the other environments have been recreated, the only 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 `job_detail`
 --
 
 DROP TABLE IF EXISTS `job_detail`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `job_detail` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `title` varchar(70) COLLATE utf8_bin DEFAULT NULL,
-  `value` varchar(125) COLLATE utf8_bin NOT NULL,
+  `value` varchar(125) COLLATE utf8_bin DEFAULT NULL,
   `url` varchar(512) COLLATE utf8_bin DEFAULT NULL,
   `job_id` bigint(20) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `<INDEX_NAME>` (`title`,`value`,`job_id`),
   KEY `<INDEX_NAME>` (`job_id`),
   CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 /*!40101 SET character_set_client = @saved_cs_client */;

On prod `select * from treeherder.job_detail where `value` is null` returned zero rows, so we should be able to just run:

ALTER TABLE treeherder.job_detail MODIFY `value` varchar(125) NOT NULL;

Comment 2

[Ed Morley [:emorley]]

Applied on stage+dev, however prod failed with:

Error Code: 1062. Duplicate entry 'Summary-Mochitest browser-chrome run-61243251' for key 'title_value_job_uniq'

Strange the others didn't too...

Comment 3

[Ed Morley [:emorley]]

(In reply to Ed Morley [:emorley] from comment #1)
> On prod `select * from treeherder.job_detail where `value` is null` returned
> zero rows, so we should be able to just run:
> 
> ALTER TABLE treeherder.job_detail MODIFY `value` varchar(125) NOT NULL;

(In reply to Ed Morley [:emorley] from comment #2)
> Applied on stage+dev, however prod failed with:
> 
> Error Code: 1062. Duplicate entry 'Summary-Mochitest browser-chrome
> run-61243251' for key 'title_value_job_uniq'
> 
> Strange the others didn't too...

I repeated the 'SELECT * FROM treeherder.job_detail WHERE value is NULL;' on prod, and got zero rows returned (as before).

In the meantime, dev was reset to prod, so I'm trying the ALTER TABLE again there to see if prod has now expired the rows that were problematic before.

Comment 4

[Ed Morley [:emorley]]

That succeeded on dev (prototype), which was reset to the prod DB recently, so should work on prod now too.

14:11:35	ALTER TABLE treeherder.job_detail MODIFY `value` varchar(125) NOT NULL	0 row(s) affected Records: 0  Duplicates: 0  Warnings: 0	22850.641 sec

...albeit it took 6.5 hours (admittedly m4.xlarge and prod is m4.2xlarge).

Comment 5

[Ed Morley [:emorley]]

Running the comment 1 ALTER TABLE against prod now.

Comment 6

[Ed Morley [:emorley]]

Complete successfully on prod, took 7 hours 4 minutes.