In addition to the general collation/charset differences across multiple DBs (bug 1303767), there are several differences between Vagrant and the SCL3 prod schema for the bugscache table: CREATE TABLE `bugscache` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `status` varchar(64) NOT NULL, - `resolution` varchar(64) NOT NULL, - `summary` varchar(255) NOT NULL, - `crash_signature` longtext NOT NULL, - `keywords` longtext NOT NULL, - `os` varchar(64) NOT NULL, - `modified` datetime(6) DEFAULT NULL, + `id` int(11) NOT NULL DEFAULT '0', + `status` varchar(64) COLLATE utf8_bin DEFAULT NULL, + `resolution` varchar(64) COLLATE utf8_bin DEFAULT NULL, + `summary` varchar(255) COLLATE utf8_bin NOT NULL, + `crash_signature` mediumtext COLLATE utf8_bin, + `keywords` mediumtext COLLATE utf8_bin, + `os` varchar(64) COLLATE utf8_bin DEFAULT NULL, + `modified` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `<REMOVED>` (`status`), KEY `<REMOVED>` (`resolution`), FULLTEXT KEY `idx_summary` (`summary`), FULLTEXT KEY `idx_crash_signature` (`crash_signature`), FULLTEXT KEY `idx_keywords` (`keywords`), FULLTEXT KEY `idx_all_full_text` (`summary`,`crash_signature`,`keywords`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; Notably: * Auto increment vs default of '0' for `id` * mediumtext vs longtext for `crash_signature`/`keywords` * datetime vs datetime(6) for `modified`

The BugsCache model is defined as: class Bugscache(models.Model): id = models.AutoField(primary_key=True) status = models.CharField(max_length=64, blank=True, db_index=True) resolution = models.CharField(max_length=64, blank=True, db_index=True) summary = models.CharField(max_length=255) crash_signature = models.TextField(blank=True) keywords = models.TextField(blank=True) os = models.CharField(max_length=64, blank=True) modified = models.DateTimeField(null=True, blank=True)

Apart from Heroku stage/SCL3 prod (which are based from each other), every other environment has a slightly different schema :-( # SCL3 prod / Heroku stage: CREATE TABLE `bugscache` ( `id` int(11) NOT NULL DEFAULT '0', `status` varchar(64) COLLATE utf8_bin DEFAULT NULL, `resolution` varchar(64) COLLATE utf8_bin DEFAULT NULL, `summary` varchar(255) COLLATE utf8_bin NOT NULL, `crash_signature` mediumtext COLLATE utf8_bin, `keywords` mediumtext COLLATE utf8_bin, `os` varchar(64) COLLATE utf8_bin DEFAULT NULL, `modified` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `<REMOVED>` (`status`), KEY `<REMOVED>` (`resolution`), FULLTEXT KEY `idx_summary` (`summary`), FULLTEXT KEY `idx_crash_signature` (`crash_signature`), FULLTEXT KEY `idx_keywords` (`keywords`), FULLTEXT KEY `idx_all_full_text` (`summary`,`crash_signature`,`keywords`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; # SCL3 stage: CREATE TABLE `bugscache` ( `id` int(11) NOT NULL DEFAULT '0', `status` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `resolution` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `summary` varchar(255) DEFAULT NULL, `crash_signature` mediumtext, `keywords` mediumtext, `os` varchar(64) DEFAULT NULL, `modified` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `<REMOVED>` (`status`), KEY `<REMOVED>` (`resolution`), FULLTEXT KEY `idx_summary` (`summary`), FULLTEXT KEY `idx_crash_signature` (`crash_signature`), FULLTEXT KEY `idx_keywords` (`keywords`), FULLTEXT KEY `idx_all_full_text` (`summary`,`crash_signature`,`keywords`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # Heroku prototype: CREATE TABLE `bugscache` ( `id` int(10) unsigned NOT NULL, `status` varchar(64) COLLATE utf8_bin DEFAULT NULL, `resolution` varchar(64) COLLATE utf8_bin DEFAULT NULL, `summary` varchar(255) COLLATE utf8_bin NOT NULL, `crash_signature` mediumtext COLLATE utf8_bin, `keywords` mediumtext COLLATE utf8_bin, `os` varchar(64) COLLATE utf8_bin DEFAULT NULL, `modified` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `<REMOVED>` (`status`), KEY `<REMOVED>` (`resolution`), FULLTEXT KEY `idx_summary` (`summary`), FULLTEXT KEY `idx_crash_signature` (`crash_signature`), FULLTEXT KEY `idx_keywords` (`keywords`), FULLTEXT KEY `idx_all_full_text` (`summary`,`crash_signature`,`keywords`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; # Vagrant: CREATE TABLE `bugscache` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` varchar(64) NOT NULL, `resolution` varchar(64) NOT NULL, `summary` varchar(255) NOT NULL, `crash_signature` longtext NOT NULL, `keywords` longtext NOT NULL, `os` varchar(64) NOT NULL, `modified` datetime(6) DEFAULT NULL, PRIMARY KEY (`id`), KEY `<REMOVED>` (`status`), KEY `<REMOVED>` (`resolution`), FULLTEXT KEY `idx_summary` (`summary`), FULLTEXT KEY `idx_crash_signature` (`crash_signature`), FULLTEXT KEY `idx_keywords` (`keywords`), FULLTEXT KEY `idx_all_full_text` (`summary`,`crash_signature`,`keywords`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I spotted a few mistakes in the model, which I'll fix first before bringing prod in sync with Vagrant (some of the inconsistencies will actually get fixed when the migration gets run, since it will clobber the differences on prod).

Commit pushed to master at https://github.com/mozilla/treeherder https://github.com/mozilla/treeherder/commit/573de7c7ce19aad94aba846d357b65d21a90cc07 Bug 1304047 - Clean up the Bugscache schema * Switches `id` from an auto_increment to an unsigned int. * Stops allowing null/blank values for status/modified. * Drops unused fulltext indexes.

The commit that landed fixed some of the differences, but there were actually a few remaining: CREATE TABLE `bugscache` ( `id` int(10) unsigned NOT NULL, - `status` varchar(64) COLLATE utf8_bin NOT NULL, - `resolution` varchar(64) COLLATE utf8_bin NOT NULL, + `status` varchar(64) COLLATE utf8_bin DEFAULT NULL, + `resolution` varchar(64) COLLATE utf8_bin DEFAULT NULL, `summary` varchar(255) COLLATE utf8_bin NOT NULL, - `crash_signature` longtext COLLATE utf8_bin NOT NULL, - `keywords` longtext COLLATE utf8_bin NOT NULL, - `os` varchar(64) COLLATE utf8_bin NOT NULL, + `crash_signature` mediumtext COLLATE utf8_bin, + `keywords` mediumtext COLLATE utf8_bin, + `os` varchar(64) COLLATE utf8_bin DEFAULT NULL, `modified` datetime(6) NOT NULL, PRIMARY KEY (`id`), KEY `<NAME>` (`status`), KEY `<NAME>` (`resolution`), FULLTEXT KEY `<NAME>` (`summary`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Fixes the differences listed in comment 6 :-)

Applied to dev/stage/prod.