Closed Bug 1304096 Opened 8 years ago Closed 8 years ago

Differences in the per-project job schema between environments

Categories

(Tree Management :: Treeherder, defect, P2)

Product:
Tree Management
Component:
Treeherder
Type:
defect

Tracking

(Not tracked)

Status:
RESOLVED WONTFIX

People

(Reporter: emorley, Unassigned)

References

Details

Notable differences:
* `tier` is an unsigned int(10) on Vagrant/Heroku prototype, but signed elsewhere
* `last_modified` is NOT NULL on Vagrant/in some other places, but NULL elsewhere
* SCL3 stage has two additional composite indexes not present anywhere else.
* All environments apart from Vagrant have the old `pending_eta` field (albeit this is expected, since it's unused, and changes to the jobs table are slow, so didn't seem worth it in bug 1181572)

This bug may end up being wontfix, since the jobs table is large (so changes more problematic) and will be going away as we move away from the per-project DBs.

# SCL3 prod / Heroku stage:

CREATE TABLE `job` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `job_guid` varchar(50) COLLATE utf8_bin NOT NULL,
  `signature` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `job_coalesced_to_guid` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `result_set_id` bigint(20) unsigned NOT NULL,
  `build_platform_id` int(10) unsigned NOT NULL,
  `machine_platform_id` int(10) unsigned NOT NULL,
  `machine_id` int(10) unsigned DEFAULT NULL,
  `option_collection_hash` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `job_type_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `failure_classification_id` int(10) unsigned DEFAULT '1',
  `who` varchar(50) COLLATE utf8_bin NOT NULL,
  `reason` varchar(125) COLLATE utf8_bin NOT NULL,
  `result` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `state` varchar(25) COLLATE utf8_bin NOT NULL,
  `submit_timestamp` int(10) unsigned NOT NULL,
  `start_timestamp` int(10) unsigned DEFAULT NULL,
  `end_timestamp` int(10) unsigned DEFAULT NULL,
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `pending_eta` int(10) unsigned DEFAULT NULL,
  `running_eta` int(10) unsigned DEFAULT NULL,
  `tier` int(10) DEFAULT '1',
  `active_status` enum('active','onhold','deleted') COLLATE utf8_bin DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `<REMOVED>` (`job_guid`),
  KEY `<REMOVED>` (`job_coalesced_to_guid`),
  KEY `<REMOVED>` (`signature`),
  KEY `<REMOVED>` (`result_set_id`),
  KEY `<REMOVED>` (`build_platform_id`),
  KEY `<REMOVED>` (`machine_platform_id`),
  KEY `<REMOVED>` (`machine_id`),
  KEY `<REMOVED>` (`option_collection_hash`),
  KEY `<REMOVED>` (`job_type_id`),
  KEY `<REMOVED>` (`product_id`),
  KEY `<REMOVED>` (`failure_classification_id`),
  KEY `<REMOVED>` (`who`),
  KEY `<REMOVED>` (`reason`),
  KEY `<REMOVED>` (`result`),
  KEY `<REMOVED>` (`state`),
  KEY `<REMOVED>` (`submit_timestamp`),
  KEY `<REMOVED>` (`start_timestamp`),
  KEY `<REMOVED>` (`end_timestamp`),
  KEY `<REMOVED>` (`pending_eta`),
  KEY `<REMOVED>` (`running_eta`),
  KEY `<REMOVED>` (`active_status`),
  KEY `<REMOVED>` (`last_modified`),
  KEY `<REMOVED>` (`tier`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`result_set_id`) REFERENCES `result_set` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


# SCL3 stage:

CREATE TABLE `job` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `job_guid` varchar(50) COLLATE utf8_bin NOT NULL,
  `signature` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `job_coalesced_to_guid` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `result_set_id` bigint(20) unsigned NOT NULL,
  `build_platform_id` int(10) unsigned NOT NULL,
  `machine_platform_id` int(10) unsigned NOT NULL,
  `machine_id` int(10) unsigned DEFAULT NULL,
  `option_collection_hash` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `job_type_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `failure_classification_id` int(10) unsigned DEFAULT '1',
  `who` varchar(50) COLLATE utf8_bin NOT NULL,
  `reason` varchar(125) COLLATE utf8_bin NOT NULL,
  `result` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `state` varchar(25) COLLATE utf8_bin NOT NULL,
  `submit_timestamp` int(10) unsigned NOT NULL,
  `start_timestamp` int(10) unsigned DEFAULT NULL,
  `end_timestamp` int(10) unsigned DEFAULT NULL,
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `pending_eta` int(10) unsigned DEFAULT NULL,
  `running_eta` int(10) unsigned DEFAULT NULL,
  `tier` int(10) DEFAULT '1',
  `active_status` enum('active','onhold','deleted') COLLATE utf8_bin DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `<REMOVED>` (`job_guid`),
  KEY `<REMOVED>` (`job_coalesced_to_guid`),
  KEY `<REMOVED>` (`signature`),
  KEY `<REMOVED>` (`result_set_id`),
  KEY `<REMOVED>` (`build_platform_id`),
  KEY `<REMOVED>` (`machine_platform_id`),
  KEY `<REMOVED>` (`machine_id`),
  KEY `<REMOVED>` (`option_collection_hash`),
  KEY `<REMOVED>` (`job_type_id`),
  KEY `<REMOVED>` (`product_id`),
  KEY `<REMOVED>` (`failure_classification_id`),
  KEY `<REMOVED>` (`who`),
  KEY `<REMOVED>` (`reason`),
  KEY `<REMOVED>` (`result`),
  KEY `<REMOVED>` (`state`),
  KEY `<REMOVED>` (`submit_timestamp`),
  KEY `<REMOVED>` (`start_timestamp`),
  KEY `<REMOVED>` (`end_timestamp`),
  KEY `<REMOVED>` (`pending_eta`),
  KEY `<REMOVED>` (`running_eta`),
  KEY `<REMOVED>` (`active_status`),
  KEY `<REMOVED>` (`last_modified`),
  KEY `<REMOVED>` (`tier`),
  KEY `<REMOVED>` (`id`,`result_set_id`),
  KEY `<REMOVED>` (`option_collection_hash`,`build_platform_id`,`id`,`job_type_id`,`result_set_id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`result_set_id`) REFERENCES `result_set` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


# Heroku prototype:

CREATE TABLE `job` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `job_guid` varchar(50) COLLATE utf8_bin NOT NULL,
  `signature` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `job_coalesced_to_guid` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `result_set_id` bigint(20) unsigned NOT NULL,
  `build_platform_id` int(10) unsigned NOT NULL,
  `machine_platform_id` int(10) unsigned NOT NULL,
  `machine_id` int(10) unsigned DEFAULT NULL,
  `option_collection_hash` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `job_type_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `failure_classification_id` int(10) unsigned DEFAULT '1',
  `who` varchar(50) COLLATE utf8_bin NOT NULL,
  `reason` varchar(125) COLLATE utf8_bin NOT NULL,
  `result` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `state` varchar(25) COLLATE utf8_bin NOT NULL,
  `submit_timestamp` int(10) unsigned NOT NULL,
  `start_timestamp` int(10) unsigned DEFAULT NULL,
  `end_timestamp` int(10) unsigned DEFAULT NULL,
  `last_modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `pending_eta` int(10) unsigned DEFAULT NULL,
  `running_eta` int(10) unsigned DEFAULT NULL,
  `tier` int(10) unsigned DEFAULT '1',
  `active_status` enum('active','onhold','deleted') COLLATE utf8_bin DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `<REMOVED>` (`job_guid`),
  KEY `<REMOVED>` (`job_coalesced_to_guid`),
  KEY `<REMOVED>` (`signature`),
  KEY `<REMOVED>` (`result_set_id`),
  KEY `<REMOVED>` (`build_platform_id`),
  KEY `<REMOVED>` (`machine_platform_id`),
  KEY `<REMOVED>` (`machine_id`),
  KEY `<REMOVED>` (`option_collection_hash`),
  KEY `<REMOVED>` (`job_type_id`),
  KEY `<REMOVED>` (`product_id`),
  KEY `<REMOVED>` (`failure_classification_id`),
  KEY `<REMOVED>` (`who`),
  KEY `<REMOVED>` (`reason`),
  KEY `<REMOVED>` (`result`),
  KEY `<REMOVED>` (`state`),
  KEY `<REMOVED>` (`submit_timestamp`),
  KEY `<REMOVED>` (`start_timestamp`),
  KEY `<REMOVED>` (`end_timestamp`),
  KEY `<REMOVED>` (`pending_eta`),
  KEY `<REMOVED>` (`running_eta`),
  KEY `<REMOVED>` (`active_status`),
  KEY `<REMOVED>` (`last_modified`),
  KEY `<REMOVED>` (`tier`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`result_set_id`) REFERENCES `result_set` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


# Vagrant:

CREATE TABLE `job` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `job_guid` varchar(50) COLLATE utf8_bin NOT NULL,
  `signature` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `job_coalesced_to_guid` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `result_set_id` bigint(20) unsigned NOT NULL,
  `build_platform_id` int(10) unsigned NOT NULL,
  `machine_platform_id` int(10) unsigned NOT NULL,
  `machine_id` int(10) unsigned DEFAULT NULL,
  `option_collection_hash` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `job_type_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `failure_classification_id` int(10) unsigned DEFAULT '1',
  `who` varchar(50) COLLATE utf8_bin NOT NULL,
  `reason` varchar(125) COLLATE utf8_bin NOT NULL,
  `result` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `state` varchar(25) COLLATE utf8_bin NOT NULL,
  `submit_timestamp` int(10) unsigned NOT NULL,
  `start_timestamp` int(10) unsigned DEFAULT NULL,
  `end_timestamp` int(10) unsigned DEFAULT NULL,
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `running_eta` int(10) unsigned DEFAULT NULL,
  `tier` int(10) unsigned DEFAULT '1',
  `active_status` enum('active','onhold','deleted') COLLATE utf8_bin DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `<REMOVED>` (`job_guid`),
  KEY `<REMOVED>` (`job_coalesced_to_guid`),
  KEY `<REMOVED>` (`signature`),
  KEY `<REMOVED>` (`result_set_id`),
  KEY `<REMOVED>` (`build_platform_id`),
  KEY `<REMOVED>` (`machine_platform_id`),
  KEY `<REMOVED>` (`machine_id`),
  KEY `<REMOVED>` (`option_collection_hash`),
  KEY `<REMOVED>` (`job_type_id`),
  KEY `<REMOVED>` (`product_id`),
  KEY `<REMOVED>` (`failure_classification_id`),
  KEY `<REMOVED>` (`who`),
  KEY `<REMOVED>` (`reason`),
  KEY `<REMOVED>` (`result`),
  KEY `<REMOVED>` (`state`),
  KEY `<REMOVED>` (`submit_timestamp`),
  KEY `<REMOVED>` (`start_timestamp`),
  KEY `<REMOVED>` (`end_timestamp`),
  KEY `<REMOVED>` (`running_eta`),
  KEY `<REMOVED>` (`active_status`),
  KEY `<REMOVED>` (`last_modified`),
  KEY `<REMOVED>` (`tier`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`result_set_id`) REFERENCES `result_set` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
(In reply to Ed Morley [:emorley] from comment #0)
> * `tier` is an unsigned int(10) on Vagrant/Heroku prototype, but signed elsewhere

This field was manually added and the SQL used in bug 1148576 comment 7 didn't match that in the in-repo schema.

> * `last_modified` is NOT NULL on Vagrant/in some other places, but NULL elsewhere

This is a leftover from bug 1100983. (There should have been a "step 4" of "remove default once existing rows populated").
Blocks: 1148576, 1100983
Wontfix since we're going to be dropping these tables once the datasource->ORM migration (bug 1178641) is complete.
Status: NEW → RESOLVED
Closed: 8 years ago
Resolution: --- → WONTFIX
You need to log in before you can comment on or make changes to this bug.