999119 - [Mkt] Clean up the database after guillotine

Status: RESOLVED FIXED

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

Description

[Wil Clouser [:clouserw]]

+++ This bug was initially created as a clone of Bug #999117 +++

This is a big bug and could use some sub bugs, but basiacally this is for getting the database into the shape you'd expect it to be for Marketplace.  Off the top of my head:

* Reviewer Stats (be careful about this, as theme reviews have been going into Marketplace). Let Reviewers know to expect a drop in scores.
* Remove add-ons from Marketplace
* Remove add-ons stats
* Remove _amo postfixed tables
* Remove any other AMO specific tables (there are a lot of them)

Comment 1

[Rob Hudson [:robhudson]]

I created an etherpad to add SQL commands to as we remove code that have models associated with them:

https://etherpad.mozilla.org/bug-999119

Comment 2

[Rob Hudson [:robhudson]]

Committed some of the migrations for code that has been removed:
https://github.com/mozilla/zamboni/commit/a16cb6c3

Comment 3

[Jeremy Orem [:oremj]]

There are a few large tables that we may not use:

* addons_collections: does marketplace use this at all?
* oauth_nonce: up to 1G, maybe it needs a cleanup script?
* file_uploads: probably needs cleaned up, but should clean the FS at the same time
* users/auth_user: do we need both? Is there a subset of mkt users or do we want them all?
* personas: guessing this is not needed?
* synced_addons_collections/colletions: needed?

Comment 4

[Rob Hudson [:robhudson]]

(In reply to Jeremy Orem [:oremj] from comment #3)
> There are a few large tables that we may not use:
> 
> * addons_collections: does marketplace use this at all?
> * personas: guessing this is not needed?
> * synced_addons_collections/colletions: needed?

This commit removes the above (plus more related tables):
https://github.com/mozilla/zamboni/commit/95c215b22b250f306cc75b3b02fe78e8a7e2e5bd

> * oauth_nonce: up to 1G, maybe it needs a cleanup script?

This is part of our oauth. Perhaps we need to add something to `mkt_gc` to garbage collect these when they are old?

> * file_uploads: probably needs cleaned up, but should clean the FS at the
> same time

I'd vote to do this when we do the add-ons cleanup. We'll need to clear the add-ons from the file system also, as well as related stuff like images, etc. I think it'll be tricky.

> * users/auth_user: do we need both? Is there a subset of mkt users or do we
> want them all?

I know Allen was saving this for after the merge and making sure everything was ok.

Needinfo ashort for oauth and auth_user.

Comment 5

[Rob Hudson [:robhudson]]

This was pushed out, removing more tables.
https://github.com/mozilla/zamboni/commit/95c215b2

Comment 6

[Allen Short [:ashort]]

We're ready to drop "auth_user", I believe -- the code doesn't use it at all now. We could indeed remove user accounts that were only used on AMO, but identifying them might be hard. Accounts with passwords were created on AMO but might be in use on marketplace.


oauth_nonce should indeed have a gc action associated with it - nonces expire after 10 minutes.

Comment 7

[Rob Hudson [:robhudson]]

(In reply to Allen Short [:ashort] from comment #6)
> oauth_nonce should indeed have a gc action associated with it - nonces
> expire after 10 minutes.

Filed bug 1016695.

Comment 8

[Rob Hudson [:robhudson]]

A few more removals incoming:
https://github.com/mozilla/zamboni/commit/0e2ef4b

Comment 9

[Rob Hudson [:robhudson]]

More cleanup, removing unused columns from the addons table:
https://github.com/mozilla/zamboni/commit/9c462cc

Comment 10

[Rob Hudson [:robhudson]]

More cleanup, all the various applications/appversions/etc tables:
https://github.com/mozilla/zamboni/commit/0bdd81d0

Also, another migration to remove tables that were removed with code long ago:
https://github.com/mozilla/zamboni/commit/0aee1160

Comment 11

[Rob Hudson [:robhudson]]

I believe all that's left is to clean out non-webapp extensions from the database tables itself, including file_uploads.

The approach I'm considering is to fix all of the cascades. Once fixed, we should be able to do `DELETE FROM addons WHERE addontype_id != 11` and all the database columns are correctly removed or set NULL, etc.

This will need to be well tested, obviously. Translations may need to be handled separately and if so, done before we remove the addons.

The alternative approach would be to iterate over the apps in Python and start removing the relationships manually. What worries be about this is caching, the various `with_deleted` model managers, and other bugs we've discovered about FK relation managers.

Comment 12

[Rob Hudson [:robhudson]]

File upload cleaning:
https://github.com/mozilla/zamboni/commit/3973716

Comment 13

[Rob Hudson [:robhudson]]

Added command to remove addons from the database. This will require careful testing on -dev to ensure we're removing all the right columns in the various tables which I plan to do in the next couple days.
https://github.com/mozilla/zamboni/commit/840de4d

Comment 14

[Rob Hudson [:robhudson]]

The script was run on dev with no apparent problems. It took about 27m to complete.

This will need to be run manually on stage and prod.

Comment 15

[Iulian Timis]

Please add STR here or mark it with [qa-] if no QA is needed.