Identify tools hosting databases on labsdb100[13] and notify maintainers
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	bd808
	Sep 5 2017, 11:08 PM

Description

Find out which tools/accounts are creating and updating user-managed databases co-located with the Wiki Replica data. These tools will need to be updated somehow to deal with the removal of user-managed dbs from the new Wiki Replica cluster.

User-managed databases will still be allowed on toolsdb. Some tools may be able to change their process to save working data there instead.
See T173511: Implement technical details and process for "datasets_p" on wikireplica hosts for discussion of a formal process for importing and mirroring curated datasets. This may be an option for other tools.
Some tools may be forced to rethink how they accomplish their work entirely.

Related Objects
Search...

Status	Assigned	Task
Resolved	jcrespo	T140788 Labs databases rearchitecture (tracking)
Resolved	bd808	T166402 Program 7 Outcome 3: data services
Resolved	bd808	T142807 Migrate all users to new Wiki Replica cluster and decommission old hardware
Resolved	bd808	T175096 Identify tools hosting databases on labsdb100[13] and notify maintainers

Event Timeline

bd808 created this task.Sep 5 2017, 11:08 PM

Initial list of accounts:

P5960 User created databases on labsdb100[13].eqiad.wmnet

2 SUBSTRING_INDEX3 , count4FROM information 5WHERE table_schema 6GROUP BY 7ORDER BY 8 9-- labsdb1001.eqiad.wmnet 10 11+------------- 12| tool 13+------------- 14| s51187 15| p50380g50497 16| p50380g50491 17| u2815 18| s51111 19| p50380g50692 20| p50380g50512 21| s52365 22| u3532 23| u4817 24| p50380g50816 25| s51454 26| p50380g50728 27| p50380g50921 28| u12219 29| p50380g50494 30| s51362 31| s51127 32| p50380g50551 33| s51892 34| u3617 35| s52690 36| s53311 37| s52467 38| u2041 39| s51755 40| s51154 41| s53381 42| s51698 43| p50380g50553 44| s52490 45| u3533 46| s51329 47| p50380g50900 48| s52037 49| u2402 50| u2248 51| u11049 52| u3356 53| u3945 54| u4301 55| u2043 56| u3949 57| s51306 58| u3290 59| u3570 60| p50380g40014 61| s51058 62| u2117 63| s51172 64| s53012 65| u2843 66| u10276 67| s51055 68| s52744 69| s52107 70| u2745 71| s51223 72| s51417 73| u2029 74| u4974 75| p50380g50592 76| s51206 77| s52524 78| s53151 79| u3214 80| p50380g50838 81| s51073 82| s53024 83| u3182 84| p50380g50824 85| s51334 86+------------- 8772 rows 88 89-- labsdb1003.eqiad.wmnet 90 91+------------- 92| tool 93+------------- 94| s51187 95| s51117 96| p50380g50497 97| p50380g50921 98| u2815 99| s51362 100| p50380g50491 101| s51111 102| p50380g50769 103| s52107 104| u3710 105| s51892 106| s52861 107| u4529 108| p50380g50729 109| p50380g50553 110| p50380g50692 111| s51072 112| s51580 113| s52957 114| p50380g50552 115| s53311 116| s51206 117| u2170 118| s51447 119| u3949 120| s51430 121| s53012 122| s51392 123| u3617 124| s51837 125| u2718 126| s52690 127| s52524 128| s51172 129+------------ 13035 rows

style="max-height: 27.6em;">

SELECT class="p">(table_schema, '_', 1) as tool class="p">(1) as tables _schema.TABLES regexp '^[psu][0-9]' tool tables desc; -+--------+ | tables | -+--------+ | 355 | | 159 | | 105 | | 79 | | 73 | | 54 | | 49 | | 49 | | 46 | | 35 | | 30 | | 26 | | 21 | | 21 | | 18 | | 17 | | 16 | | 14 | | 12 | | 11 | | 9 | | 8 | | 8 | | 7 | | 7 | | 6 | | 6 | | 6 | | 5 | | 5 | | 5 | | 4 | | 4 | | 4 | | 4 | | 4 | | 4 | | 3 | | 3 | | 3 | | 2 | | 2 | | 2 | | 2 | | 2 | | 2 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | -+--------+ in set (1.09 sec) -+--------+ | tables | -+--------+ | 688 | | 343 | | 261 | | 138 | | 89 | | 86 | | 73 | | 72 | | 39 | | 35 | | 33 | | 33 | | 25 | | 20 | | 9 | | 8 | | 7 | | 5 | | 4 | | 3 | | 3 | | 3 | | 2 | | 2 | | 2 | | 2 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | --+--------+ in set (1.36 sec)

bd808 moved this task from Backlog to Wiki replicas on the Data-Services board.Sep 11 2017, 9:58 PM

jcrespo moved this task from Triage to Blocked external/Not db team on the DBA board.Sep 12 2017, 4:21 PM

bd808 mentioned this in T176381: Grant tool "tool-db-usage" (s53508) ability to read full table_schema.TABLES contents.Sep 21 2017, 12:52 AM

zhuyifei1999 subscribed.Sep 21 2017, 4:07 PM

New tool https://tools.wmflabs.org/tool-db-usage/ can be used to see this data better. It also decodes the owners where possible which will make contacting them a bit easier.

Ricordisamoa subscribed.Sep 27 2017, 1:30 AM

bd808 edited projects, added cloud-services-team (Kanban); removed cloud-services-team.Oct 12 2017, 8:20 PM

In T175096#3589046, @bd808 wrote:

Initial list of accounts:
P5960

I've annotated the labsdb1001 section with wikitech usernames, and notes about broken links and probable maintainers, at P6184.
It should be suitable for (A) Sending a massmessage reminder to all maintainers (after a quick alphabetical shuffle and trim), (B) perhaps pointing the notified maintainers towards, so that they can easily connect the alphanumerical string to their own tool?
I'll do the same for labsdb1003 later in the week.

P6184 [Annotated] User created databases on labsdb1001.eqiad.wmnet

1	-- labsdb1001.eqiad.wmnet
2
3	\| s51187 \| 355 \|
4	Akoopal
5	Alphos
6	Cyberpower678
7	MZMcBride
8	Magnus Manske
9	Nemo bis
10	PiRSquared17
11	Se4598
12	TheDJ
13	\| p50380g50497 \| 159 \| --- PROBLEM: links to (not found) https://wikitech.wikimedia.org/wiki/User:liagent-php -- See also "tools.liangent-php (s51117)" on labsdb1003
14	Liangent
15	\| p50380g50491 \| 105 \|
16	Tb
17	\| u2815 \| 79 \|
18	Dispenser
19	\| s51111 \| 73 \|
20	Tb
21	\| p50380g50692 \| 54 \| --- PROBLEM: links to (not found) https://wikitech.wikimedia.org/wiki/User:Dplbot - should be https://tools.wmflabs.org/admin/tool/dplbot
22	Dispenser
23	JaGa
24	Russell Blau
25	\| p50380g50512 \| 49 \|
26	UNKNOWN
27	\| s52365 \| 49 \|
28	Mjbmr
29	\| u3532 \| 46 \|
30	DOESNT EXIST ANYMORE
31	\| u4817 \| 35 \|
32	DOESNT EXIST ANYMORE
33	\| p50380g50816 \| 30 \|
34	Kaldari
35	Mr.Z-man
36	Niharika29
37	\| s51454 \| 26 \|
38	DOESNT EXIST ANYMORE
39	\| p50380g50728 \| 21 \|
40	Jmorgan
41	\| p50380g50921 \| 21 \|
42	Kolossos
43	Retsam
44	Simon04
45	\| u12219 \| 18 \|
46	Kjschiroo
47	\| p50380g50494 \| 17 \|
48	UNKNOWN
49	\| s51362 \| 16 \|
50	Akoopal
51	Alphos
52	Cyberpower678
53	MZMcBride
54	Magnus Manske
55	Nemo bis
56	PiRSquared17
57	Se4598
58	TheDJ
59	\| s51127 \| 14 \|
60	Merl
61	\| p50380g50551 \| 12 \| -- probably User:Hersfold User:Martijn_Hoekstra User:Thehelpfulone User:Tparis per https://tools.wmflabs.org/admin/tool/unblock ?
62	UNKNOWN
63	\| s51892 \| 11 \|
64	Jkroll
65	Nosy
66	Tobias Gritschacher
67	\| u3617 \| 9 \|
68	Blahma
69	\| s52690 \| 8 \|
70	dispenser
71	\| s53311 \| 8 \|
72	Hall1467
73	\| s52467 \| 7 \|
74	MahmoudHashemi
75	Slaporte
76	\| u2041 \| 7 \|
77	Halfak
78	\| s51755 \| 6 \|
79	Blahma
80	\| s51154 \| 6 \|
81	Zache-tool
82	\| s53381 \| 6 \|
83	Tpt
84	\| s51698 \| 5 \|
85	Superyetkin
86	\| p50380g50553 \| 5 \| --- PROBLEM: links to (not found) https://wikitech.wikimedia.org/wiki/User:suggestbot -- should be https://tools.wmflabs.org/admin/tool/suggestbot
87	Bobo.03
88	Estuempfig
89	Halfak
90	Kjschiroo
91	Nettrom
92	\| s52490 \| 5 \|
93	Madhuvishy
94	MahmoudHashemi
95	Ori.livneh
96	Slaporte
97	\| u3533 \| 4 \|
98	Hasteur
99	\| s51329 \| 4 \|
100	BenKurtovic
101	\| p50380g50900 \| 4 \|
102	UNKNOWN
103	\| s52037 \| 4 \|
104	Anorange0409
105	Denny Vrandecic
106	Gwiki
107	Pasleim
108	\| u2402 \| 4 \|
109	Jmorgan
110	\| u2248 \| 4 \|
111	DOESNT EXIST ANYMORE
112	\| u11049 \| 3 \|
113	Ashwinpp
114	\| u3356 \| 3 \|
115	Nutz
116	\| u3945 \| 3 \|
117	Staeiou
118	\| u4301 \| 2 \|
119	Robertb
120	\| u2043 \| 2 \|
121	Giovanni Luca Ciampaglia
122	\| u3949 \| 2 \|
123	Computermacgyver
124	\| s51306 \| 2 \|
125	Eranroz
126	Kaldari
127	Lucas559
128	MusikAnimal
129	Niharika29
130	Samwilson
131	\| u3290 \| 2 \|
132	DOESNT EXIST ANYMORE
133	\| u3570 \| 2 \|
134	DOESNT EXIST ANYMORE
135	\| p50380g40014 \| 1 \|
136	UNKNOWN
137	\| s51058 \| 1 \|
138	Betacommand
139	\| u2117 \| 1 \|
140	DarTar
141	\| s51172 \| 1 \|
142	DOESNT EXIST ANYMORE
143	\| s53012 \| 1 \|
144	Lokal Profil
145	Sebastian Berlin (WMSE)
146	\| u2843 \| 1 \|
147	TTO
148	\| u10276 \| 1 \|
149	Baku
150	\| s51055 \| 1 \|
151	Anomie
152	\| s52744 \| 1 \|
153	BenKurtovic
154	\| s52107 \| 1 \| -- PROBLEM: https://tools.wmflabs.org/admin/tool/ptbot lists the non-existent https://wikitech.wikimedia.org/wiki/User:Tools.ptbot as a maintainer
155	Arash.pt
156	\| u2745 \| 1 \|
157	Tb
158	\| s51223 \| 1 \|
159	DOESNT EXIST ANYMORE
160	\| s51417 \| 1 \|
161	Dominic
162	Jeremyb
163	\| u2029 \| 1 \|
164	Yuvipanda
165	\| u4974 \| 1 \|
166	Awight
167	\| p50380g50592 \| 1 \|
168	UNKNOWN
169	\| s51206 \| 1 \|
170	Agx
171	Alchimista
172	Danilo
173	Diego Queiroz
174	He7d3r
175	HenriqueCrang
176	Raylton P. Sousa
177	Rodrigo Padula
178	Sir Lestaty de Lioncourt
179	\| s52524 \| 1 \|
180	Andrew Bogott
181	Coren
182	Yuvipanda
183	\| s53151 \| 1 \|
184	Samtar
185	\| u3214 \| 1 \|
186	CBM
187	\| p50380g50838 \| 1 \| -- probably User:Mr.Z-man per https://tools.wmflabs.org/admin/tool/geophotoreq
188	UNKNOWN
189	\| s51073 \| 1 \|
190	Legoktm
191	MZMcBride
192	Naraht
193	Niharika29
194	Tim Landscheidt
195	\| s53024 \| 1 \|
196	Soni
197	\| u3182 \| 1 \|
198	Ladsgroup
199	\| p50380g50824 \| 1 \| -- probably User:Jalexander per https://tools.wmflabs.org/admin/tool/contributionsurveyor
200	UNKNOWN
201	\| s51334 \| 1 \|
202	MZMcBride

Is it possible to make a dump of the merl DB and put it into the tool dir? Merl has been inactive for some time now, but if case that he returns, that dump might be useful.

In T175096#3711025, @Luke081515 wrote:

Is it possible to make a dump of the merl DB and put it into the tool dir? Merl has been inactive for some time now, but if case that he returns, that dump might be useful.

What is the database name? I cannot find anything if I look for merldb or merl on labsdb1001 and labsdb1003.
I would like to check a few things.

In T175096#3711831, @Marostegui wrote:

In T175096#3711025, @Luke081515 wrote:

Is it possible to make a dump of the merl DB and put it into the tool dir? Merl has been inactive for some time now, but if case that he returns, that dump might be useful.

What is the database name? I cannot find anything if I look for merldb or merl on labsdb1001 and labsdb1003.
I would like to check a few things.

The match in my list above leads to s51127 and from there to https://tools.wmflabs.org/tool-db-usage/owner/s51127 if that helps?

In T175096#3714074, @Quiddity wrote:

In T175096#3711831, @Marostegui wrote:

In T175096#3711025, @Luke081515 wrote:

Is it possible to make a dump of the merl DB and put it into the tool dir? Merl has been inactive for some time now, but if case that he returns, that dump might be useful.

What is the database name? I cannot find anything if I look for merldb or merl on labsdb1001 and labsdb1003.
I would like to check a few things.

The match in my list above leads to s51127 and from there to https://tools.wmflabs.org/tool-db-usage/owner/s51127 if that helps?

Yes, that are the DBs I meant, sorry.

I have made a backup of that directory and left it on:

[root@labsdb1001 06:23 /srv/tmp]
# pwd
/srv/tmp

[root@labsdb1001 06:24 /srv/tmp]
# ls -lh merlbot.tar.gz
-rw-r--r-- 1 root root 4.7M Oct 27 06:23 merlbot.tar.gz

Same place for labsdb1003 (as labsdb1001 will be rebooted).

As above, I've created the list for 1003 at

P6313 [Annotated] User created databases on labsdb1003.eqiad.wmnet

1	-- labsdb1003.eqiad.wmnet
2
3	\| s51187 \| 688 \|https://tools.wmflabs.org/admin/tool/xtools
4	Cyberpower678
5	Matthewrbowker
6	MusikAnimal
7	Samwilson
8	Technical 13
9	\| s51117 \| 343 \| --- PROBLEM: links to (not found) https://wikitech.wikimedia.org/wiki/User:liagent-php -- See also "tools.liangent-php (p50380g50497)" on labsdb1001
10	\| p50380g50497 \| 261 \| --- ibid
11	\| p50380g50921 \| 138 \| --- PROBLEM: links to (not found) https://tools.wmflabs.org/admin/tool/wikiminiatlas -- Probably https://tools.wmflabs.org/admin/tool/wiwosm but that leads to (404) https://tools.wmflabs.org/wiwosm/
12	Kolossos
13	Retsam
14	Simon04
15	\| u2815 \| 89 \|
16	Dispenser
17	\| s51362 \| 86 \|https://tools.wmflabs.org/admin/tool/erwin85
18	Akoopal
19	Alphos
20	Cyberpower678
21	MZMcBride
22	Magnus Manske
23	Nemo bis
24	PiRSquared17
25	Se4598
26	TheDJ
27	\| p50380g50491 \| 73 \| https://tools.wmflabs.org/admin/tool/tb-dev
28	Harej
29	Tb
30	Zhuyifei1999
31	\| s51111 \| 72 \|https://tools.wmflabs.org/admin/tool/tb-dev
32	Harej
33	Tb
34	Zhuyifei1999
35	\| p50380g50769 \| 39 \|https://tools.wmflabs.org/admin/tool/wikiviewstats
36	Betacommand
37	Cyberpower678
38	Hedonil
39	Technical 13
40	Tools.wikiviewstats2
41	Tools.xtools
42	\| s52107 \| 35 \|https://tools.wmflabs.org/admin/tool/ptbot
43	Arash.pt
44	Tools.ptbot
45	\| u3710 \| 33 \|
46	Hedonil
47	\| s51892 \| 33 \|https://tools.wmflabs.org/admin/tool/toolserverdb
48	Jkroll
49	Nosy
50	Tobias Gritschacher
51	\| s52861 \| 25 \|https://tools.wmflabs.org/admin/tool/request
52	FNDE
53	\| u4529 \| 20 \|
54	Dungodung
55	\| p50380g50729 \| 9 \| --- PROBLEM: points to (non-registered, nor on metawiki) https://wikitech.wikimedia.org/wiki/User:Grantsbot -- ah, should be https://meta.wikimedia.org/wiki/User:GrantsBot
56	I JethroBT
57	Jmorgan
58	\| p50380g50553 \| 8 \| --- PROBLEM: points to (non-registered) https://wikitech.wikimedia.org/wiki/User:Suggestbot -- Probably should be https://en.wikipedia.org/wiki/User:SuggestBot or https://tools.wmflabs.org/admin/tool/suggestbot
59	Bobo.03
60	Estuempfig
61	Halfak
62	Kjschiroo
63	Nettrom
64	Tools.suggestbot
65	\| p50380g50692 \| 7 \| --- PROBLEM: points to (non-registered) https://wikitech.wikimedia.org/wiki/User:Dplbot
66	Dispenser
67	JaGa
68	Russell Blau
69	\| s51072 \| 5 \|https://tools.wmflabs.org/admin/tool/giftbot
70	Doc Taxon
71	Gifti
72	\| s51580 \| 4 \|https://tools.wmflabs.org/admin/tool/hroest
73	Hroest
74	\| s52957 \| 3 \|https://tools.wmflabs.org/admin/tool/wdcat
75	Merlijn van Deen
76	\| p50380g50552 \| 3 \|
77	UNKNOWN
78	\| s53311 \| 3 \|https://tools.wmflabs.org/admin/tool/tool-account
79	Hall1467
80	\| s51206 \| 2 \|https://tools.wmflabs.org/admin/tool/ptwikis
81	Agx
82	Alchimista
83	Danilo
84	Diego Queiroz
85	He7d3r
86	HenriqueCrang
87	Raylton P. Sousa
88	Rodrigo Padula
89	Sir Lestaty de Lioncourt
90	\| u2170 \| 2 \|
91	Alex_Monk
92	\| s51447 \| 2 \|https://tools.wmflabs.org/admin/tool/cobain
93	Alchimista
94	Anskar
95	Arnaugir
96	Castor
97	Coet
98	Gerardduenas
99	Joancreus
100	KRLS
101	Lluis tgn
102	Nknudsen
103	Unapersona
104	\| u3949 \| 2 \|
105	Computermacgyver
106	\| s51430 \| 1 \| --- PROBLEM: links to (not found) https://tools.wmflabs.org/admin/tool/edinbot
107	Edin
108	\| s53012 \| 1 \|https://tools.wmflabs.org/admin/tool/fikarummet
109	Lokal Profil
110	Sebastian Berlin (WMSE)
111	\| s51392 \| 1 \|https://tools.wmflabs.org/admin/tool/coord
112	Myst
113	\| u3617 \| 1 \|
114	Blahma
115	\| s51837 \| 1 \|https://tools.wmflabs.org/admin/tool/taxonbot
116	Doc Taxon
117	Gifti
118	\| u2718 \| 1 \|
119	Edin
120	\| s52690 \| 1 \|https://tools.wmflabs.org/admin/tool/dispenser
121	Dispenser
122	\| s52524 \| 1 \|https://tools.wmflabs.org/admin/tool/toolschecker
123	Andrew Bogott
124	Coren
125	Yuvipanda
126	\| s51172 \| 1 \|
127	MOVED ALREADY?

MassMessage sent to c3.labsdb database users (example).

I checked the Tool-Erwin's-tools /s51362. The affected data seems to be used by 4 of its tools and is basically all related to category tree traversal caching. These 4 tools all do joins between the tool data and the replication data.

These tools will likely have to be deprecated, as I see no way to quickly convert them into tools that would perform sufficiently. It's been on life support for a long time now and I guess we just ran out.

Liuxinyu970226 subscribed.Dec 1 2017, 12:00 PM

bd808 closed this task as Resolved.Dec 11 2017, 7:03 AM

bd808 claimed this task.

Liuxinyu970226 unsubscribed.Dec 11 2017, 9:23 AM

bd808 moved this task from Inbox to Done on the cloud-services-team (Kanban) board.Jan 4 2018, 12:15 AM

Quiddity mentioned this in T186564: Update tool-db-usage to just show 1 column.Feb 5 2018, 9:09 PM