Page MenuHomePhabricator

/users throws a fatal error
Closed, ResolvedPublic

Description

traceback
[2019-12-08 19:39:38,784] ERROR in app: Exception on /users [GET]
Traceback (most recent call last):
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/flask/app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/flask/app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/flask/app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/flask/_compat.py", line 35, in reraise
    raise value
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/data/project/commons-mass-description/www/python/src/app.py", line 81, in users
    cur.execute(sql)
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/pymysql/connections.py", line 516, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/pymysql/connections.py", line 727, in _read_query_result
    result.read()
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/pymysql/connections.py", line 1066, in read
    first_packet = self.connection._read_packet()
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/pymysql/connections.py", line 683, in _read_packet
    packet.check_error()
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/data/project/commons-mass-description/www/python/venv/lib/python3.4/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1054, "Unknown column 'rev_user_text' in 'field list'")

Notes

This happens due to actor migration, in which rev_user and rev_user_text columns were normalized into actor table.

Event Timeline

Good Evening,
I've created the following SQL query to replace what was not previously working.

SELECT actor_name, count(*) from change_tag join actor_revision join revision join change_tag_def on ct_rev_id=rev_id where ctd_name="OAuth CID: 821" and actor_user>0 group by actor_user order by count(*) desc

Quarry

However, this is returning no results since the change_tag_def table holds no records of ctd_name="OAuth CID: 821" - I have checked what this table includes by using SELECT * FROM change_tag_def on Quarry which shows me many other rows that do feature OAuth CIDs; none with 821. Because of this, my query will always return nothing. Is this CID correct? Thanks

Similarly, I have fixed the second SQL query as part of this task:

SELECT count(*) FROM change_tag join change_tag_def join revision join actor_revision on ct_rev_id=rev_id where ctd_name="OAuth CID: 821" AND actor_id>0

Quarry

This returns count=0

Just wondering whether this is correct or if I'm doing something totally wrong?! Thanks!

Hello @NicholasG04, thank you for claiming this task! That's because you didn't tell Quarry which database it should use, so it used its default (the English Wikipedia). Commons Mass Description doesn't work at the English Wikipedia, so your queries returned 0. You need to add USE commonswiki_p; before your query. Hope that helps!

Ahhh, how stupid of me! I should've realised! Thanks very much.

Now my queries aren't completing - probably because I'm joining too many (rather large I suppose) tables together. Is there a better way of submitting such a query which now requires so many tables? Thanks!

Now my queries aren't completing - probably because I'm joining too many (rather large I suppose) tables together. Is there a better way of submitting such a query which now requires so many tables? Thanks!

IIRC, join actor_revision join revision, without any ON condition, is going to do a cross join.

Hi, thanks for your very helpful advice! It has allowed me to fix my SQL such that it is now working fine on Quarry.
One last thing, before I push for review: When trying to run the flask app to see if it is working, it is erroring that it cannot connect to 'commonswiki.web.db.svc.eqiad.wmflabs' when using toolforget.connect('commonswiki') or commonswiki_p.

Am I right in thinking that this is purely a local issue that should be alright when it's pushed properly? Thanks!

Yes, you are. Internally, the database server is exposed as commonswiki.web.db.svc.eqiad.wmflabs, and that destination is unreachable from the outside world. Once you push your patch for review, I'll check it and let you know if it works! It should be very likely it does work, if your query works on Quarry.

Brilliant! Thank you so much for your assistance sir throughout this task. I'll push for review now.

Change 556243 had a related patch set uploaded (by NicholasG04; owner: NicholasG04):
[labs/tools/commons-mass-description@master] GCI - Fixed SQL for the /users page

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

Change 556243 merged by jenkins-bot:
[labs/tools/commons-mass-description@master] GCI - Fixed SQL for the /users page

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