1251610 - Delete apps from addons table

Status: RESOLVED WONTFIX

(Cloud Services :: Operations: AMO, task)

Description

[Jorge Villalobos [:jorgev] (he/him)]

I was investigating the use of version.deleted for Andrew, and ended up with 1300 versions that used it. After looking into them a little more, I realized they were old app data from the Marketplace days, and version.deleted wasn't used for add-ons at all. We should get rid of all the app data so it's easier to identify code and DB entities that are no longer in use.

We should delete all entries in the addons table that have addontype_id = 11, and other data associated with them (versions, files, etc.).

Comment 1

[Jason Thomas [:jason]]

Let's do this after our production push.

If someone from the dev team can provide queries that would be helpful.

Comment 2

[:wezhou]

"delete all entries in the addons table that have addontype_id = 11" is straightforward, but how should we delete "other data associated with them (versions, files, etc.)"?

Is there a command that safely deletes entries with addontype_id = 11 and their associated data?

Comment 3

[Jason Thomas [:jason]]

The table schema for versions, files, etc should have a constraint to cascade on delete. It should just work but we should test on dev and stage first. If we are really paranoid we can try running it on a prod snapshot instance first.

Comment 4

[:wezhou]

Tested on -dev. It looks like at least one of the foreign key constraints doesn't have the "cascade on delete" option, which prevents the rows from being deleted.

MySQL [addons_dev_allizom_org]> delete from addons where addontype_id = 11;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`addons_dev_allizom_org`.`users_install`, CONSTRAINT `addon_id_refs_id` FOREIGN KEY (`addon_id`) REFERENCES `addons` (`id`))

Because the "cascade on delete" option is not easily set on existing constraints, we will need to get dev opinion on how to do this safely and effectively. I'll try to talk to them about this.

Comment 5

[Mathieu Pillard [:mat]]

Most of our tables do *not* have on delete cascade, because the cascade is handled by django, not MySQL. Some do because they were created manually but we should always assume it's not the case.

That being said, the `users_install` table should have been dropped in https://github.com/mozilla/addons-server/commit/6119cdedad97c38ce48dc34389fcb67140f0a1a9 so maybe it's safe to try again ?

Comment 6

[Jeremy Orem [:oremj]]

No action in a long time, closing as WONTFIX. Please reopen if this is still needed.