Page MenuHomePhabricator

Fix broken views in labs DB "ERROR 1356 -- references invalid table(s) or column(s)"
Closed, ResolvedPublic

Description

MariaDB [enwiki_p]> DESCRIBE `aft_article_answer`;
ERROR 1356 (HY000): View 'enwiki_p.aft_article_answer' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

It looks like I get a similar result for all aft_... and article_feedback_... tables.

Details

Related Gerrit Patches:
operations/puppet : productionmaintain-views.yaml: Remove unused views

Event Timeline

Halfak created this task.Dec 14 2016, 5:00 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 14 2016, 5:00 PM

Looks like povwatch_log has problems too

The underlying table simply no longer exists:

mysql:wikiadmin@db1089 [enwiki]> show create table povwatch_log;
ERROR 1146 (42S02): Table 'enwiki.povwatch_log' doesn't exist
mysql:wikiadmin@db1089 [enwiki]> show create table aft_article_answer;
ERROR 1146 (42S02): Table 'enwiki.aft_article_answer' doesn't exist

Related tickets: T103011, T92739, T54924

@Halfak what do you want to do with this ticket. The views are failing because the tables do not exist anymore as per: T153213#2873200
Thanks!

Halfak added a comment.Jun 9 2017, 2:46 PM

Seems like the views for tables that do not exist should get cleaned up, I suppose.

@chasemp is that something you guys can do?

Change 361034 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] maintain-views.yaml: Remove unsued views

https://gerrit.wikimedia.org/r/361034

Marostegui closed this task as Resolved.Jun 26 2017, 7:09 AM
Marostegui claimed this task.

I have cleaned up the views, the only pending thing would be to merge: https://gerrit.wikimedia.org/r/361034 whenever cloud-services-team want

I have cleaned up the views, the only pending thing would be to merge: https://gerrit.wikimedia.org/r/361034 whenever cloud-services-team want

Thanks @Marostegui

FYI there is a mechanism for this we put in with the last refactor, if we fixup maintain-views.yaml first we should be able to:

maintain-views --all-databases --clean --replace-all --debug

--clean               Clean out views from _p varient that are no longer
                      specified make changes.

In theory this works well! :) (I am updating that doc description as well...)

Oh thank you @chasemp! I didn't know that - next time I will clean it up with the script :-)

Change 361034 merged by Rush:
[operations/puppet@production] maintain-views.yaml: Remove unused views

https://gerrit.wikimedia.org/r/361034

A run post merge on labsdb1009:

sudo puppet agent --test (updates the repo)
time maintain-views --all-databases --clean --replace-all --debug

(took 5m55.561s)