Closed
Bug 1335975
Opened 8 years ago
Closed 8 years ago
All foreign key constraints missing from the performance_datum table on prod
Categories
(Tree Management :: Perfherder, defect, P1)
Tree Management
Perfherder
Tracking
(Not tracked)
RESOLVED
FIXED
People
(Reporter: emorley, Assigned: wlach)
References
Details
Attachments
(1 file)
I'm presuming this is fallout from the manual performance_datum table changes in bug 1265503.
--- schema-vagrant.sql
+++ schema-prod.sql
...
CREATE TABLE `performance_datum` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` double NOT NULL,
`push_timestamp` datetime(6) NOT NULL,
`ds_job_id` int(10) unsigned DEFAULT NULL,
`result_set_id` int(10) unsigned DEFAULT NULL,
`job_id` bigint(20) DEFAULT NULL,
`push_id` int(11) NOT NULL,
`repository_id` int(11) NOT NULL,
`signature_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `<NAME>` (`repository_id`,`job_id`,`push_id`,`signature_id`),
KEY `<NAME>` (`repository_id`,`signature_id`,`push_timestamp`),
KEY `<NAME>` (`job_id`),
KEY `<NAME>` (`push_id`),
- KEY `<NAME>` (`signature_id`),
- CONSTRAINT `<NAME>` FOREIGN KEY (`signature_id`) REFERENCES `performance_signature` (`id`),
- CONSTRAINT `<NAME>` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`),
- CONSTRAINT `<NAME>` FOREIGN KEY (`push_id`) REFERENCES `push` (`id`),
- CONSTRAINT `<NAME>` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`)
+ KEY `<NAME>` (`signature_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
The index/constraint names from Vagrant are:
...
KEY `performance_datum_repository_id_aea3e540_idx` (`repository_id`,`signature_id`,`push_timestamp`),
KEY `performance_datum_job_id_2851afe3_fk_job_id` (`job_id`),
KEY `performance_datum_push_id_74dba66f_fk_push_id` (`push_id`),
KEY `performance_da_signature_id_a9af3d6b_fk_performance_signature_id` (`signature_id`),
CONSTRAINT `performance_da_signature_id_a9af3d6b_fk_performance_signature_id` FOREIGN KEY (`signature_id`) REFERENCES `performance_signature` (`id`),
CONSTRAINT `performance_datum_job_id_2851afe3_fk_job_id` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`),
CONSTRAINT `performance_datum_push_id_74dba66f_fk_push_id` FOREIGN KEY (`push_id`) REFERENCES `push` (`id`),
CONSTRAINT `performance_datum_repository_id_dcef2c93_fk_repository_id` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`)
Reporter | ||
Updated•8 years ago
|
Flags: needinfo?(wlachance)
Assignee | ||
Comment 1•8 years ago
|
||
Hmm, not sure how this happened but I guess it was probably me somehow, probably trying to optimize migration of the performance datum table (which is extremely slow). Sorry. I think the attached sql should fix it?
Assignee: nobody → wlachance
Flags: needinfo?(wlachance)
Assignee | ||
Comment 2•8 years ago
|
||
(In reply to William Lachance (:wlach) [ away Jan 28-> Feb 4 2017 ] from comment #1)
> Created attachment 8834034 [details]
> SQL to re-add constraints and index
>
> Hmm, not sure how this happened but I guess it was probably me somehow,
> probably trying to optimize migration of the performance datum table (which
> is extremely slow). Sorry. I think the attached sql should fix it?
(should add that I tested this on stage and it worked great)
Reporter | ||
Comment 3•8 years ago
|
||
Comment on attachment 8834034 [details]
SQL to re-add constraints and index
Looks good, thank you!
Attachment #8834034 -
Flags: review+
Assignee | ||
Comment 4•8 years ago
|
||
Applied the changes to prod, everything looks good now.
Reporter | ||
Comment 5•8 years ago
|
||
Many thanks! :-)
Status: NEW → RESOLVED
Closed: 8 years ago
Resolution: --- → FIXED
Reporter | ||
Comment 6•8 years ago
|
||
The schemas are much closer on Vagrant and prod now, but there is one difference remaining. Prod has a duplicate index on signature_id, that wasn't there in comment 0.
Vagrant:
CREATE TABLE `performance_datum` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` double NOT NULL,
`push_timestamp` datetime(6) NOT NULL,
`ds_job_id` int(10) unsigned DEFAULT NULL,
`result_set_id` int(10) unsigned DEFAULT NULL,
`job_id` bigint(20) DEFAULT NULL,
`push_id` int(11) NOT NULL,
`repository_id` int(11) NOT NULL,
`signature_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `performance_datum_repository_id_766430e3_uniq` (`repository_id`,`job_id`,`push_id`,`signature_id`),
KEY `performance_datum_repository_id_aea3e540_idx` (`repository_id`,`signature_id`,`push_timestamp`),
KEY `performance_datum_job_id_2851afe3_fk_job_id` (`job_id`),
KEY `performance_datum_push_id_74dba66f_fk_push_id` (`push_id`),
KEY `performance_da_signature_id_a9af3d6b_fk_performance_signature_id` (`signature_id`),
CONSTRAINT `performance_da_signature_id_a9af3d6b_fk_performance_signature_id` FOREIGN KEY (`signature_id`) REFERENCES `performance_signature` (`id`),
CONSTRAINT `performance_datum_job_id_2851afe3_fk_job_id` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`),
CONSTRAINT `performance_datum_push_id_74dba66f_fk_push_id` FOREIGN KEY (`push_id`) REFERENCES `push` (`id`),
CONSTRAINT `performance_datum_repository_id_dcef2c93_fk_repository_id` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Prod:
CREATE TABLE `performance_datum` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ds_job_id` int(10) unsigned DEFAULT NULL,
`result_set_id` int(10) unsigned DEFAULT NULL,
`value` double NOT NULL,
`push_timestamp` datetime(6) NOT NULL,
`repository_id` int(11) NOT NULL,
`signature_id` int(11) NOT NULL,
`push_id` int(11) NOT NULL,
`job_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `performance_datum_repository_id_766430e3_uniq` (`repository_id`,`job_id`,`push_id`,`signature_id`),
KEY `performance_datum_repository_id_795c16d99ae557e2_idx` (`repository_id`,`signature_id`,`push_timestamp`),
KEY `performance_datum_edb74c98` (`signature_id`),
KEY `performance_datum_e13f0cea` (`push_id`),
KEY `performance_datum_d697ea38` (`job_id`),
KEY `performance_da_signature_id_a9af3d6b_fk_performance_signature_id` (`signature_id`),
CONSTRAINT `performance_da_signature_id_a9af3d6b_fk_performance_signature_id` FOREIGN KEY (`signature_id`) REFERENCES `performance_signature` (`id`),
CONSTRAINT `performance_datum_job_id_2851afe3_fk_job_id` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`),
CONSTRAINT `performance_datum_push_id_74dba66f_fk_push_id` FOREIGN KEY (`push_id`) REFERENCES `push` (`id`),
CONSTRAINT `performance_datum_repository_id_dcef2c93_fk_repository_id` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=222650486 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
To resolve, we can run this against stage/prod (presuming it looks good to you):
ALTER TABLE treeherder.performance_datum DROP INDEX performance_datum_edb74c98;
Also prototype was missing the constraints still, so I've just run the previous SQL (minus the duplicate index) against it now.
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
Reporter | ||
Comment 7•8 years ago
|
||
(In reply to Ed Morley [:emorley] from comment #6)
> To resolve, we can run this against stage/prod (presuming it looks good to
> you):
>
> ALTER TABLE treeherder.performance_datum DROP INDEX
> performance_datum_edb74c98;
Done :-)
Status: REOPENED → RESOLVED
Closed: 8 years ago → 8 years ago
Resolution: --- → FIXED
You need to log in
before you can comment on or make changes to this bug.
Description
•