Establish a working setup for PAWS with multi-instance wikireplicas
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	• Bstorm
	Mar 2 2021, 10:22 PM

Description

Lots of work has already been done on the parent task, and I'm splitting this off to properly track it.

The dbproxy image and service in PAWS splits authentication and records the user in SQL comments so that there is some kind of reasonable tracking of the shared account for the wikireplicas used by PAWS.

This has been done with mysql-proxy in lua so far, which is great, but it has some interesting limitations when trying to do routing based on schema/database. You can only change backend connection if you maintain an authenticated pool.

Figure it out in mysql-proxy or T253134: Find an alternative solution for the mysql-proxy in PAWS

Details

Subject	Repo	Branch	Lines +/-
maintain-dbusers: rely on the UIDS, not username for all accounts	operations/puppet	production	+13 -25
maintain-dbusers: rely on the global_id, not username for paws	operations/puppet	production	+26 -16
maintain-dbusers: polish things up a bit	operations/puppet	production	+33 -31
maintain-dbusers: correct the types on a the PAWS UID and paths	operations/puppet	production	+2 -2
maintain-dbusers: type cast the uid for paws users	operations/puppet	production	+2 -2
maintain-dbusers: fix the order of the paws accounts listing	operations/puppet	production	+1 -1
wikireplicas: create actual paws database accounts	operations/puppet	production	+115 -11
wikireplicas: create actual paws database accounts	operations/puppet	production	+69 -3

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
Resolved	• Marostegui	T233766 labsdb1011 mariadb crashed
		Restricted Task
		Restricted Task
		Unknown Object (Task)
Resolved	RobH	T260441 (Need By: ASAP) rack/setup/install clouddb10[13-20]
Open	None	T215858 Plan a replacement for wiki replicas that is better suited to typical OLAP use cases than the MediaWiki OLTP schema
Open	None	T280152 Mitigate breaking changes from the new Wiki Replicas architecture
Resolved	• Bstorm	T260389 Redesign and rebuild the wikireplicas service using a multi-instance architecture
Resolved	• Bstorm	T276284 Establish a working setup for PAWS with multi-instance wikireplicas

Event Timeline

• Bstorm created this task.Mar 2 2021, 10:22 PM

Restricted Application removed a project: Patch-For-Review. · View Herald TranscriptMar 2 2021, 10:22 PM

Interesting note to self: if you want mysql-proxy to print things in a docker container, don't use print(). Use io.stderr:write().

I've found that proxysql should be able to do this work, but it still considers lua a "future feature", which leaves me unable to do DNS lookups for routing and basically most of the auth interception (though some of that could be done).

It may be that a small service that simply keeps a pool of connections open so that mysql-proxy has them for routing would do it. Then I could swap connections as needed, using DNS for routing after reading the default_db. This would not be necessary if the mysql wire protocol included ANY auth or default_db information on connect. It doesn't.

• Phamhi triaged this task as High priority.Mar 9 2021, 5:15 PM

• Phamhi moved this task from Inbox to Doing on the cloud-services-team (Kanban) board.

Change 670626 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] wikireplicas: create actual paws database accounts

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

gerritbot added a project: Patch-For-Review.Mar 11 2021, 12:48 AM

Chicocvenancio added a project: PAWS.Mar 11 2021, 12:50 AM

Change 670626 merged by Bstorm:
[operations/puppet@production] wikireplicas: create actual paws database accounts

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

Change 672540 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] wikireplicas: create actual paws database accounts

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

Change 672540 merged by Bstorm:
[operations/puppet@production] wikireplicas: create actual paws database accounts

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

Change 673380 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] maintain-dbusers: fix the order of the paws accounts listing

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

Change 673380 merged by Bstorm:
[operations/puppet@production] maintain-dbusers: fix the order of the paws accounts listing

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

Change 673524 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] maintain-dbusers: type cast the uid for paws users

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

Change 673524 merged by Bstorm:
[operations/puppet@production] maintain-dbusers: type cast the uid for paws users

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

Change 673538 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] maintain-dbusers: correct the types on a the PAWS UID and paths

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

Mentioned in SAL (#wikimedia-cloud) [2021-03-19T17:17:59Z] <bstorm> running ALTER TABLE account MODIFY COLUMN type ENUM('user','tool','paws'); against the labsdbaccounts database on m5 T276284

Change 673538 merged by Bstorm:
[operations/puppet@production] maintain-dbusers: correct the types on a the PAWS UID and paths

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

• Bstorm renamed this task from Establish a working version of the dbproxy for PAWS with multi-instance wikireplicas to Establish a working setup for PAWS with multi-instance wikireplicas.Mar 19 2021, 6:38 PM

Ok, at this point, PAWS users have accounts on the wikireplicas using the default $HOME/.my.cnf file. I've confirmed it works for me.

I need to clean up the failed attempt at a rework of mysql-proxy, and documentation will be needed. The old mysql-proxy method will stop working when the old replicas are decommissioned. In order to keep at least some of the existing notebooks working, PAWS could use an update to inject the auth info in the file into the environment (even if just temporarily), I think. Otherwise all 300+ notebooks that use the replicas will need to switch to a more similar auth method as is used on Toolforge.

Since PAWS uses the default location for the client config, many clients will automatically detect it, which is nice.

Change 673606 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] maintain-dbusers: polish things up a bit

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

Change 673606 merged by Bstorm:
[operations/puppet@production] maintain-dbusers: polish things up a bit

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

Change 674151 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] maintain-dbusers: rely on the global_id, not username for paws

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

Change 674165 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] maintain-dbusers: rely on the global_id, not username for paws

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

Change 674165 merged by Bstorm:
[operations/puppet@production] maintain-dbusers: rely on the global_id, not username for paws

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

• Jhernandez subscribed.Mar 30 2021, 4:57 PM

At this point maintain-dbusers is stable, so I'm going to close the task.

• Bstorm mentioned this in rPAWS17b00ad91907: database testing: remove failed experiments.Apr 2 2021, 5:46 PM

bd808 merged a task: T253134: Find an alternative solution for the mysql-proxy in PAWS.May 24 2021, 6:00 PM

bd808 added subscribers: yuvipanda, bd808.

bd808 mentioned this in T253134: Find an alternative solution for the mysql-proxy in PAWS.May 24 2021, 6:03 PM

Change 674151 abandoned by Bstorm:

[operations/puppet@production] maintain-dbusers: rely on the UIDS, not username for all accounts

Reason:

Until the underlying problem of duplicate accounts if fixed, this isn't worth keeping open.