Closed
Bug 1320947
Opened 8 years ago
Closed 8 years ago
Differences in the repository schema between environments
Categories
(Tree Management :: Treeherder, defect, P2)
Tree Management
Treeherder
Tracking
(Not tracked)
RESOLVED
FIXED
People
(Reporter: emorley, Assigned: emorley)
References
Details
Attachments
(1 file)
--- vagrant.sql 2016-11-25 17:22:53.439965200 +0000
+++ prod.sql 2016-11-25 17:31:49.775087600 +0000
...
--
-- Table structure for table `repository`
--
DROP TABLE IF EXISTS `repository`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `repository` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_bin NOT NULL,
`dvcs_type` varchar(25) COLLATE utf8_bin NOT NULL,
`url` varchar(255) COLLATE utf8_bin NOT NULL,
- `codebase` varchar(50) COLLATE utf8_bin NOT NULL,
- `description` longtext COLLATE utf8_bin NOT NULL,
- `active_status` varchar(7) COLLATE utf8_bin NOT NULL,
- `repository_group_id` int(11) NOT NULL,
+ `codebase` varchar(50) COLLATE utf8_bin DEFAULT NULL,
+ `description` text COLLATE utf8_bin,
+ `active_status` enum('active','onhold','deleted') COLLATE utf8_bin DEFAULT 'active',
+ `repository_group_id` int(11) DEFAULT NULL,
`performance_alerts_enabled` tinyint(1) NOT NULL,
- `branch` varchar(50) COLLATE utf8_bin,
+ `branch` varchar(50) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `<INDEX_NAME>` (`name`),
KEY `<INDEX_NAME>` (`name`),
KEY `<INDEX_NAME>` (`dvcs_type`),
KEY `<INDEX_NAME>` (`codebase`),
KEY `<INDEX_NAME>` (`active_status`),
KEY `<INDEX_NAME>` (`repository_group_id`),
KEY `<INDEX_NAME>` (`branch`),
CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`repository_group_id`) REFERENCES `repository_group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
Assignee | ||
Comment 1•8 years ago
|
||
Updated•8 years ago
|
Attachment #8817468 -
Flags: review?(cdawson) → review+
Assignee | ||
Comment 2•8 years ago
|
||
Running that SQL gave:
"Error Code: 1832. Cannot change column 'repository_group_id': used in a foreign key constraint 'fk_repository_group'"
So I've skipped changing that field for now and just modified the others (on dev/stage/prod), using:
ALTER TABLE treeherder.repository
MODIFY `codebase` varchar(50) NOT NULL,
MODIFY `description` longtext NOT NULL,
MODIFY `active_status` varchar(7) NOT NULL,
MODIFY `branch` varchar(50);
To change `repository_group_id` I'll need to drop the foreign key constraint and modify the repository_group table at the same time, but I'll do that later.
Assignee | ||
Comment 3•8 years ago
|
||
(In reply to Ed Morley [:emorley] from comment #2)
> Running that SQL gave:
> "Error Code: 1832. Cannot change column 'repository_group_id': used in a
> foreign key constraint 'fk_repository_group'"
>
> So I've skipped changing that field for now and just modified the others
Running this same SQL again succeeded (guessing since just modifying one field at a time). Have run on dev/stage/prod.
The only remaining difference is a redundant index:
> SELECT * FROM sys.schema_redundant_indexes WHERE table_name LIKE 'repository'
******************** 1. row *********************
table_schema: treeherder
table_name: repository
redundant_index_name: idx_name
redundant_index_columns: name
redundant_index_non_unique: 1
dominant_index_name: repository_name_13592bc8829e9cbe_uniq
dominant_index_columns: name
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `treeherder`.`repository` DROP INDEX `idx_name`
1 rows in set
Dropped from dev/stage/prod using the statement from `sql_drop_index`.
Status: ASSIGNED → RESOLVED
Closed: 8 years ago
Resolution: --- → FIXED
You need to log in
before you can comment on or make changes to this bug.
Description
•