1303763 - Resolve differences between Vagrant/prod DB schemas

Status: RESOLVED FIXED

(Tree Management :: Treeherder, defect, P1)

Description

[Ed Morley [:emorley]]

As part of working on bug 1279169, I've discovered there are several differences between the DB schemas used in stage vs prod vs the local vagrant environment.

Differences between environments include:
* Missing indexes
* Duplicate indexes
* Different charsets/collations
* Different default values / NULL vs NOT NULL
* Different character field lengths
* Missing/superfluous tables
* Missing constraints

I suspect the cause of these are a mixture of:
* Different server options (eg default collation)
* Manual changes run against one environment but not another
* Automatic changes (ie: using migrations files) that timed out in one environment but not another
* The original stage/prod schema being from the in-repo SQL rather than having been generated by the initial Django migration

I've dumped the schema from all environments (including that generated by a fresh `vagrant up`), and have tweaked them slightly to make the diffs cleaner (eg by removing the randomly generated index names / timestamps / auto-increment current value etc).

Please can we all have a quick look through these to find individual issues, and file dependant bugs to resolve?

Comment 1

[Ed Morley [:emorley]]

Attachment: Schema: Vagrant

Comment 2

[Ed Morley [:emorley]]

Attachment: Schema: SCL3 Stage

Comment 3

[Ed Morley [:emorley]]

Attachment: Schema: Heroku Prototype

Comment 4

[Ed Morley [:emorley]]

Attachment: Schema: Heroku Stage

The Heroku stage schema should match SCL3 prod, since it was not long ago copied from it. However there are already differences (due to default collation).

--

I meant to mention - for all environments, I only dumped the following tables (to get a mix of old vs new):
* treeherder(_stage)?
* stylo
* mozilla_inbound(_jobs_1)?

Comment 5

[Ed Morley [:emorley]]

Attachment: Schema: SCL3 Prod

Comment 6

[Ed Morley [:emorley]]

Attachment: Diff: Vagrant -> SCL3 Prod

Comment 7

[Ed Morley [:emorley]]

Attachment: Diff: SCL3 Stage -> SCL3 Prod

Comment 8

[Ed Morley [:emorley]]

Attachment: Diff: Heroku Stage -> SCL3 Prod

Comment 9

[Ed Morley [:emorley]]

Attachment: Schema: Vagrant

Comment 10

[Ed Morley [:emorley]]

Attachment: Schema: Heroku Prod

Updated schema dumps. Taken when prod was on 67f83a2, so I reset Vagrant to that revision too.

I've only included the main `treeherder` DB this time, since the others are going away soon with the datasource->ORM migration.

Comment 11

[Ed Morley [:emorley]]

Attachment: Diff: Vagrant -> Heroku Prod

Still quite a lot of differences, including in tables that have only recently been added using the ORM :-(

The "let's manually add things that are hard to do via migrations" seems prone to introducing discrepancies.

Comment 12

[Ed Morley [:emorley]]

Narrowing scope to be about Vagrant vs prod - I think we should just reset stage to a recent prod snapshot once the datasource->ORM migration work is complete.

Comment 13

[Ed Morley [:emorley]]

Ah so the `datetime` vs `datetime(6)` differences are due to :
https://docs.djangoproject.com/en/1.8/ref/databases/#fractional-seconds-support-for-time-and-datetime-fields
https://github.com/django/django/commit/22da5f8817ffff3917bcf8a652dce84f382c9202#diff-915ba964e993f66ed1159d2a9d375862R40

ie: Tables that were created before Django 1.8 would have used `datetime` instead.

Comment 14

[Ed Morley [:emorley]]

Attachment: Schema: Vagrant

Comment 15

[Ed Morley [:emorley]]

Attachment: Schema: Prod

Comment 16

[Ed Morley [:emorley]]

Attachment: Diff: Vagrant -> Prod

Comment 17

[Ed Morley [:emorley]]

Attachment: Schema: Vagrant

Comment 18

[Ed Morley [:emorley]]

Attachment: Schema: Prod

Comment 19

[Ed Morley [:emorley]]

Attachment: Diff: Vagrant -> Prod

Updated schema dumps/diff after the fixes in the 29 dep bugs.

Aside from the datetime vs datetime(6) differences on the failure_line table (which we've decided to ignore, bug 1304062 comment 6), the schema on prod now matches that in the Vagrant environment.

Please let's try to keep it that way! ;-)