Page MenuHomePhabricator

Generate labsdb views for dtywiki, pawikisource, ptwikimedia, wbwikimedia
Closed, ResolvedPublic

Description

Apparently we have missed the queue for the creation of new labsdb wikis (or at least, I did). DBAs have already set up the production filtering, you have the greenlight to create the _p databases and the views (and meta_p?). To avoid having to deal with 4 separate tickets, see if they are public/private/etc., I have summarized the list of pending dbs for you:

  • dtywiki
  • pawikisource
  • ptwikimedia
  • wbwikimedia

Event Timeline

jcrespo created this task.Apr 28 2017, 6:45 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 28 2017, 6:45 PM

hey Security folks can we can a sign off on creating the normal views for these four wiki's on the labs DB replicas?

Friendly ping for @Bawolff and @dpatrick. I'm not sure if these are approved somewhere else in this capacity or not but I'm trying to error on the side of prudence since I have not been able to locate.

No qualms from us. Please proceed at your convenience.

This is done on:

labsdb1001
labsdb1009
labsdb1010
labsdb1011

Our old friend cropped up on labsdb1003 with all 4:

maintain-views --databases wbwikimedia --replace-all --debug
2017-05-03 18:50:13,522 DEBUG Removing 0 dbs as sensitive
2017-05-03 18:50:13,526 INFO Full views for wbwikimedia:
2017-05-03 18:50:13,528 INFO [abuse_filter_action]
2017-05-03 18:50:13,528 DEBUG SQL:
                    CREATE OR REPLACE
                    DEFINER=viewmaster
                    VIEW `wbwikimedia_p`.`abuse_filter_action`
                    AS SELECT * FROM `wbwikimedia`.`abuse_filter_action`;

Traceback (most recent call last):
  File "/usr/local/sbin/maintain-views", line 442, in <module>
    ops.execute(fullviews, customviews)
  File "/usr/local/sbin/maintain-views", line 272, in execute
    self.do_fullview(view)
  File "/usr/local/sbin/maintain-views", line 125, in do_fullview
    """.format(self.definer, self.db_p, view, self.db))
  File "/usr/local/sbin/maintain-views", line 53, in write_execute
    self.cursor.execute(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 158, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 308, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 820, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1002, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1285, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 966, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 394, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception
    raise errorclass(errno, errorvalue)
pymysql.err.OperationalError: (1142, "CREATE VIEW command denied to user 'maintainviews'@'localhost' for table 'abuse_filter_action'")

@jcrespo ideas?

I have done a FLUSH PRIVILEGES but it didn't work (as usual).
What was the issue the last time? Was it also 1003? I remember Jaime and yourself talking about it on IRC but I was doing something else and didn't pay much attention to that particular issue that day.

I will try random stuff today until it works.

Forgot about meta_p, will run that today :)

Mentioned in SAL (#wikimedia-operations) [2017-05-04T14:03:35Z] <chasemp> maintain-meta_p --all-databases --purge --debug labsdb1009/1010/1011 for T164103

Mentioned in SAL (#wikimedia-operations) [2017-05-04T14:16:31Z] <chasemp> maintain-meta_p --all-databases --purge --debug labsdb1001 for T164103

Mentioned in SAL (#wikimedia-operations) [2017-05-04T14:23:50Z] <chasemp> maintain-meta_p --databases dtywiki,pawikisource,ptwikimedia,wbwikimedia --debug labsdb1003 for T164103

chasemp added a comment.EditedMay 4 2017, 2:24 PM
select count(*) from meta_p.wiki;
+----------+
| count(*) |
+----------+
|      871 |
+----------+

on all

Mentioned in SAL (#wikimedia-operations) [2017-05-04T14:29:11Z] <jynus> dropping and recreating user for maintain-views on labsdb1001 T164103

I ran "maintain-views --databases wbwikimedia --replace-all --debug" and I think it works. The only thing I did was to delete the user and recreate it with the same grants. Do I do it for the other 3 databases?

chasemp closed this task as Resolved.May 4 2017, 3:14 PM
chasemp claimed this task.

This is done on:
labsdb1001
labsdb1009
labsdb1010
labsdb1011
Our old friend cropped up on labsdb1003 with all 4:

maintain-views --databases wbwikimedia --replace-all --debug
2017-05-03 18:50:13,522 DEBUG Removing 0 dbs as sensitive
2017-05-03 18:50:13,526 INFO Full views for wbwikimedia:
2017-05-03 18:50:13,528 INFO [abuse_filter_action]
2017-05-03 18:50:13,528 DEBUG SQL:
                    CREATE OR REPLACE
                    DEFINER=viewmaster
                    VIEW `wbwikimedia_p`.`abuse_filter_action`
                    AS SELECT * FROM `wbwikimedia`.`abuse_filter_action`;
Traceback (most recent call last):
  File "/usr/local/sbin/maintain-views", line 442, in <module>
    ops.execute(fullviews, customviews)
  File "/usr/local/sbin/maintain-views", line 272, in execute
    self.do_fullview(view)
  File "/usr/local/sbin/maintain-views", line 125, in do_fullview
    """.format(self.definer, self.db_p, view, self.db))
  File "/usr/local/sbin/maintain-views", line 53, in write_execute
    self.cursor.execute(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 158, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 308, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 820, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1002, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1285, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 966, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 394, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception
    raise errorclass(errno, errorvalue)
pymysql.err.OperationalError: (1142, "CREATE VIEW command denied to user 'maintainviews'@'localhost' for table 'abuse_filter_action'")

@jcrespo ideas?

with the help of @jcrespo who removed and recreated the user this has now been done on labsdb1003

Mentioned in SAL (#wikimedia-operations) [2017-05-04T15:15:20Z] <chasemp> labsdb1003 maintain-views --databases ptwikimedia,pawikisourcewbwikimedia,dtywiki --replace-all --debug T164103

chasemp reopened this task as Open.May 4 2017, 3:26 PM

Need to add the DNS aliases

chasemp triaged this task as Medium priority.May 4 2017, 3:54 PM
chasemp updated the task description. (Show Details)
Meno25 removed a subscriber: Meno25.May 5 2017, 6:41 AM