1175691 - [tracking] Investigate db inconsistencies between docker/dev and stage/prod.

Status: RESOLVED FIXED

(Marketplace Graveyard :: Code Quality, defect, P1)

Description

[Rob Hudson [:robhudson]]

Docker bypasses migrations and creates all the tables only using syncdb. Dev was also created in this way when fakedata started. Stage and production have been running the migrations all along. Because of this docker/dev and stage/prod table definitions do not match which makes writing migrations difficult. (E.g. when trying to alter a column that is a foreign key to addons.id and addons.id is signed on dev but unsigned on prod)

This bug is to investigate other subtle differences and hopefully come up with a plan for reducing the differences so dev and local development more closely matches production.

Comment 1

[Rob Hudson [:robhudson]]

See also:
https://github.com/mozilla/zamboni/blob/master/scripts/deploy_fake_data.sh
and
https://github.com/mozilla/zamboni/blob/master/bin/docker_run.sh

Comment 2

[Rob Hudson [:robhudson]]

The Django bug for making AutoFields unsigned is entertaining:
https://code.djangoproject.com/ticket/56

Comment 3

[Chuck Harmston [:chuck]]

I tied myself into knots today because of this issue, as well. This makes it impossible to write functioning migrations when foreign keys are involved.

Nominate this as a P1.

Comment 4

[Chuck Harmston [:chuck]]

905785

Comment 5

[Rob Hudson [:robhudson]]

As discussed during the work week between mat and I, the path of least resistance is to move the db schema to match what Django's syncdb outputs.

This may result in more changes than just the signed vs unsigned integers but it will ensure parity across all stages of our servers (from docker to dev to prod).

We may find tables we no longer use and can drop as well, e.g. auth_users?

Comment 6

[Chuck Harmston [:chuck]]

There are two fixes that need to happen here:

1) an emergency fix to unblock people. This probably means copying the stage DB to dev, though that's pending consultation with ops. This is tentatively scheduled to happen on Tuesday afternoon after the push or on Wednesday morning.

2) a "right way" fix, as Rob proposes in comment 5: moving our database schema to match Django's syncdb outputs. This is a much more ambitious project that is obviously wide-reaching. It's also a blocker to bug  905785 and bug 1078531. It's important to take our time to do this right, and doing the emergency fix gives us the breathing room to do so.

Comment 7

[Chuck Harmston [:chuck]]

Emergency fix has been completed, thanks to help from :jason and :jlaz. Holler if there are any concerns.

Comment 8

[Allen Short [:ashort]]

A migration has been added to remove tables/columns that don't exist in the django-generated schema:

https://github.com/mozilla/zamboni/blob/master/migrations/922-leave-the-past-behind.sql

Data from the production database should load cleanly into the new schema now.

Comment 9

[Rob Hudson [:robhudson]]

https://github.com/mozilla/zamboni/pull/3182

Added some unique constraints and indexes to Python to reflect what is currently in production.

Comment 10

[Allen Short [:ashort]]

New DB schema is in production.