Closed
Bug 1304338
Opened 8 years ago
Closed 8 years ago
Differences in the build_platform table schema between environments
Categories
(Tree Management :: Treeherder, defect, P1)
Tree Management
Treeherder
Tracking
(Not tracked)
RESOLVED
FIXED
People
(Reporter: emorley, Assigned: emorley)
References
Details
Differences:
* Heroku stage contains a duplicate composite primary key on (`os_name`,`platform`,`architecture`)
* Charset/collation varies (bug 1303767)
# SCL3 prod / Heroku prototype:
CREATE TABLE `build_platform` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`os_name` varchar(25) COLLATE utf8_bin NOT NULL,
`platform` varchar(100) COLLATE utf8_bin NOT NULL,
`architecture` varchar(25) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `<REMOVED>` (`os_name`,`platform`,`architecture`),
KEY `<REMOVED>` (`os_name`),
KEY `<REMOVED>` (`platform`),
KEY `<REMOVED>` (`architecture`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
# SCL3 stage:
CREATE TABLE `build_platform` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`os_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`platform` varchar(100) NOT NULL,
`architecture` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `<REMOVED>` (`os_name`,`platform`,`architecture`),
KEY `<REMOVED>` (`os_name`),
KEY `<REMOVED>` (`platform`),
KEY `<REMOVED>` (`architecture`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# Heroku stage:
CREATE TABLE `build_platform` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`os_name` varchar(25) COLLATE utf8_bin NOT NULL,
`platform` varchar(100) COLLATE utf8_bin NOT NULL,
`architecture` varchar(25) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `<REMOVED>` (`os_name`,`platform`,`architecture`),
UNIQUE KEY `<REMOVED>` (`os_name`,`platform`,`architecture`),
KEY `<REMOVED>` (`os_name`),
KEY `<REMOVED>` (`platform`),
KEY `<REMOVED>` (`architecture`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
# Vagrant:
CREATE TABLE `build_platform` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`os_name` varchar(25) NOT NULL,
`platform` varchar(100) NOT NULL,
`architecture` varchar(25) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `<REMOVED>` (`os_name`,`platform`,`architecture`),
KEY `<REMOVED>` (`os_name`),
KEY `<REMOVED>` (`platform`),
KEY `<REMOVED>` (`architecture`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Assignee | ||
Comment 1•8 years ago
|
||
(In reply to Ed Morley [:emorley] from comment #0)
> * Heroku stage contains a duplicate composite primary key on (`os_name`,`platform`,`architecture`)
This appears to be due to bug 1291882 comment 11.
Blocks: 1291882
Assignee | ||
Comment 2•8 years ago
|
||
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 `build_platform`
--
DROP TABLE IF EXISTS `build_platform`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `build_platform` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`os_name` varchar(25) COLLATE utf8_bin NOT NULL,
`platform` varchar(100) COLLATE utf8_bin NOT NULL,
- `architecture` varchar(25) COLLATE utf8_bin NOT NULL,
+ `architecture` varchar(25) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `<INDEX_NAME>` (`os_name`,`platform`,`architecture`),
KEY `<INDEX_NAME>` (`os_name`),
KEY `<INDEX_NAME>` (`platform`),
KEY `<INDEX_NAME>` (`architecture`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
On prod `select * from treeherder.build_platform where architecture is null` returned zero rows, so we should be able to just run:
ALTER TABLE treeherder.build_platform MODIFY `architecture` varchar(25) NOT NULL;
Assignee | ||
Comment 3•8 years ago
|
||
Applied to prod+stage+dev.
Before:
CREATE TABLE `build_platform` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`os_name` varchar(25) COLLATE utf8_bin NOT NULL,
`platform` varchar(100) COLLATE utf8_bin NOT NULL,
`architecture` varchar(25) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `build_platform_os_name_67b17eeee9812d2e_uniq` (`os_name`,`platform`,`architecture`),
KEY `idx_os_name` (`os_name`),
KEY `idx_platform` (`platform`),
KEY `idx_architecture` (`architecture`)
) ENGINE=InnoDB AUTO_INCREMENT=246 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
After:
CREATE TABLE `build_platform` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`os_name` varchar(25) COLLATE utf8_bin NOT NULL,
`platform` varchar(100) COLLATE utf8_bin NOT NULL,
`architecture` varchar(25) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `build_platform_os_name_67b17eeee9812d2e_uniq` (`os_name`,`platform`,`architecture`),
KEY `idx_os_name` (`os_name`),
KEY `idx_platform` (`platform`),
KEY `idx_architecture` (`architecture`)
) ENGINE=InnoDB AUTO_INCREMENT=246 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
Assignee: nobody → emorley
Status: NEW → RESOLVED
Closed: 8 years ago
Resolution: --- → FIXED
You need to log in
before you can comment on or make changes to this bug.
Description
•