Talk:Quarry/2019

Discussion area for discussion about Quarry itself and help about individual queries.

"select *" returns different rows from "select col_name"?

When I run:

select rd_title from redirect where rd_title like 'The\_%' limit 5;

I get:

The_"5"_Royales

The_"5"_Royales

The_"5"_Royales

The_"5"_Royales

The_"5"_Royales

If I replace the explicit column name "rd_title" with "*", I get different rows:

select * from redirect where rd_title like 'The\_%' limit 5;

2751	0	The_Angry_Brigade
2903	0	The_Diary_of_a_Young_Girl
3317	0	The_Birth_of_a_Nation
3920	0	The_Beatles
4238	0	The_Bronx

What's going on here? I expected to get the same rows, just more columns. RoySmith (talk) 04:20, 23 January 2019 (UTC)

You may not expect a particular order of data, unless you specify ORDER BY. That said, the reason is probably somewhere deep in the database software. Matěj Suchánek (talk) 14:00, 23 January 2019 (UTC)

Search for genus & species items missing descriptions

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I'd like to port this working WD Query Service query into Quarry (it works when there are ≲ 5000 possible results, but genus & species are timing out the server), but I'm having trouble, could someone help?   ~ Tom.Reding (talk ⋅dgaf)  21:49, 7 February 2019 (UTC)

USE wikidatawiki_p;
SELECT page_title FROM page
JOIN pagelinks ON page_id = pl_from
LEFT JOIN wb_terms ON term_full_entity_id = page_title AND page_namespace = 0 AND term_language = 'en' AND term_type = 'description'
WHERE pl_title = 'Q34740' AND pl_namespace = 0;

Since the SQL database doesn't hold statements, we need to use standard MediaWiki links. Matěj Suchánek (talk) 08:43, 8 February 2019 (UTC)

How does one search for empty description term_text though? I tried adding "AND NOT EXISTS(term_text)" to the WHERE, but got an error; I also tried adding term_text to the SELECT but it times out, even with LIMIT 100.   ~ Tom.Reding (talk ⋅dgaf)  13:45, 8 February 2019 (UTC)

Oops, I forgot AND term_text IS NULL. Matěj Suchánek (talk) 14:20, 8 February 2019 (UTC)

Killed again after 30 minutes :( I have gotten the original WDQS query to not timeout/error somehow, by, counter intuitively, removing the artificially imposed limit... Thanks for your help though.   ~ Tom.Reding (talk ⋅dgaf)  16:47, 8 February 2019 (UTC)

That is interesting... Matěj Suchánek (talk) 18:06, 8 February 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Listing past runs of a query

It is clear that the results of past runs of a query are stored on Quarry's servers; I was able to find those for my label statistics run on the 25th (compare those from the 26th) after some crawling based on a suspected range of run IDs within which the 25th's results would have fallen.

Is there a way to obtain a list of the results of past runs of a query so that such crawling won't be necessary? Mahir256 (talk) 22:24, 26 February 2019 (UTC)

You are correct. It is indeed stored. It is however not exposed in the interface. And I just checked the code and found there are no uses of Query.revs. So unfortunately we don't currently have the list of revisions for a particular query exposed. Would you like to contribute a patch to add this? Zhuyifei1999 (talk) 02:13, 27 February 2019 (UTC)

Error Hmm... Is the SQL actually running?!

What does this mean? Kerry Raymond (talk) 01:52, 13 March 2019 (UTC)

phab:T205214 Zhuyifei1999 (talk) 02:52, 13 March 2019 (UTC)

Quarry Help Needed

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Would someone be able to create a script that could bring back a list of users on the page https://en.wikipedia.org/wiki/Wikipedia:WikiProject_Apple_Inc./Subscribe that haven't Edited in the last 5 years. If it could remove them, that would be helpful but not needed. RhinosF1 (talk) 09:26, 16 March 2019 (UTC)

Just to clarify, do you mean:

Let the list of people on that page be L. You want to find this set:

{user | user <- L and user has not made any edits on this wiki in 5 years}

What about other wikis? Also, what do you mean by "remove them", like remove them from the list?

To find users that haven't made any edits in one wiki you could do:

 select user_id
   from user
  where user_id in (... your list ...) 
    and user_id not in
        (select distinct rev_user
           from revision
          where rev_timestamp > '20140301000000'
        )
;

But that subquery would be impossibly slow on enwiki, so this is a heavy query. Not sure how to optimize it off the top of my head. Might just be better to make a temporary table somewhere out of the users that did edit in the past 5 years and query that. Milimetric (WMF) (talk) 14:22, 18 March 2019 (UTC)

Editors in last 5 years would also work. That query failed (https://quarry.wmflabs.org/query/34487). Just enwiki is what we need. We need to end up with a list of users who have/have not edited in the past 5 years and are on that page. RhinosF1 (talk) 15:14, 18 March 2019 (UTC)

you substituted "https://en.wikipedia.org/wiki/Wikipedia:WikiProject_Apple_Inc./Subscribe" where I have (... your list ...) in the query, but that's not how Quarry works. Quarry just executes SQL, it doesn't now how to read the wiki and parse the user ids out of it. You'd have to do that manually, or build a query to get a list of user_ids based on user names. You can also make a list of user names that looks like this:

('user name 1', 'user name 2', ...) and pass that in the query above as `user_name in ('user name 1', 'user name 2')`. Milimetric (WMF) (talk) 17:38, 27 March 2019 (UTC)

Sorry for the late reply, The query was done in the end via production for us RhinosF1 (talk) 06:38, 11 April 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

How to kill a query?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Hello, my query https://quarry.wmflabs.org/query/34500 it says "running" for 4 hours, probably because of large output size, please help kill it! Землеройкин (talk) 00:06, 19 March 2019 (UTC)

It'd probably dead already. No status being updated is a consequence of quarry running out of memory while trying to store results. Zhuyifei1999 (talk) 01:34, 19 March 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

template tables not updated?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Hi,

This simple quarry should return some pages in enwiki which have no fa interwiki. It works for articles, but not for templates (the results for templates have fa interwiki). Can somebody help? Saeidpourbabak (talk) 17:52, 10 April 2019 (UTC)

I couldn't find an example in the results. Note that there may be a problem in the database, like a deferred update. Matěj Suchánek (talk) 18:31, 10 April 2019 (UTC)

Hello,

The first item in the results (en:template:Gmina Istebna) has fa interwiki.

En:Template:Gmina Istebna Saeidpourbabak (talk) 18:52, 10 April 2019 (UTC)

Looks like Wikidata doesn't do database updates properly. I did a null edit on that page and now the entry is in the database. Unfortunately, you will need to do this on every page if you want accurate results. Matěj Suchánek (talk) 19:40, 10 April 2019 (UTC)

Thank you, but isn't there any more efficient and standard way to fix the problem? The quarry I referred to was just an example I made to show the issue. The quarry that I used and encountered problem with has more than 13k results, which performing null edit on that number of pages is impractical. Saeidpourbabak (talk) 12:40, 11 April 2019 (UTC)

Well, to "fix the problem" you need to somehow trigger a new database update. And as a casual user, the only way (if you're lucky) is really performing a null edit. I will make a bug report on this shortly. Matěj Suchánek (talk) 15:29, 11 April 2019 (UTC)

Task: phab:T220794 Matěj Suchánek (talk) 08:47, 12 April 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Need an active user query

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Per a conversation on #wikimedia-tech Special:Statistics no longer works , Would it be possible to get an alternate query?

RhinosF1 (talk) 16:27, 11 April 2019 (UTC)

I see you already have a few examples like https://quarry.wmflabs.org/query/35057, and I'm wondering what else you'd like help with. One thing I can suggest is that you try to format your queries so they can be more easily read. For example, try this style (which I'm a little too obsessive about, I admit, but it's good)

SELECT COUNT(DISTINCT rc_user)
  FROM recentchanges_userindex
           JOIN
       user                       ON (rc_user = user_id)
           LEFT JOIN
       ipblocks                   ON (rc_user = ipb_user)
 WHERE rc_user != 0
   AND rc_timestamp > DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y%m%d%H%i%S')
   AND rc_type = 0
   AND rc_bot = 0
   AND ipb_id IS NULL
   AND (user_registration IS NULL OR user_registration < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%Y%m%d%H%i%S'))
   AND user_editcount >= 1

And btw, you can write that user registration clause as:

(coalesce(user_registration, '2000') < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%Y%m%d%H%i%S'))

Milimetric (WMF) (talk) 14:21, 12 April 2019 (UTC)

Ah, It wasn't me that made them! Can't remember who! - I was looking for one that just did a number of editors with a logged edit or action in last 30 days @Milimetric (WMF) RhinosF1 (talk) 14:27, 12 April 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

article extraction

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Can someone help me to write a query which gives the article content from Wikipedia articles in Telugu language 202.63.103.210 (talk) 13:02, 15 April 2019 (UTC)

It is not possible to extract content from articles via Quarry (relevant tables are not publicly available). Matěj Suchánek (talk) 19:21, 16 April 2019 (UTC)

But you can use the Special:Export page on your local wiki to get an w:XML version of the content of the pages. Framawiki (talk) 13:55, 22 April 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Search by revision text?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I want to find all revisions where a given user added a specific word to a page. Is this possible?

The best I can see is to find all revisions a user has made, then grovel over them to generate the diffs for each one and test if the word is in the diff. RoySmith (talk) 16:19, 15 May 2019 (UTC)

Not possible using Quarry (explained in Talk:Quarry/2019#h-article_extraction-2019-04-15T13:02:00.000Z). Matěj Suchánek (talk) 18:45, 15 May 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Wikisource text layer encoding

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Am I correct to assume that the text layer of a Wikisource page is encoded as UTF-8? Does this apply to all Wikipedia sites that contain text? — Ineuw talk 18:38, 18 May 2019 (UTC)

Yes and yes as far as I know. Citing Manual:$wgDBTableOptions: "MediaWiki internally always produces data in UTF-8 encoding to be saved in the database."

Achim (talk) 22:01, 18 May 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Queries running slow?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I've been trying to run https://quarry.wmflabs.org/query/28096 for the last few days, but it keeps timing out. It normally works (or works after timing out once or twice). Are things running slow on the back-end? If so, would it be possible to increase the time-out from 30 minutes to 60 minutes or so? Thanks! Mike Peel (talk) 17:18, 23 May 2019 (UTC)

It says it's executed in 894.39 seconds. Is this still an issue? Zhuyifei1999 (talk) 01:13, 25 May 2019 (UTC)

I just reran it again today, and it worked first time with an execution time of 735.61 - so maybe this is resolved. However, let's see how it goes over the next week... Mike Peel (talk) 08:05, 25 May 2019 (UTC)

Note that Quarry is "simply" an interface for Wiki replicas databases. A timeout certainly comes from these DB. Framawiki (talk) 09:43, 25 May 2019 (UTC)

It's back to timing out again today. :-( It must somehow be linked to server loads, as I'm not changing the query between runs, and the amount of data to analyse and the number of results are roughly similar. Mike Peel (talk) 18:51, 29 May 2019 (UTC)

It's still timing out. :-( Mike Peel (talk) 20:58, 31 May 2019 (UTC)

... and today it completed in 484.18 seconds (~8 minutes). Very weird - the run time seems to be very random. Mike Peel (talk) 06:47, 4 June 2019 (UTC)

It's been back to timing out for most of this last week. Mike Peel (talk) 18:45, 12 June 2019 (UTC)

I have been having the same problem with query/36685 :( Frietjes (talk) 23:10, 12 June 2019 (UTC)

The query has been timing out for the last fortnight now. :-( Mike Peel (talk) 20:31, 18 June 2019 (UTC)

The query has been timing out for the last fortnight now. :-( I've filed a bug report at https://phabricator.wikimedia.org/T226050 Mike Peel (talk) 20:33, 18 June 2019 (UTC)

Same issue with quarry:quarry/36666 which had been taking about 15 minutes but is now timing out after 30. Ahecht (talk) 16:40, 20 June 2019 (UTC)

I experience the same thing. It seems analytics cluster is overloaded or there may be some other issue. All my simple queries now take 20+ minutes or timeout, a year ago they run under 2 minutes.

For Toolforge users the easy solution is to try the web cluster (sql --cluster=web enwiki), but the issue should be solved anyway. Dvorapa (talk) 11:51, 28 June 2019 (UTC)

Per IRC thread everything should be back to normal soon Dvorapa (talk) 14:27, 28 June 2019 (UTC)

IRC thread? Mike Peel (talk) 18:12, 28 June 2019 (UTC)

I was able to run quarry:query/36685 about 20 minutes ago, and it didn't timeout this time! Frietjes (talk) 18:31, 28 June 2019 (UTC)

I'm getting error 500 from https://quarry.wmflabs.org/query/runs/all - and https://quarry.wmflabs.org/query/37235 seems to be queued, which is new! Mike Peel (talk) 18:37, 28 June 2019 (UTC)

Error 500 should be fixed now based on what guys have said on IRC (#wikimedia-cloud) Dvorapa (talk) 19:05, 28 June 2019 (UTC)

It seems to be working now, thanks! :-) Mike Peel (talk) 07:45, 29 June 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

mw-fr-revisiontag

Hello, I am looking for articles with flaggedRevs boxes, is there any way to find them by http://quarry.wmflabs.org/? جار الله (talk) 10:52, 30 May 2019 (UTC)

Subquery succeeds but Join times out

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I'm trying to get some estimates of the prevalence of WP policy violations, but am seeing timeouts for queries that do joins.

SELECT page.page_title, revision.rev_id, mycomment.comment_text FROM (SELECT comment.comment_text, comment.comment_id FROM comment WHERE comment.comment_text RLIKE '[[:<:]]WP:(OUTING|PRIVACY|DOX)[[:>:]]' LIMIT 10) AS mycomment INNER JOIN revision ON revision.rev_comment_id = mycomment.comment_id INNER JOIN page on page.page_id = revision.rev_page;

The internal select statement finishes in a <cough> reasonable amount of time, but trying to use it in a join fails every time. Any suggestions on how to materialize the subselect perhaps? PaigePhault (talk) 19:08, 30 May 2019 (UTC)

What about...?

SELECT page_title, rev_id, comment_text
FROM page
JOIN revision ON rev_page = page_id
JOIN `comment` ON rev_comment_id = comment_id
WHERE rev_comment_id IN (SELECT comment_id FROM `comment` AS c1 WHERE c1.comment_text RLIKE '[[:<:]]WP:(OUTING|PRIVACY|DOX)[[:>:]]')
LIMIT 10;

Matěj Suchánek (talk) 17:32, 2 June 2019 (UTC)

Nope, that also times out see here https://quarry.wmflabs.org/query/36349 PaigePhault (talk) 19:32, 2 June 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Missing columns in revision table?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I've just tried to run a query that uses rev_user_text, one of the columns in the revision table, and get a failure with :

Unknown column 'rev_user_text' in 'field list'

It runs fine when I remove SELECT rev_user_text from the query. Is this a temporary glitch, or has rev_user_text been permanently removed? Le Deluge (talk) 12:55, 4 June 2019 (UTC)

Yes, permanently removed.

See phab:T223406. Edgars2007 (talk) 13:24, 4 June 2019 (UTC)

There is some additional information on this change at wikitech:News/Actor storage changes on the Wiki Replicas. BDavis (WMF) (talk) 15:40, 4 June 2019 (UTC)

Also seems we have out of date documentation however...

https://www.mediawiki.org/wiki/Manual:Revision_table#rev_user_text

That's probably not helpful... —TheDJ (Not WMF) (talk • contribs) 22:46, 4 June 2019 (UTC)

I created phab:T225007 today to track the need updating the Manual. Part of the issue that has happened here is explained (but maybe not well) on wikitech:News/Actor storage changes on the Wiki Replicas is that the Wiki Replica views have changed in advance of MediaWiki changes. I'm not 100% sure which patches have landed in the MediaWiki code base and which are still pending in Gerrit. There definitely is some documentation lag however because even if the legacy user fields have not been remove from MediaWiki core yet, the actor table and related fields have landed. BDavis (WMF) (talk) 23:44, 4 June 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Search in my SQL queries

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Due to the ongoing migration to the actor table, quite a number of queries need modifications. Currently I have 75 queries in my Quarry account, and it would be nice if there was a "Search in my queries" functionality that would allow me to look for e.g. "_user". Would it be possible to add such a search field to Quarry? MisterSynergy (talk) 18:22, 10 June 2019 (UTC)

I currently simply use scraping for such purposes (also it acts like backup). But yeah, this would be very, very nice.

There should be thread or phab ticket for this. Something like "meta-quarry" (that is, query the quarry data). Edgars2007 (talk) 04:45, 11 June 2019 (UTC)

phab:T90509 seems related, but it sounds like you might be hoping for searching of the SQL query body itself too. BDavis (WMF) (talk) 22:17, 11 June 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Number of people who put specific page into Watchlist

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

We can get number of people who put specific page into Watchlist through [https://en.wikipedia.org/w/api.php?action=query&prop=info&titles=Main%20Page&inprop=watchers API]. Could We become able to do this through Quarry?

I'd like to create page list most watchlisted. Thank you. Japanologie (talk) 01:28, 12 June 2019 (UTC)

No, the relevant tables are not public for privacy reasons. Matěj Suchánek (talk) 15:37, 12 June 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Font size smaller than before

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

The font size on https://quarry.wmflabs.org/ is now much smaller than before. Was this intentional? GTrang (talk) 02:52, 17 June 2019 (UTC)

Which part is smaller? Zhuyifei1999 (talk) 04:27, 17 June 2019 (UTC)

I can confirm that. The black input field, "Fork of...", "This query has been published...", query names under My profile. Firefox. Matěj Suchánek (talk) 07:39, 17 June 2019 (UTC)

@Framawiki: I think this is related to https://github.com/wikimedia/analytics-quarry-web/commit/29e39d686992ca48d973fc03ed2e073bac591e2e#diff-84c0b0cbacf66dd21e5b6405ef115fa3R48 Zhuyifei1999 (talk) 13:02, 17 June 2019 (UTC)

Buh. Patch: https://gerrit.wikimedia.org/r/c/analytics/quarry/web/+/518315 Framawiki (talk) 20:36, 21 June 2019 (UTC)

It should be better now, can you confirm? Framawiki (talk) 21:35, 21 June 2019 (UTC)

Seem correct, thanks! Matěj Suchánek (talk) 09:25, 24 June 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Categories with exactly 1 category in them

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Hi, can anybody help me create a query for a list of "Categories with exactly 1 category in them"? Thanks Bennylin (talk) 15:27, 5 July 2019 (UTC)

Nvm, I got it quarry:query/37399 Bennylin (talk) 04:59, 6 July 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Pagepile from quarry

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Any example of quarry, which I can pass to pagepile to create new? Alex Blokha (talk) 21:37, 28 July 2019 (UTC)

That is tracked as phab:T190242. One Day... Framawiki (talk) 19:59, 30 July 2019 (UTC)

Yes, but pagepile says it can create pagepile from quarry. I need an example of such quarry. Alex Blokha (talk) 20:54, 30 July 2019 (UTC)

Quarry is tool to make SQL queries to wikimedia databases. You need to enter query id in this field. You can get it from URL while you have opened query. wargo (talk) 14:45, 3 October 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Renaming Wikisource pages to new titles.

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Is there someone who (has the authority) to execute update querries on four misnamed Wikisource volumes? It would entail the renaming of the 4 djvu files and rename about 820 pages in each volume and replace the the same in the main namespace pages.

— Ineuw talk 04:22, 13 August 2019 (UTC)

Use bot to move these pages wargo (talk) 05:06, 13 August 2019 (UTC)

Someone tried to help me do that but the move failed. I never used the bot. I made a list of the changes need to be done https://en.wikisource.org/wiki/User_talk:Ineuw#Changes_required_for_the_four_History_of_Mexico_(Bancroft)_volumes — Ineuw talk 05:55, 13 August 2019 (UTC)

The list of changes is incomplete and probably the suggested changes are incorrect.

1. I think subpages of file pages are unsuppoerted (as "/" is not a legal character in a filename AFAIK); I see you decided to change "/" to "-"
2. moving an index/file page requires to move the associated pages in Page namespace; and this is good idea to prepare the list of required moves in advance as after the move you lose the list of pages
3. There are sometimes problems with redirects in Index namespace (they may need manual fixing of page content model by admin and they seems to be not fully supported by proofreadpage). I would suggest avoiding such redirects, but this requires fixing all pages linking to them.

The (2) suggests that the move is performed by an admin who is a also a bot operator or has online support of a bot operator (to synchtonise the changes and make the mess as short in time as possible). (Admin rights should not be necessery, but they may be needed for fixing (3) or any accidental mistakes).

Making such a move on an index being worked on in always a mess for them.

I made some such moves in pl.ws, but not such massive ones. My bot has not the bot flag here, so I cannot help at the moment. Ankry (talk) 08:22, 14 August 2019 (UTC)

Ankry, thanks for the advice. I forgot about the "/"slash in the index file name and it was converted to a "-" hyphen automatically by move process. I will correct my list accordingly. Already posted a bot request on en.WS — Ineuw talk 18:14, 14 August 2019 (UTC)

Replying to my own comment. I began the move on the Mediawiki Commons, where the originals and images are kept - before renaming the .djvu file in Wikisource. The Wikisource move failed and I am stuck with a self-made mess. Would it be possible to restore EVERYTHING to their original state on the Commons and Wikisource? The reason being that a forward moving process of correcting my mistakes will leave behnid a big mess.

— Ineuw talk 22:12, 13 August 2019 (UTC)

Maybe @Ankry: knows? wargo (talk) 06:41, 14 August 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Proofread status

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Hi! is there any way to know if a revision changed the proofread status of a Wikisource page? Ashot (talk) 23:26, 23 August 2019 (UTC)

Yes, notice edit summary has new level of proofread. wargo (talk) 14:43, 3 October 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Commons images only in hidden cat and in unexisting cat

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

@Edgars2007 and others. Any idea how to find images like this https://commons.wikimedia.org/wiki/File:Arbol_y_cielo_sepia.jpg

For only hidden cat the quarry is like this: https://quarry.wmflabs.org/query/38499 Estopedist1 (talk) 06:52, 6 September 2019 (UTC)

By what properties? wargo (talk) 14:41, 3 October 2019 (UTC)

@Wargo, actually I didn't get the point, but the goal is to find an image like this ([https://commons.wikimedia.org/wiki/File:%C3%87ay_dayaz_1.jpg]): hidden categories and one unexisting category (= red category) Estopedist1 (talk) 14:47, 3 October 2019 (UTC)

Table categorylinks also stores red categories assignement so you can add someting like

WHERE cl_to = 'Uncategorized media with description in Azerbaijani language' wargo (talk) 14:57, 3 October 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Querying wiki open/closed

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Some weeks ago I've seen a queryable way to check whether a wiki is closed or not. Unfortunately I don't find it anymore (should have written down the link due to my age...). Any hints?

Achim (talk) 07:36, 16 September 2019 (UTC)

If Wikimedia - https://noc.wikimedia.org/conf/highlight.php?file=dblists/closed.dblist

This configuration is stored in configuration file, not in database. wargo (talk) 08:21, 16 September 2019 (UTC)

wikitech:Help:Toolforge/Database#Metadata_database is probably what you are looking for. Matěj Suchánek (talk) 16:12, 16 September 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Use of a selected db inside of a query

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

If a db is selected like USE enwiki_p; is there a way to check this selected db in IF or CASE statements? Pseudocode: if db == enwiki_p then etc. Background: Running similar queries against different dbs using the (nearly) same code, so that one has to change the USE statement only. Achim (talk) 12:42, 2 October 2019 (UTC)

Not an answer to first question, but to (probably) your problem. You can use something like this:

use enwiki_p;
select * from some_en_wiki_table;
use dewiki_p;
select * from some_de_wiki_table;
use frwiki_p;
select * from some_fr_wiki_table;

It should work. Edgars2007 (talk) 16:08, 2 October 2019 (UTC)

Not really. I found a way (see quarry:query/16164) but that's not sufficient, because other projects like wikisource or wikispecies have same names for their main pages.

Edit: Just had an idea, the page_id could be used additionally, that will work I think. Achim (talk) 18:36, 2 October 2019 (UTC)

There is the DATABASE() function. Zhuyifei1999 (talk) 13:46, 3 October 2019 (UTC)

Great, thank you very much! Achim (talk) 14:21, 3 October 2019 (UTC)

Try also loops wargo (talk) 14:41, 3 October 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Why is count(*) so slow?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I'm running select count(*) from comment limit 10; (https://quarry.wmflabs.org/query/39582), which is taking forever. I would think that would be instantaneous.

Quarry claims, "Query status: running", but when I click the Explain button, I get, "Hmm... Is the SQL actually running?!"

RoySmith (talk) 16:05, 16 October 2019 (UTC)

"Hmm... Is the SQL actually running?!" Is a bug. phab:T205214

Why is it show? Because the explain is like:

{
  "rows": [
    [
      1,
      "PRIMARY",
      "comment",
      "index",
      null,
      "comment_hash",
      "4",
      null,
      266953357,
      "Using where; Using index"
    ],
    [
      11,
      "DEPENDENT SUBQUERY",
      "logging",
      "ref",
      "type_time,log_comment_type,log_type_action",
      "log_comment_type",
      "8",
      "enwiki.comment.comment_id",
      185,
      "Using where; Using index"
    ],
    [
      10,
      "DEPENDENT SUBQUERY",
      "revision_comment_temp",
      "ref",
      "PRIMARY,revcomment_rev,revcomment_comment_id",
      "revcomment_comment_id",
      "8",
      "enwiki.comment.comment_id",
      1,
      "Using index"
    ],
    [
      10,
      "DEPENDENT SUBQUERY",
      "revision",
      "eq_ref",
      "PRIMARY",
      "PRIMARY",
      "4",
      "enwiki.revision_comment_temp.revcomment_rev",
      1,
      "Using where"
    ],
    [
      9,
      "DEPENDENT SUBQUERY",
      "recentchanges",
      "ref",
      "rc_comment_deleted",
      "rc_comment_deleted",
      "8",
      "enwiki.comment.comment_id",
      1,
      "Using where; Using index"
    ],
    [
      8,
      "DEPENDENT SUBQUERY",
      "protected_titles",
      "ref",
      "pt_reason_id",
      "pt_reason_id",
      "8",
      "enwiki.comment.comment_id",
      4,
      "Using index"
    ],
    [
      7,
      "DEPENDENT SUBQUERY",
      "oldimage",
      "ref",
      "oi_description_deleted",
      "oi_description_deleted",
      "8",
      "enwiki.comment.comment_id",
      28,
      "Using where; Using index"
    ],
    [
      6,
      "DEPENDENT SUBQUERY",
      "ipblocks",
      "ref",
      "ipb_reason_deleted",
      "ipb_reason_deleted",
      "9",
      "enwiki.comment.comment_id,const",
      32,
      "Using index"
    ],
    [
      5,
      "DEPENDENT SUBQUERY",
      "filearchive",
      "ref",
      "fa_description_deleted",
      "fa_description_deleted",
      "8",
      "enwiki.comment.comment_id",
      124,
      "Using where; Using index"
    ],
    [
      4,
      "DEPENDENT SUBQUERY",
      "filearchive",
      "ref",
      "fa_reason",
      "fa_reason",
      "8",
      "enwiki.comment.comment_id",
      405,
      "Using index"
    ],
    [
      3,
      "DEPENDENT SUBQUERY",
      "image",
      "ref",
      "img_description_id",
      "img_description_id",
      "8",
      "enwiki.comment.comment_id",
      26,
      "Using index"
    ]
  ],
  "headers": [
    "id",
    "select_type",
    "table",
    "type",
    "possible_keys",
    "key",
    "key_len",
    "ref",
    "rows",
    "Extra"
  ]
}

Zhuyifei1999 (talk) 16:14, 16 October 2019 (UTC)

How did you get the explain output? RoySmith (talk) 16:19, 16 October 2019 (UTC)

As for why it is joining other tables, it wants to prevent people from gaining any extra knowledge of comments that may not be publicly viewable from the web interface, such as revision-deleted comments.

The enwiki_p.comment is a view against the database enwiki, with the following definition (SHOW CREATE VIEW comment;):

CREATE ALGORITHM=UNDEFINED DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER VIEW `comment` AS select `enwiki`.`comment`.`comment_id` AS `comment_id`,`enwiki`.`comment`.`comment_hash` AS `comment_hash`,`enwiki`.`comment`.`comment_text` AS `comment_text`,`enwiki`.`comment`.`comment_data` AS `comment_data` from `enwiki`.`comment` where (exists(select 1 from `enwiki`.`image` where (`enwiki`.`image`.`img_description_id` = `enwiki`.`comment`.`comment_id`) limit 1) or exists(select 1 from `enwiki`.`filearchive` where (`enwiki`.`filearchive`.`fa_deleted_reason_id` = `enwiki`.`comment`.`comment_id`) limit 1) or exists(select 1 from `enwiki`.`filearchive` where ((`enwiki`.`filearchive`.`fa_description_id` = `enwiki`.`comment`.`comment_id`) and ((`enwiki`.`filearchive`.`fa_deleted` & 2) = 0)) limit 1) or exists(select 1 from `enwiki`.`ipblocks` where ((`enwiki`.`ipblocks`.`ipb_reason_id` = `enwiki`.`comment`.`comment_id`) and (`enwiki`.`ipblocks`.`ipb_deleted` = 0)) limit 1) or exists(select 1 from `enwiki`.`oldimage` where ((`enwiki`.`oldimage`.`oi_description_id` = `enwiki`.`comment`.`comment_id`) and ((`enwiki`.`oldimage`.`oi_deleted` & 2) = 0)) limit 1) or exists(select 1 from `enwiki`.`protected_titles` where (`enwiki`.`protected_titles`.`pt_reason_id` = `enwiki`.`comment`.`comment_id`) limit 1) or exists(select 1 from `enwiki`.`recentchanges` where ((`enwiki`.`recentchanges`.`rc_comment_id` = `enwiki`.`comment`.`comment_id`) and ((`enwiki`.`recentchanges`.`rc_deleted` & 2) = 0)) limit 1) or exists(select 1 from (`enwiki`.`revision` join `enwiki`.`revision_comment_temp` on((`enwiki`.`revision_comment_temp`.`revcomment_rev` = `enwiki`.`revision`.`rev_id`))) where ((`enwiki`.`revision_comment_temp`.`revcomment_comment_id` = `enwiki`.`comment`.`comment_id`) and ((`enwiki`.`revision`.`rev_deleted` & 2) = 0)) limit 1) or exists(select 1 from `enwiki`.`logging` where ((`enwiki`.`logging`.`log_comment_id` = `enwiki`.`comment`.`comment_id`) and ((`enwiki`.`logging`.`log_deleted` & 2) = 0) and (`enwiki`.`logging`.`log_type` in ('abusefilter','articlefeedbackv5','block','campus','close','contentmodel','course','create','delete','eparticle','gather','gblblock','gblrename','gblrights','globalauth','gwtoolset','import','institution','instructor','interwiki','liquidthreads','lock','managetags','massmessage','merge','moodbar','move','mwoauthconsumer','newsletter','newusers','notifytranslators','online','pagelang','pagetranslation','pagetriage-curation','pagetriage-deletion','patrol','protect','renameuser','review','rights','spamblacklist','stable','student','tag','thanks','timedmediahandler','translationreview','upload','usermerge'))) limit 1))

Zhuyifei1999 (talk) 16:23, 16 October 2019 (UTC)

> How did you get the explain output?

You just have to try a few times, each time refreshing the page.. Hacky... I know. Zhuyifei1999 (talk) 16:24, 16 October 2019 (UTC)

Related information at https://wikitech.wikimedia.org/wiki/News/Actor_storage_changes_on_the_Wiki_Replicas#The_actor_table_seems_really_slow--so_does_comment BDavis (WMF) (talk) 16:33, 16 October 2019 (UTC)

Hmm. I actually tried that, but apparently with too small a value of "few" :-(

The view thing is ugly, but I get it. Is there any way to cancel a running query? This is still running, but I really don't want to trash the DB over this.

Maybe I should just back up and ask what I'm really trying to do. I want to find all the revisions which have a certain string in the edit comment. I'm assuming I want something long the lines of:

select rev_id from revision, comment

where rev_comment_id = comment_id

and comment_text like '%my certain string%';

That's obviously an expensive query, but I don't have any feel for just how long it would take? Are we talking minutes, hours, days? Will I do bad things to the DB if I try to run that? RoySmith (talk) 16:41, 16 October 2019 (UTC)

Using the comment_revision view instead of the comment view will help a bit, but comment_text like '%my certain string%'; will be really slow and expensive no matter what. Any LIKE match with a leading wildcard (%) will require a full table scan. See 8.3.1 How MySQL Uses Indexes in the MySQL documentation for a bit more detail, but fundamentally MySQL/MariaDB do not support indexes for prefix wildcards. BDavis (WMF) (talk) 18:01, 16 October 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

How to get revision content?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

If I have a rev_content_id, how do I get the actual content? The only way I see to join the revision and content tables is on content_sha1, but that's not indexed. RoySmith (talk) 15:43, 17 October 2019 (UTC)

The Wiki Replica databases do not store contents of revisions or pages. Page content must be retrieved from the MediaWiki Action API which is not integrated into Quarry. BDavis (WMF) (talk) 16:11, 17 October 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Has something been changed during the past 4 days?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I can't start https://quarry.wmflabs.org/query/15564 any longer, the "submit query" button doesn't work. Feels like a limitation of lines or chars had been implemented there. Well, my query is a bit voluminous but a run takes not much time as one can see. A comparable query https://quarry.wmflabs.org/query/39167 runs fine. Achim (talk) 19:27, 14 November 2019 (UTC)

The web node (not the database node) ran out of disk space right before Nov 12. Might be relevant. Let me fix the disk space issue. Zhuyifei1999 (talk) 21:28, 14 November 2019 (UTC)

I cleaned the files. phab:T238375. Could you test if the issue is fixed? Zhuyifei1999 (talk) 22:02, 14 November 2019 (UTC)

@Zhuyifei1999: Runs well now, thank you very much! Achim (talk) 09:41, 15 November 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

how do you find the author of a file?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Lately I was adding SDC statements P7482=Q66458942 to files that have {{Own }} template on Commons, using https://quarry.wmflabs.org/query/40759 query. User:Schlurcher pointed out cases where people are incorrectly using {{Own }} template for files where person listed as author is not the uploader. I would like to exclude such cases in the future query and find files where I added P7482, but where uploader is not the same as the author listed in {{Information }} template.

https://quarry.wmflabs.org/query/40838 query can find my edits from this month to files and can find the name of the original uploader. Anybody knows how to find the name listed as "author"? Extension:Media Viewer displays "author" so I assume it is stored somewhere. Any ideas? Jarekt (talk) 03:47, 26 December 2019 (UTC)

You will have to use API (or parsing of desciption pages). Database doesn't have that info. Also Mediaviewer gets author with API: today's POTD example Edgars2007 (talk) 09:12, 26 December 2019 (UTC)

Edgars2007, Thank you for reply. API is not a very useful solution as I was hoping to write SQL query that compares the uploader to the author (listed in {{Information }} template). Do SQL queries have access to the wikitext of the files? I could just get the author through regular expressions. Jarekt (talk) 23:34, 26 December 2019 (UTC)

Yes, I understand that it's not useful, creates a headache etc.  :)

And no, database doesn't have the wikitext of pages. Edgars2007 (talk) 11:43, 27 December 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

How do you find the original uploader of a file?

use commonswiki_p;
SELECT page_title, actor_name
FROM page
JOIN image ON img_name = page_title 
join actor on img_actor=actor_id # get the last uploader
WHERE page_namespace = 6
AND   page_id = 15730

lists the username of the last uploader of File:Mta_station_wall.jpg. How do you look up the name of the original uploader? Jarekt (talk) 14:53, 27 December 2019 (UTC)

Join it with 'revision' table and add the condition 'rev_parent_id = 0' which means that it is a page creation (no previous version). Then find the original uploader using 'rev_actor' field Tohaomg (talk) 15:14, 27 December 2019 (UTC)

Seems to be that only revision table has that information:

use commonswiki_p;
SELECT *
from revision
where rev_page=15730 and rev_parent_id=0

Edgars2007 (talk) 15:19, 27 December 2019 (UTC)

(that kind of was "edit conflict") Edgars2007 (talk) 15:20, 27 December 2019 (UTC)

How to get random quotes from en.wikiquote.org

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

How can I extract specific number of random quotes from en.wikiquote.org? Also The final list should contain the name of the person that the quote belongs to. 151.238.177.184 (talk) 19:13, 29 December 2019 (UTC)

The Wiki Replica databases contain metadata about pages and edits, but they do not contain the content of articles. The Action API can be used to fetch page content. action=query's list=random can probably be used to do what you are interested in. Try it out in en.wikiquote's API Sandbox. BDavis (WMF) (talk) 05:06, 30 December 2019 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

what is the query for retrieving articles of 8000 bytes in simple english wikipedia?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

can i get the query? Ramu ummadishetty (talk) 11:46, 31 December 2019 (UTC)

Is this what you wish?

quarry.wmflabs.org/query/41198 Quarryist (talk) 13:51, 7 January 2020 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.