Page MenuHomePhabricator

Replicate ores_classification and ores_model tables in labs
Closed, ResolvedPublic

Description

We talked about it in the wikimania but @yuvipanda told me that due to T138450: maintain-replicas.pl unmaintained, unmaintainable we can't have it but since the blocker got resolved, I'm guessing it's okay now.

Details

Related Gerrit Patches:

Event Timeline

Restricted Application added a project: Cloud-Services. · View Herald TranscriptOct 18 2016, 5:45 PM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript
Ladsgroup renamed this task from Replicate ores_classification and ores_model on labs to Replicate ores_classification and ores_model tables in labs.Oct 18 2016, 5:45 PM
Halfak added a subscriber: Halfak.Nov 10 2016, 3:49 PM

@jcrespo, I see that this is placed in the "Blocked external/Not db team" on the DBA board. Can you clarify who you think ought to own this task?

Maybe we should be talking to @chasemp and @madhuvishy about getting this done... ?

I believe now that it is unblocked, the tables are actually replicated, so labs team or whoever maintains the views needs to enable them to be visible for users.

I am standing by in case there is some support needed about transmission from production, but I believe my part (replication itself) was done/enabled back in the day when the tables were created.

I can confirm they are in labs:

$ mysql -h labsdb1001 wikidatawiki -e "SELECT * FROM ores_classification LIMIT 10"
+----------+-----------+-------------+-------------+-------------------+--------------------+
| oresc_id | oresc_rev | oresc_model | oresc_class | oresc_probability | oresc_is_predicted |
+----------+-----------+-------------+-------------+-------------------+--------------------+
| 11098302 | 374419683 |           7 |           1 |             0.027 |                  0 |
| 11118144 | 374462067 |           7 |           1 |             0.004 |                  0 |
| 11118145 | 374462049 |           7 |           1 |             0.024 |                  0 |
| 11118146 | 374462070 |           7 |           1 |             0.161 |                  0 |
| 11118147 | 374462080 |           7 |           1 |             0.147 |                  0 |
| 11118148 | 374462090 |           7 |           1 |             0.022 |                  0 |
| 11118149 | 374462101 |           7 |           1 |             0.033 |                  0 |
| 11118150 | 374462103 |           7 |           1 |             0.007 |                  0 |
| 11118151 | 374462107 |           7 |           1 |             0.025 |                  0 |
| 11118152 | 374462087 |           7 |           1 |             0.005 |                  0 |
+----------+-----------+-------------+-------------+-------------------+--------------------+

Great! Thanks. Resolving

Halfak closed this task as Resolved.Nov 10 2016, 4:54 PM
Halfak moved this task from Monitor (long term) to Done on the Scoring-platform-team (Current) board.

Sorry I was not clear enough- the tables are replicated, but they are probably not exposed to the users yet, that is the part that someone has to figure out.

jcrespo reopened this task as Open.Nov 10 2016, 4:56 PM

@jcrespo is right:

ladsgroup@tools-bastion-03:~$ sql fawiki "select oresm_id from ores_model limit 1;"
ERROR 1146 (42S02) at line 1: Table 'fawiki_p.ores_model' doesn't exist

@jcrespo I see. Who should figure this out?

jcrespo added a subscriber: Krenair.EditedNov 10 2016, 5:39 PM

I think labs ops will know, but they are mostly unavailable/busy this week, so I will ask you to be patient.

@Krenair may know, but it is not something he has to do (but I think he helped with the new script).

I think what they do is patching https://phabricator.wikimedia.org/diffusion/OPUP/browse/production/modules/role/templates/labsdb/maintain-views.yaml (not 100% sure that is the right file), but amending that may speed up the process?

I do the amending.

Change 320804 had a related patch set uploaded (by Ladsgroup):
labs: add ores_classification and ores_model tables

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

Halfak assigned this task to Ladsgroup.Nov 10 2016, 6:13 PM
Halfak moved this task from Done to Review on the Scoring-platform-team (Current) board.
Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptNov 10 2016, 6:13 PM

@Krenair may know, but it is not something he has to do (but I think he helped with the new script).

The code and files are different but it is the same process as with the old script. You patch the config to include the table in the (in this case) full_views list, then run the script against the affected databases (on all labsdb mysql replica servers) to have it actually create the views (along with any other changes that may have been missed by changes to the file being made without the script being run). Without this process taking place, there is little point replicating anything as no users will be able to see it.

jcrespo added a comment.EditedNov 10 2016, 7:35 PM

@AlexMonk-WMF, this is not a replication or Database issue, as I just demonstrated.

Some of the subtasks you added here are not according to the description. https://phabricator.wikimedia.org/T50930#2585212 If you want to track LabsDB issues, I would kindly suggest creating a separate, specific task.

AlexMonk-WMF added a comment.EditedNov 10 2016, 8:01 PM

@AlexMonk-WMF, this is not a replication or Database issue, as I just demonstrated.

It's blocking users from accessing parts of the replicas, so it is relevant.

chasemp added a comment.EditedNov 11 2016, 2:25 PM

Are these https://gerrit.wikimedia.org/r/#/c/320804/2/modules/role/templates/labsdb/maintain-views.yaml views that need to be exposed in every DB or some specific DB?

Is there meant to be a table in enwiki and olowiki and on

AlexMonk-WMF added a comment.EditedNov 11 2016, 6:18 PM

Are these https://gerrit.wikimedia.org/r/#/c/320804/2/modules/role/templates/labsdb/maintain-views.yaml views that need to be exposed in every DB or some specific DB?
Is there meant to be a table in enwiki and olowiki and on

maintain-views doesn't check that, if a database is getting public views and a defined view's source exists in that database, it should create a view. These wikis have the table(s), so will get the view(s) if replication has been set up for them:

krenair@terbium:~$ ./foreachdbs.sh "select table_schema from information_schema.tables where table_name = 'ores_classification';"
enwiki
nlwiki
plwiki
ptwiki
trwiki
wikidatawiki
ruwiki
fawiki
krenair@terbium:~$ ./foreachdbs.sh "select table_schema from information_schema.tables where table_name = 'ores_model';"
enwiki
nlwiki
plwiki
ptwiki
trwiki
wikidatawiki
ruwiki
fawiki
chasemp added a comment.EditedNov 11 2016, 7:13 PM

sure, thanks, I understand. I meant something more like: is this a table to be exposed on a single wiki one-time, on these wikis one-time, on existing wiki's starting with these as X happens to add the table, or only new wiki's from here forward. The outline here is somewhat opaque, and I don't understand what the expectation is. I can certainly run maintain-views across for these DBs/tables as a one-time thing, but I don't expect to have it running as a cron until the new labsdb setup at this time.

on existing wiki's starting with these as X happens to add the table

We have 8 wikis and we're expanding. I expect to have more wikis with ores_* tables ready soon.

on existing wiki's starting with these as X happens to add the table

We have 8 wikis and we're expanding. I expect to have more wikis with ores_* tables ready soon.

Alright, I think I understand then thanks. I'll take a pass at this for the existing 8 and we can treat newcomers when they pop up in a new issue. Hopefully, soon we will not have this be as manual / adhoc.

For context there are two layers to this and neither is much good without the other. The title of Replicate ores_classification and ores_model tables in labs is probably most literally construed as completed now but we manage access to the underlying tables by creating views and have a permission model around it. Something like Expose x tables to labs users via labsdb would be more inclusive. This is not a big deal at all, just a thought on why I was momentarily confused about the scope here. The replication and views specifics are definitely inside baseball. We have a big week here and I think we can get to this wednesday 11/16 or so.

mark added a subscriber: mark.Nov 15 2016, 4:11 PM

@AlexMonk-WMF, this is not a replication or Database issue, as I just demonstrated.
Some of the subtasks you added here are not according to the description. https://phabricator.wikimedia.org/T50930#2585212 If you want to track LabsDB issues, I would kindly suggest creating a separate, specific task.

If it's inconvenient for @jcrespo to have this replication tracking task as a parent for this kind of Labs DB / views tasks, I don't see why we can't break those off onto a separate parent task indeed. Let's clarify that on T50930 as well.

Change 320804 merged by Rush:
labs: add ores_classification and ores_model tables

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

chasemp closed this task as Resolved.Nov 16 2016, 4:38 PM
--- /etc/maintain-views.yaml	2016-11-02 19:12:41.326458322 +0000
+++ /tmp/puppet-file20161116-18909-1ma54yp	2016-11-16 16:30:19.300683398 +0000
@@ -88,6 +88,8 @@
   - module_deps
   - msg_resource_links
   - namespaces
+  - ores_classification
+  - ores_model
   - page
   - page_broken
   - pagelinks

1> maintain-views --databases wikidatawiki enwiki nlwiki plwiki ptwiki trwiki ruwiki fawiki --table ores_classification --replace-all --debug
2
3> maintain-views --databases wikidatawiki enwiki nlwiki plwiki ptwiki trwiki ruwiki fawiki --table ores_model --replace-all --debug
4
5
62016-11-16 16:35:37,865 DEBUG Removing 0 dbs as sensitive
72016-11-16 16:35:37,872 INFO Full views for wikidatawiki:
82016-11-16 16:35:37,875 INFO [ores_classification]
92016-11-16 16:35:37,875 DEBUG SQL:
10 CREATE OR REPLACE
11 DEFINER=viewmaster
12 VIEW `wikidatawiki_p`.`ores_classification`
13 AS SELECT * FROM `wikidatawiki`.`ores_classification`;
14
152016-11-16 16:35:37,876 INFO Custom views for wikidatawiki:
162016-11-16 16:35:37,883 INFO Full views for fawiki:
172016-11-16 16:35:37,887 INFO [ores_classification]
182016-11-16 16:35:37,887 DEBUG SQL:
19 CREATE OR REPLACE
20 DEFINER=viewmaster
21 VIEW `fawiki_p`.`ores_classification`
22 AS SELECT * FROM `fawiki`.`ores_classification`;
23
242016-11-16 16:35:37,888 INFO Custom views for fawiki:
252016-11-16 16:35:37,893 INFO Full views for ptwiki:
262016-11-16 16:35:37,897 INFO [ores_classification]
272016-11-16 16:35:37,897 DEBUG SQL:
28 CREATE OR REPLACE
29 DEFINER=viewmaster
30 VIEW `ptwiki_p`.`ores_classification`
31 AS SELECT * FROM `ptwiki`.`ores_classification`;
32
332016-11-16 16:35:37,898 INFO Custom views for ptwiki:
342016-11-16 16:35:37,903 INFO Full views for plwiki:
352016-11-16 16:35:37,905 INFO [ores_classification]
362016-11-16 16:35:37,906 DEBUG SQL:
37 CREATE OR REPLACE
38 DEFINER=viewmaster
39 VIEW `plwiki_p`.`ores_classification`
40 AS SELECT * FROM `plwiki`.`ores_classification`;
41
422016-11-16 16:35:37,907 INFO Custom views for plwiki:
432016-11-16 16:35:37,912 INFO Full views for trwiki:
442016-11-16 16:35:37,915 INFO [ores_classification]
452016-11-16 16:35:37,915 DEBUG SQL:
46 CREATE OR REPLACE
47 DEFINER=viewmaster
48 VIEW `trwiki_p`.`ores_classification`
49 AS SELECT * FROM `trwiki`.`ores_classification`;
50
512016-11-16 16:35:37,918 INFO Custom views for trwiki:
522016-11-16 16:35:37,923 INFO Full views for enwiki:
532016-11-16 16:35:37,926 INFO [ores_classification]
542016-11-16 16:35:37,926 DEBUG SQL:
55 CREATE OR REPLACE
56 DEFINER=viewmaster
57 VIEW `enwiki_p`.`ores_classification`
58 AS SELECT * FROM `enwiki`.`ores_classification`;
59
602016-11-16 16:35:37,928 INFO Custom views for enwiki:
612016-11-16 16:35:37,934 INFO Full views for ruwiki:
622016-11-16 16:35:37,936 INFO [ores_classification]
632016-11-16 16:35:37,936 DEBUG SQL:
64 CREATE OR REPLACE
65 DEFINER=viewmaster
66 VIEW `ruwiki_p`.`ores_classification`
67 AS SELECT * FROM `ruwiki`.`ores_classification`;
68
692016-11-16 16:35:37,939 INFO Custom views for ruwiki:
702016-11-16 16:35:37,944 INFO Full views for nlwiki:
712016-11-16 16:35:37,946 INFO [ores_classification]
722016-11-16 16:35:37,946 DEBUG SQL:
73 CREATE OR REPLACE
74 DEFINER=viewmaster
75 VIEW `nlwiki_p`.`ores_classification`
76 AS SELECT * FROM `nlwiki`.`ores_classification`;
77
782016-11-16 16:35:37,948 INFO Custom views for nlwiki:
79
802016-11-16 16:36:25,804 INFO Full views for nlwiki:
812016-11-16 16:36:25,806 INFO [ores_model]
822016-11-16 16:36:25,806 DEBUG SQL:
83 CREATE OR REPLACE
84 DEFINER=viewmaster
85 VIEW `nlwiki_p`.`ores_model`
86 AS SELECT * FROM `nlwiki`.`ores_model`;
87
882016-11-16 16:36:25,811 INFO Custom views for nlwiki:
892016-11-16 16:36:25,816 INFO Full views for ruwiki:
902016-11-16 16:36:25,818 INFO [ores_model]
912016-11-16 16:36:25,818 DEBUG SQL:
92 CREATE OR REPLACE
93 DEFINER=viewmaster
94 VIEW `ruwiki_p`.`ores_model`
95 AS SELECT * FROM `ruwiki`.`ores_model`;
96
972016-11-16 16:36:25,821 INFO Custom views for ruwiki:
982016-11-16 16:36:25,826 INFO Full views for wikidatawiki:
992016-11-16 16:36:25,828 INFO [ores_model]
1002016-11-16 16:36:25,828 DEBUG SQL:
101 CREATE OR REPLACE
102 DEFINER=viewmaster
103 VIEW `wikidatawiki_p`.`ores_model`
104 AS SELECT * FROM `wikidatawiki`.`ores_model`;
105
1062016-11-16 16:36:25,830 INFO Custom views for wikidatawiki:
1072016-11-16 16:36:25,835 INFO Full views for enwiki:
1082016-11-16 16:36:25,837 INFO [ores_model]
1092016-11-16 16:36:25,837 DEBUG SQL:
110 CREATE OR REPLACE
111 DEFINER=viewmaster
112 VIEW `enwiki_p`.`ores_model`
113 AS SELECT * FROM `enwiki`.`ores_model`;
114
1152016-11-16 16:36:25,839 INFO Custom views for enwiki:
1162016-11-16 16:36:25,843 INFO Full views for plwiki:
1172016-11-16 16:36:25,845 INFO [ores_model]
1182016-11-16 16:36:25,845 DEBUG SQL:
119 CREATE OR REPLACE
120 DEFINER=viewmaster
121 VIEW `plwiki_p`.`ores_model`
122 AS SELECT * FROM `plwiki`.`ores_model`;
123
1242016-11-16 16:36:25,846 INFO Custom views for plwiki:
1252016-11-16 16:36:25,851 INFO Full views for ptwiki:
1262016-11-16 16:36:25,854 INFO [ores_model]
1272016-11-16 16:36:25,854 DEBUG SQL:
128 CREATE OR REPLACE
129 DEFINER=viewmaster
130 VIEW `ptwiki_p`.`ores_model`
131 AS SELECT * FROM `ptwiki`.`ores_model`;
132
1332016-11-16 16:36:25,856 INFO Custom views for ptwiki:
1342016-11-16 16:36:25,861 INFO Full views for fawiki:
1352016-11-16 16:36:25,864 INFO [ores_model]
1362016-11-16 16:36:25,864 DEBUG SQL:
137 CREATE OR REPLACE
138 DEFINER=viewmaster
139 VIEW `fawiki_p`.`ores_model`
140 AS SELECT * FROM `fawiki`.`ores_model`;
141
1422016-11-16 16:36:25,865 INFO Custom views for fawiki:
1432016-11-16 16:36:25,870 INFO Full views for trwiki:
1442016-11-16 16:36:25,872 INFO [ores_model]
1452016-11-16 16:36:25,872 DEBUG SQL:
146 CREATE OR REPLACE
147 DEFINER=viewmaster
148 VIEW `trwiki_p`.`ores_model`
149 AS SELECT * FROM `trwiki`.`ores_model`;
150
1512016-11-16 16:36:25,879 INFO Custom views for trwiki:

Let me know when more DBs are ready, this is not a big deal to do though it is unfortunately a manual command.