435149 - SQL run needed to fix mis-tagged "Miscellaneous" add-ons

Status: VERIFIED FIXED

(addons.mozilla.org Graveyard :: Public Pages, defect)

Description

[Stephen Donner [:stephend] Not actively reading bugmail]

(Found this while testing bug 425500, btw.)

I swear I thought this already was filed; forgive the ackward summary!

Summary: A couple add-ons with "Miscellaneous" as a subcategory within "Themes" don't display their category links.

Steps to Reproduce:

1. Load https://preview.addons.mozilla.org/en-US/firefox/browse/type:2/cat:all?sort=name
2. Click on "708090" (https://preview.addons.mozilla.org/en-US/firefox/addon/130) or "708090-lite" (https://preview.addons.mozilla.org/en-US/firefox/addon/131)

Actual Results:

In both sets of URLs the category name to which each add-on belongs is missing.

Expected Results:

According to https://preview.addons.mozilla.org/en-US/firefox/developers/index/130 and https://preview.addons.mozilla.org/en-US/firefox/developers/index/131, both add-ons are in the "Miscellaneous" category.

Comment 1

[Basil Hashem [:baz]]

Good catch Stephen!

The problem stems from the fact that these two themes didn't have categories associated with them. Probably due to previous migration issues with Remora.

On production (and preview) for both of these themes I've added them to the Miscellaneous category. And preview is now showing a category for these.

But to solve this problem more generally. I think we have an issue with mismatched Miscellaneous fields. For example, doing a query such as the one below shows a bunch of add-ons that appear in the wrong Miscellaneous category

select addons.id,translations.localized_string,addons_tags.tag_id from addons
	left join translations on addons.name=translations.id
	left join addons_tags on addons.id=addons_tags.addon_id
where addons.addontype_id=2 and addons_tags.tag_id in (73,49,50)

Note that tag id 73, 49 and 50 are meant to be EXTENSION categories not theme categories.


Similarly, if you run the following:
select addons.id,translations.localized_string,addons_tags.tag_id from addons
	left join translations on addons.name=translations.id
	left join addons_tags on addons.id=addons_tags.addon_id
where addons.addontype_id=1 and addons_tags.tag_id in (21,59,60)

You'll find a bunch of extensions that appear in the Misc theme category.

Can someone please write some SQL to do the following replacement:

if type is theme and tag_id is 73, replace with tag_id 59 (for Firefox) [shouldn't be any]
if type is theme and tag_id is 49, replace with tag_id 59 (for SeaMonkey)
if type is theme and tag_id is 50, replace with tag_id 60 (for Thunderbird)


if type is ext and tag_id is 21, replace with tag_id 73 (for Firefox)
if type is ext and tag_id is 59, replace with tag_id 49 (for SeaMonkey)
if type is ext and tag_id is 60, replace with tag_id 50 (for Thunderbird)

Thanks.

Comment 3

[Fred Wenzel [:wenzel]]

Attachment: SQL to fix the categories

This SQL file should do the trick. Mike, can you take a quick look at it just so I am not hosing our tags table on accident ;)

First I am trying to UPDATE the existing tag assignments according to Basil's rules above. However, this fails with a duplicate key error if that add-on also already is in the category that we want to assign it to now. Hence the IGNORE in the UPDATE statement to at least get the ones in that work.

Then I DELETE all rows still matching the rule above, because if they weren't just UPDATEd, they must be the ones that caused a duplicate key error.

Does that make sense?

Comment 4

[Michael Morgan [:morgamic]]

Comment on attachment 322919 [details]
SQL to fix the categories

I'm scared.

Comment 5

[Fred Wenzel [:wenzel]]

So am I which is why you should look at it and also run it on your dev DB and make sure it doesn't blow away an unreasonable amount of rows ;)

But it worked fine for me so chances are it's good like this.

Comment 6

[Michael Morgan [:morgamic]]

Ok, will ponder it while sleeping.  Review in progresszzzz.z.....

Comment 7

[Michael Morgan [:morgamic]]

Comment on attachment 322919 [details]
SQL to fix the categories

This works for me.

Comment 8

[Michael Morgan [:morgamic]]

We need to add this to the deployment bug.

Comment 9

[Michael Morgan [:morgamic]]

Attachment: sql output, just fyi

Comment 10

[Stephen Donner [:stephend] Not actively reading bugmail]

Verified FIXED on preview, using the URLs in comment 0; both now say "Themes | Miscellaneous".