Last modified: 2014-01-03 15:56:00 UTC
This issue was converted from https://jira.toolserver.org/browse/DBQ-108. Summary: Admin (sysop) statistics for Polish Wikipedia Issue type: Task - A task that needs to be done. Priority: Major Status: Done Assignee: (none) ------------------------------------------------------------------------------- From: Maciej Jaros <egil@wp.pl> Date: Sat, 16 Oct 2010 14:59:15 ------------------------------------------------------------------------------- While waiting for my account... I want to get some admin (sysop) statistics for a friend's presentation on Polish Wikipedia conference. To do this I would like to run some queries and dump whatever I have in a created table. I've upload the script to my site (link in URL). This can be run in batch mode, but be warned that it creates "admin_stats" table and assumes pl.wikipedia database name is "plwiki_p". What I need is a dump of "admin_stats" table after the script is run. You can delete the table afterwards.
------------------------------------------------------------------------------- From: Maciej Jaros <egil@wp.pl> Date: Sat, 16 Oct 2010 15:14:06 ------------------------------------------------------------------------------- On more thing I would be grateful for information if (after running the script) this query returns any records: > SELECT ug_user FROM plwiki_p.user_groups WHERE ug_group = 'sysop' AND ug_user NOT IN (SELECT admin_id FROM admin_stats)
------------------------------------------------------------------------------- From: EdoDodo <dodo.wikipedia@gmail.com> Date: Mon, 18 Oct 2010 05:33:42 ------------------------------------------------------------------------------- When I get to the query: UPDATE admin_stats a LEFT JOIN plwiki_p.logging l ON (l.log_user_text = a.admin_name) SET registration_dt = log_timestamp ,admin_id = log_user WHERE log_type = 'newusers' AND log_action = 'create' The Toolserver won't let me run it because I don't have high enough permissions to use a table from the Wikipedia databases in an UPDATE statement. I'll unassign it and leave it for somebody else to run, since as it is at the moment, I can't.
------------------------------------------------------------------------------- From: Maciej Jaros <egil@wp.pl> Date: Mon, 18 Oct 2010 06:53:36 ------------------------------------------------------------------------------- Damn, I thought it might... Try this: UPDATE admin_stats SET registration_dt = (SELECT MIN(log_timestamp) FROM wikidb.logging WHERE log_user_text = admin_name AND log_type = 'newusers' AND log_action = 'create') ,admin_id = (SELECT MIN(log_user) FROM wikidb.logging WHERE log_user_text = admin_name AND log_type = 'newusers' AND log_action = 'create') ; Should work the same, but might be a lot slower.
------------------------------------------------------------------------------- From: Marcin Cieślak <saper@saper.info> Date: Fri, 22 Oct 2010 12:19:35 ------------------------------------------------------------------------------- I have fixed this, an the query fails with: ERROR 1054 (42S22) at line 29: Unknown column 'log_user_text' in 'where clause' What we have is: +---------------+---------------------+------+-----+----------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------------+------+-----+----------------+-------+ | log_id | int(10) unsigned | NO | | 0 | | | log_type | varchar(32) | NO | | | | | log_action | varchar(32) | NO | | | | | log_timestamp | varchar(14) | NO | | 19700101000000 | | | log_user | int(10) unsigned | NO | | 0 | | | log_namespace | int(11) | NO | | 0 | | | log_deleted | tinyint(3) unsigned | NO | | 0 | | | log_title | varchar(255) | NO | | | | | log_comment | varchar(255) | NO | | | | | log_params | longblob | NO | | NULL | | +---------------+---------------------+------+-----+----------------+-------+ (as http://www.mediawiki.org/wiki/Manual:Logging_table says)
------------------------------------------------------------------------------- From: Maciej Jaros <egil@wp.pl> Date: Fri, 22 Oct 2010 17:07:04 ------------------------------------------------------------------------------- No, no it should be there. Wikipedia uses 1.16. See http://svn.wikimedia.org/svnroot/mediawiki/branches/REL1_16/phase3/maintenance/tables.sql Maybe it was removed :/. Do you have access to the user table? Or anything else that contains names of the users and their ids? I'm sure counters get this data from some place.
------------------------------------------------------------------------------- From: Maciej Jaros <egil@wp.pl> Date: Sun, 24 Oct 2010 08:40:51 ------------------------------------------------------------------------------- Done on dumps. Thanks for trying though. The queries were less perfect then I though (at least for MediaWiki installation that sailed through different version of MW).
This bug was imported as RESOLVED. The original assignee has therefore not been set, and the original reporters/responders have not been added as CC, to prevent bugspam. If you re-open this bug, please consider adding these people to the CC list: Original assignee: (none) CC list: egil@wp.pl, dodo.wikipedia@gmail.com, marcin.cieslak@gmail.com
