Talk:Quarry/2017

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

External links in articles in a given category

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.

---

Given all pages in this category I would to find in what pages the is an external link to http://www.tilastopaja.net

Can you tell me what is wrong in this query.

Thanks in advance The Polish (talk) 18:46, 22 January 2017 (UTC)

It returned 0 rows, because you were querying enwiki, not plwiki :)

See updated (and rewritten) query. The main non-visual difference is "USE plwiki_p;" Edgars2007 (talk) 10:13, 23 January 2017 (UTC)

Thanks :D The Polish (talk) 10:58, 23 January 2017 (UTC)

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

Finding better image versions (SVG)

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.

---

My question was solved with https://quarry.wmflabs.org/query/16036 Abschreibbar (talk) 13:18, 3 February 2017 (UTC)

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

Wikidata queries

Some queries of Wikidata can not be made in SPARQL, for example I would like to query a time of creation of a specific property of specific item (Lets try P31 of Q1). I could use help with the query itself and with documentation of Wikidata SQL schema. Also is there an easy way of searching Quarry for Wikidata queries? Jarekt (talk) 19:25, 13 February 2017 (UTC)

yes https://www.wikidata.org/wiki/Wikidata:Quarry

Uziel302 (talk) 10:11, 25 May 2020 (UTC)

does quarry have API interface?

is there an API interface to query Quarry, or is the UI the only way to use it? peace. קיפודנחש (talk) 01:48, 22 February 2017 (UTC)

AFAIK, only UI Edgars2007 (talk) 13:11, 22 February 2017 (UTC)

Same here, I think you would need to request a Labs account to do API type stuff.y Reguyla (talk) 21:33, 2 March 2017 (UTC)

You can use directly the mediawiki API.

Or, you can consider posting a feature request for a Quarry API in phabricator. XXN (talk) 16:22, 12 May 2017 (UTC)

It would be wonderful to be able to use the quarry-output in a Python script, as the generator in Quarry is often a 1000 times faster.... Edoderoo (talk) 18:39, 22 June 2017 (UTC)

Well, that is possible. If you use quarry page id (not query id), then the url is: https://quarry.wmflabs.org/query/${id}/result/latest/0/json

I think you'll be able to move forward yourself; if not, say. Edgars2007 (talk) 06:20, 23 June 2017 (UTC)

splendid! Edoderoo (talk) 06:21, 23 June 2017 (UTC)

Rank column

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 a way to add a column to rank the results of the query? Like example: The first user in the list (query below) would have number 1, the second is 2... I mean giving me the ranks automatically without "#", which doesn't work with tables in Wikipedia.

I saw a query before with results like this and I couldn't find it.

https://quarry.wmflabs.org/query/16785 FShbib (talk) 13:19, 27 February 2017 (UTC)

I think this is what you wanted: https://quarry.wmflabs.org/query/16986 HenriqueCrang (talk) 14:27, 2 March 2017 (UTC)

Yes. Thank you, I appreciate your help. :) FShbib (talk) 14:33, 2 March 2017 (UTC)

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

Can someone help me with a query ?

Hello

I am looking for a query that would do that for me

• • list of all editors (by username)
  • who edited articles where talk pages are from that category (https://en.wikipedia.org/wiki/Category:WikiProject_Wiki_Loves_Women_translation)
  • in a list of languages (the full list is here : https://meta.wikimedia.org/wiki/16_African_Women_Translate-a-thon/languages but any query with 2 languages will do. I can then adapt it myself to add the other languages)
  • between date 1 and date 2 (practically speaking between 20th of Feb and the 08 of March 2017)
  • information to display : username, article name, language of the article edited
• Can anyone help me ?

Thanks Anthere (talk) 21:15, 2 March 2017 (UTC)

Is "Category:WikiProject_Wiki_Loves_Women_translation" used across all of the languages under consideration? Or is it maybe localized/translated on the individual wikis? Halfak (WMF) (talk) 16:28, 6 March 2017 (UTC)

Here's a query that gets all editors of all talk pages tagged with that category in English Wikipedia: https://quarry.wmflabs.org/query/17110 Halfak (WMF) (talk) 16:30, 6 March 2017 (UTC)

That's a good start ! Thanks. I go try to add the other elements. Anthere (talk) 17:05, 6 March 2017 (UTC)

Anthere accidentally edited one of my comments to reply. I've undone that change. Here's the message she posted:

It is only used on the English language at the moment. I could try to ask people to come up with translations equivalent, or I could try to stick the English version....

Halfak (WMF) (talk) 17:00, 6 March 2017 (UTC)

I always have troubles with Flow :( Sorry... Anthere (talk) 17:04, 6 March 2017 (UTC)

No worries. The difference between talk pages and flow is pretty big.  :) Halfak (WMF) (talk) 18:12, 6 March 2017 (UTC)

Quality Category of the Articles

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 all. Is there a query I can make to find out the quality tag for each article? For example, querying the IDs of those articles which belong to the quality class of Featured Articles (FA), etc? Anmoldalmia (talk) 10:29, 3 March 2017 (UTC)

Here you go: https://quarry.wmflabs.org/query/17108 Halfak (WMF) (talk) 16:25, 6 March 2017 (UTC)

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

Orphaned articles with linkings

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.

---

Given all pages containing the template orphaned on pl.wiki, I would to find what pages are not orphaned (one or more linkings).

Can somebody tell me what is wrong in this query. In the resultset there are all the linkings to the template orphaned.

Thanks in advance. The Polish (talk) 13:36, 12 March 2017 (UTC)

Negation is not needed, remove that "NOT". XXN (talk) 13:52, 12 March 2017 (UTC)

That is not the only problem because is not still working (now 0 results). For ex. there is the article Aleksiej Rybakow (polityk) that has 1 linking and in the talk page there is the template orphaned. I don't know why Quarry doesn't show it. The Polish (talk) 13:58, 12 March 2017 (UTC)

This is lvwiki version (ignore all those comments and stuff). We have a little bit different situation (we use template in articles) and the query may need some clean-up, but this one should be a good start Edgars2007 (talk) 14:03, 12 March 2017 (UTC)

@The Polish: Removed also "pl_from = page_id" in that subquery (as it's un-needed here), seems to work as expected. XXN (talk) 14:13, 12 March 2017 (UTC)

Thanks. The Polish (talk) 19:45, 12 March 2017 (UTC)

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

How would i get a list of all hip hop artists?

What title says. Not sure where they are located in the db 2607:FEA8:2CDF:F468:612A:3346:1588:DDA0 (talk) 00:10, 19 March 2017 (UTC)

An easy way to get such a list is via Petscan. Here is a list of results but it might also include few other articles. XXN (talk) 11:17, 19 March 2017 (UTC)

New pages

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, what is the parameter to discovery while users create more pages in a wiki? Mr. Fulano (talk) 00:06, 26 March 2017 (UTC)

Hmm... It'll be hard to do over-all (for performance reasons), but it's pretty easy to check how many page creations were done in the last 30 days. The recentchanges table has that information.

https://quarry.wmflabs.org/query/17668

• rc_type = 1 limits the query to new page creations
• rc_namespace = 0 limits he query to article namespace

Here's the top 10 article-page creators in the last 30 days:

!rc_user_text

!COUNT(*)

|-

|DBD

|2207

|-

|Wilhelmina Will

|2046

|-

|Sesamehoneytart

|1873

|-

|Lugnuts

|1030

|-

|Ss112

|939

|-

|AnomieBOT

|904

|-

|Lithopsian

|819

|-

|Matthiaspaul

|793

|-

|Chubbles

|763

|-

|SchreiberBike

|703

|} Halfak (WMF) (talk) 14:50, 29 March 2017 (UTC)

Thank you, I did this! Mr. Fulano (talk) 21:23, 29 March 2017 (UTC)

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

find a roman letter and greek letter next to it?

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 a way to search through Quarry for [A-Z][Α-Ω], it doesn't appear that a search on the english language wikipedia for insource:/[A-Z][Α-Ω]/ doesn't return any page at all. Naraht (talk) 00:54, 27 March 2017 (UTC)

No, you can't search wikitext via Quarry. Edgars2007 (talk) 07:16, 27 March 2017 (UTC)

Thank you. Still looking for a way to do this without having another editor pull it from a database dump. Naraht (talk) 14:23, 27 March 2017 (UTC)

@Naraht: a search for insource:/[A-Za-z]+[Α-Ωα-ω]+/ [1] on en.wp returns 859 pages. XXN (talk) 16:34, 12 May 2017 (UTC)

Thanx, over the last two months I've gotten more adept at using insource. Found some really odd usages... Naraht (talk) 22:24, 12 May 2017 (UTC)

@XXN Thank you. Naraht (talk) 22:25, 12 May 2017 (UTC)

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

which tables wikipedia uses

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.

---

which tables wikipedia uses Priyasadhale (talk) 05:09, 27 March 2017 (UTC)

Hello! Could you be more specific about what you want to know? here is the database schema Edgars2007 (talk) 06:47, 27 March 2017 (UTC)

As you might have guessed, you can also use "SHOW TABLES;". The database schema does not have several tables such as flaggedRev tables. You can check https://quarry.wmflabs.org/query/14669. Deniz (talk) 21:56, 25 April 2017 (UTC)

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

Is this a good place to find experts in querying Wikidata and Wikipedia?

We are looking for an expert to build custom queries on Wikidata and Wikipedia, is this the right place to be or if not any other suggestion? MaDeRoma (talk) 21:46, 11 April 2017 (UTC)

w:en:WP:Request a query for Wikipedia-related queries and d:WD:Request a query for Wikidata-related queries. Matěj Suchánek (talk) 13:54, 12 April 2017 (UTC)

Thanks MaDeRoma (talk) 14:08, 12 April 2017 (UTC)

I hang out here helping people with their queries.  :) What are you looking to query? Halfak (WMF) (talk) 20:07, 13 April 2017 (UTC)

== Getting info for category Daily Newspaper (Q1110794) ==

Hi can I ask here some help in writing a query? Actually I need to query Wikidata to get the title, description, all the "In more languages" items, all statements label, statement descriptions and links to some wikipedia languages pages for the category "Daily Newspaper"... 

Is this possible? Thanks for a feedback!

{{SPARQL

| query = SELECT ?newspaper ?newspaperLabel ?countryLabel ?publisherLabel ?siteLabel WHERE {

  ?newspaper wdt:P31 wd:Q1110794.

  OPTIONAL { ?newspaper wdt:P495 ?country. }

  OPTIONAL { ?newspaper wdt:P123 ?publisher. }

  OPTIONAL { ?newspaper wdt:P856 ?site. }

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }

}

}}

This is what I could get in ten minutes work. Maybe you want some more information, please let me know. Q.Zanden questions? 11:04, 13 April 2017 (UTC)

@Mdeninno: , this is some more information. Do you still need more?

{{SPARQL

| query = SELECT DISTINCT ?newspaperLabel ?newspaperDescription ?countryLabel ?publisherLabel ?site ?logo ?creationdate ?founder ?title ?popLabel ?lowLabel ?award WHERE {

  ?newspaper wdt:P31 wd:Q1110794.

  OPTIONAL { ?newspaper wdt:P495 ?country. }

  OPTIONAL { ?newspaper wdt:P123 ?publisher. }

  OPTIONAL { ?newspaper wdt:P856 ?site. }

  OPTIONAL { ?newspaper wdt:P154 ?logo. }

  OPTIONAL { ?newspaper wdt:P571 ?creationdate. }

  OPTIONAL { ?newspaper wdt:P112 ?founder. }

  OPTIONAL { ?newspaper wdt:P1476 ?title. }

  OPTIONAL { ?newspaper wdt:P291 ?pop. } #place of publication

  OPTIONAL { ?newspaper wdt:P364 ?low. } #language of original work

  OPTIONAL { ?newspaper wdt:P166 ?award. }

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,nl,de,it,es". }

}

}}

PS. please sign your message with four tildes (~~~~) Q.Zanden questions? 16:35, 13 April 2017 (UTC)

~~~~ @QZanden: This is good thanks a lot!

Last question: do you know how can I get the pointer to the en Wikipedia page for each record?

M

Here is it:

{{SPARQL

| query = SELECT DISTINCT ?newspaperLabel ?newspaperDescription ?countryLabel ?publisherLabel ?site ?logo ?creationdate ?founder ?title ?popLabel ?lowLabel ?award ?article WHERE {

  ?newspaper wdt:P31 wd:Q1110794.

  OPTIONAL { ?newspaper wdt:P495 ?country. }

  OPTIONAL { ?newspaper wdt:P123 ?publisher. }

  OPTIONAL { ?newspaper wdt:P856 ?site. }

  OPTIONAL { ?newspaper wdt:P154 ?logo. }

  OPTIONAL { ?newspaper wdt:P571 ?creationdate. }

  OPTIONAL { ?newspaper wdt:P112 ?founder. }

  OPTIONAL { ?newspaper wdt:P1476 ?title. }

  OPTIONAL { ?newspaper wdt:P291 ?pop. } #place of publication

  OPTIONAL { ?newspaper wdt:P364 ?low. } #language of original work

  OPTIONAL { ?newspaper wdt:P166 ?award. }

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,nl,de,it,es". }

  Optional { ?article schema:about ?newspaper.

             ?article schema:isPartOf <https://en.wikipedia.org/>.}

}

}}

Q.Zanden questions? 23:50, 13 April 2017 (UTC)

~~~~ @QZanden: Fantastic!

I just tried to remove the other languages but still getting multiple istances for some records.. didn't understood why...

M MaDeRoma (talk) 15:16, 14 April 2017 (UTC)

some querries cannot be 'unstarred'

I was trying to organize the favorite queries by their url (newer queries have bigger numbers in the url). I noticed that when I unstar and then star several queries, they do not effectively get unstarred; multiple copies start to exist. Please check https://quarry.wmflabs.org/query/11613 and https://quarry.wmflabs.org/query/11685 . Deniz (talk) 21:51, 25 April 2017 (UTC)

It's recommended to report this bug in http://phabricator.wikimedia.org XXN (talk) 20:25, 26 April 2017 (UTC)

Most visited pages

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,

1) Can I receive with quarry the 3000 of most visited pages in english wikipedia? If yes, how?

2) can I receive 100 of most visited pages in english wikipedia, which are not present in ukrainian wikipedia? Alex Blokha (talk) 21:34, 28 April 2017 (UTC)

1) No

2) Not in easy way Edgars2007 (talk) 06:12, 29 April 2017 (UTC)

Most visited pages are not available in quarry for now.

1) You can access the top 1000 viewed pages per project using the analytics pageview API (https://wikitech.wikimedia.org/wiki/Analytics/AQS/Pageviews#Most_viewed_articles)

2) This is not available as of now. JAllemandou (WMF) (talk) 09:21, 3 May 2017 (UTC)

1) Yes, I know about this tool, but I need more :) Alex Blokha (talk) 12:07, 3 May 2017 (UTC)

If you need more than 1000 pages, the only reasonable way to get that right now is to download the pageviews dumps and crunch the numbers yourself: https://dumps.wikimedia.org/other/pagecounts-ez/ Milimetric (WMF) (talk) 13:57, 8 May 2017 (UTC)

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

trwiki Edits

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,

How do I get a list of users editing in the last 3 days on trwiki? And with number of edit. By erdo can (talk) 13:46, 2 May 2017 (UTC)

https://quarry.wmflabs.org/query/18350 Halfak (WMF) (talk) 15:21, 2 May 2017 (UTC)

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

Is it possible to cancel a running 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.

---

is it possible to cancel a quarry? Tdcan (talk) 16:02, 20 May 2017 (UTC)

The only way to cancel a query is to run another one in the same edit pane. However, you shouldn't worry about canceling a query. Quarry has a query canceler that will automatically take care of a long running query. Halfak (WMF) (talk) 15:45, 24 May 2017 (UTC)

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

Select bigger articles

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,

How can I select all articles from german wiki, which are bigger by size, than articles in enwiki? Alex Blokha (talk) 00:22, 23 May 2017 (UTC)

Not in easy way. You could do two separate queries: 1) dewiki articles, that are in enwiki 2) enwiki articles, that are in dewiki. And then process it off-wiki with some programm. Edgars2007 (talk) 07:21, 23 May 2017 (UTC)

Omg. I didn't expect it is so complex. Alex Blokha (talk) 17:55, 23 May 2017 (UTC)

Is there any ablity to join them by wikidata id? Alex Blokha (talk) 22:15, 25 May 2017 (UTC)

https://quarry.wmflabs.org/query/14329

SELECT 
 enp.page_title as en_title,
 enp.page_len as en_len,
 dep.page_title as de_title,
 dep.page_len as de_len       
FROM enwiki_p.page enp
 JOIN enwiki_p.langlinks enll ON enll.ll_from = enp.page_id and ll_lang="de"
 JOIN dewiki_p.page dep on enll.ll_title = REPLACE(dep.page_title, '_', ' ')
WHERE enp.page_namespace = 0 and enp.page_is_redirect = 0 and dep.page_namespace = 0 and dep.page_is_redirect = 0
GROUP BY enp.page_id  
HAVING dep.page_len > enp.page_len  
LIMIT 50

Its runtime is long and is very close to time out on Quarry. XXN (talk) 19:44, 30 May 2017 (UTC)

You can post your answere here.

https://stackoverflow.com/questions/44167755/select-bigger-articles-among-two-wikipedias

I will mark it as answered. Alex Blokha (talk) 21:29, 7 June 2017 (UTC)

Ok :) XXN (talk) 22:06, 7 June 2017 (UTC)

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

LabsDB replica databases can drift from production originals

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.

---

Quarry is using a copy of the database that doesn't match the production database - there's clearly been some kind of corruption or replication problem. It first reared its head 13 months ago, and has now come back and is active at the moment. Please can Quarry be given a "clean" version of the db to feed on?

Email issues mean I can't get on Phabricator at the moment, but hopefully someone here can get something done. I do a lot of work with red-link and other problem categories on en.wiki, particularly with these three reports : | Uncategorized categories (Quarry), Categories categorized in red-linked categories (Quarry) and Red-linked categories with incoming links (Quarry).

Obviously you would expect every category in the first query to exist - but it became clear that there were four that were "stuck"in the query that had been deleted either on 22 April 2016 or 30/Apr/16. The second query has fourteen "zombies" - some cats that were also deleted on 22 April 2016 (and so shouldn't be in the report), and some cats that do exist but have a parent category that exists (which should disqualify them from the report) - in those cases the parent category was moved to its current name on 22 April 2016. I've even tried null edits and recreating some of these zombies and then deleting them, but it doesn't affect what happens in Quarry.

It gets worse on the third query, which now has 31 zombie cats all of which are empty and so shouldn't be in the query. A couple overlap with query 2, there's some birth and date ones whose only thing in common is the removal of a CfD tag on 1 May 2016, and there's maintenance categories - one deleted on 13 May 2016, one on 4 June 16 and one deleted on both 8 June 2016 and 14 August 2016. So far, a coherent story seems to be emerging - a big problem on 22 April 16, which trickled on for a few weeks afterwards but then was fixed.

Now it's back. If you look at the third query you'll see a bunch of maintenance categories from the last week, which were mostly deleted on 2 June 2017 but there's at least one which was deleted just 13 hours ago. So whatever this corruption/replication problem is, it has clearly come back.

From my point of view, all I want is Quarry to be working on a copy of the database that actually matches the production database. However, there's also a wider issue of what is the underlying cause of all this - and does it only affect replication to the Labs copy of the database, or is there a wider problem? Worms, meet can.... Le Deluge (talk) 13:43, 4 June 2017 (UTC)

The problem of deletions not replicating properly is known. The short answer is that MediaWiki does things in the database that the current replication strategy does not always deal with well. Work has been underway for several months to build a new database cluster that uses a different replication strategy that should miss fewer deletes and undeletes. I won't say that it will get rid of all of them because there are some database operations that MediaWiki does that are just very difficult to sync to the filtered replicas in the Cloud Services environment. As soon as the new database servers are fully populated for all shards we expect to change the configuration of Quarry to use them. BDavis (WMF) (talk) 22:25, 7 June 2017 (UTC)

Thanks for the reply (and I assume you had something to do with Quarry getting so much quicker recently, I now have queries taking 10-20% of the time - and more importantly not timing out!)

If what you're saying is that Quarry will be working off a "clean" copy of the database within a few months then that's good enough for me, I was more worried that I'd turned up something that had wider implications for the integrity of the database. And it seemed to have got a whole lot worse recently, although that may just be a particular thing to do with query 3 which has a lot of maintenance cats passing through it for a day or two, lately they've been coming in but not managing to get out again.

So just my curiosity remains - what's actually been happening with the secondary copies of eg en:category:Law_about_religion_by_country? Is it some kind of locking issue or something deeper? Le Deluge (talk) 12:50, 8 June 2017 (UTC)

There is some discussion of causes of replica drift on wikitech. There is also a tracking task in phabricator where you can report specific problems you find.

The TL;DR of why this happens is that MediaWiki performs some database changes which are not deterministic (they are based on the current state of the database at the time the change is made) and the wiki replicas hosted by Cloud Services are not identical copies of production. This combination makes for drift problems that must be resolved manually or by a very slow re-import process. The new cluster that is being prepared is using a different replication engine that we hope will make drift less common. That work is tracked in phab:T140788. The only thing that will eliminate drift is changing how MediaWiki does the problematic database operations. BDavis (WMF) (talk) 17:08, 8 June 2017 (UTC)

And the replication problem is ongoing - another 7 cats are still there that were deleted today such as here Le Deluge (talk) 13:42, 5 June 2017 (UTC)

Confirmed. See also [https://tools.wmflabs.org/supercount/?project=commons.wikimedia&user=Le_Deluge]: a lag of 30 hours, yesterday in the evening it has been 14 hours. :(( Achim (talk) 12:32, 6 June 2017 (UTC)

And now it's a bit over 3 hours. Thanks, it's a sign of database stress, but I'm not talking about something that's "just" a bit of replication lag. These queries seem to reflect what's happening in the production database pretty closely - a minute or two lag at most. What I'm talking about is individual entries getting "stuck" - for over a year in some cases.

It feels like these entries have ended up getting permanently locked on the Quarry db, apparently as a result of being moved or deleted on the production database (presumably at a time of database stress). The locks have been cleared on the production db but not on the Quarry db and as a result the entries on the Quarry db can't be updated.

At least, that's my guess. Le Deluge (talk) 15:19, 6 June 2017 (UTC)

The increasing lag mentioned above is now at 37 hours, subtracting 14 of yesterday, there is no replication at all.Yesterday at 12:32 (UTC) I saved a page on Commons the content of which isn't queriable via quarry by now. Achim (talk) 20:01, 6 June 2017 (UTC)

Ahem, it does sound less impressive if you accidentally divide by 10 like I did.... Again, we are at slight cross-purposes, I'm talking about the main en database which is not showing significant lag, even if the Commons database is struggling. But the fact that a major database is under such stress must mean that Wikimedia as a whole has a problem. Le Deluge (talk) 00:20, 7 June 2017 (UTC)

There is ongoing production maintenance of the "s4" shard that hosts the commons database. The latest at this writing is phab:T166206. This query of the server admin logs shows others happening recently.

You can check on the lag of particular database replicas using https://tools.wmflabs.org/replag/.

The DBA team has been working on building a new replica cluster for Labs/Cloud Services that we hope will be ready for everyone to use in the next month or two. This new cluster uses a different backend technology for replication (row based replication rather than binlogs) that should stay in better sync with the production servers. This process of bringing up a new cluster from scratch is pretty slow because we can't just copy the data directly from production. The wiki replicas are filtered to remove data that has been supressed and that filtering process needs to happen as the data is copied across rather than some operation after a full copy to try and remove it.

We don't like it when the replicas are out of sync by more than a second or two, but sometimes there isn't much that can be done to prevent it. You can take some amount of solace in knowing that back in the olden days of the toolserver replica lag of more than a week was not uncommon for all of the tables in all of the wikis. We are generally doing a lot better as time goes on. BDavis (WMF) (talk) 22:20, 7 June 2017 (UTC)

The s4 shard is back in sync, but there is another planned maintenance starting sometime on 2017-06-12 that will cause replication to lag for a few days (phab:T166206#3331928). BDavis (WMF) (talk) 16:48, 8 June 2017 (UTC)

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

How can insert Tilde(~) symbol as a delimiter after QNumber?

Kindly, Have a look at https://quarry.wmflabs.org/query/19420. How can i insert ~ symbol inbetween the Q-number and the label? Info-farmer (talk) 05:42, 16 June 2017 (UTC)

You can just prepend tildes to the second column:

CONCAT("Q",ips_item_id),CONCAT('~ ',ips_site_page)

or to append them the first column:

CONCAT("Q",ips_item_id,'~')

but if you need 3 columns, you can use:

CONCAT("Q",ips_item_id), CONCAT('~'), ips_site_page XXN (talk) 12:47, 16 June 2017 (UTC)

Note, that in second XXN's example, you can use '~' instead of CONCAT('~') Edgars2007 (talk) 03:26, 17 June 2017 (UTC)

Queries for orphaned pages don't work

I can't perform queries for orphaned pages on it.wiki since this week, neither with Quarry (seehttps://quarry.wmflabs.org/query/18648) or with Lists (see http://tools.wmflabs.org/lists/itwiki/Voci/Voci_orfane_per_dimensione). The syntax of the queries always worked until the coming up of this issue, but I don't get what's the reason why it doesn't complete the task now. Mess (talk) 15:27, 24 June 2017 (UTC)

You are right, my queries are killed as well. It's not the first time Quarry has slown down and - surprise!! - there are again a lot of queries that are "running" or queued since some weeks. Needs a clean restart, @Yuvipanda or someone else: please reset it, thanks. Achim (talk) 16:10, 24 June 2017 (UTC)

Hm, @Yuvipanda isn't maintaining this anymore sadly, but I'm watching these pages. I'll go try and figure out how to reset it. Milimetric (WMF) (talk) 13:31, 26 June 2017 (UTC)

User:Milimetric (WMF), thank you!. It's an old problem, see Talk:Quarry/2016#h-Quarry_may_have_hung_again?-2016-07-27T11:59:00.000Z, Talk:Quarry/2016#h-Hanging...-2016-06-29T18:30:00.000Z and several more. See also here: [2] Achim (talk) 13:43, 26 June 2017 (UTC)

User:Milimetric (WMF), may I suggest you to implement a maximum time limit of (just say) 1 hour for queued/hanged queries in order to prevent this overload? I can't figure out why some queries can avoid the regular limit of 30 minutes and can't be killed like any other query when the time period is over. Mess (talk) 13:59, 26 June 2017 (UTC)

Interesting, yeah, I thought there was a solid time limit in place. I'll double check that code if I can but sadly this is very low on my priority list. So far I found out I don't have rights to the Quarry box so I pinged some people to get that. I'll try and help if I can. Milimetric (WMF) (talk) 14:03, 26 June 2017 (UTC)

OK, thank you for your interest, @Milimetric (WMF). Meanwhile I've read the 2016 Community Wishlist Survey request for the Quarry maintenance (linked by @Achim55) and I've found out this old Phabricator task (https://phabricator.wikimedia.org/T139162), where Yuvipanda had published the SQL code to reset the queue. Mess (talk) 14:18, 26 June 2017 (UTC)

Ok, I got access to the project and restarted the celery workers (the things that run the queries). Let me know if things don't improve and I can try restarting more stuff. Milimetric (WMF) (talk) 18:31, 26 June 2017 (UTC)

User:Milimetric (WMF), it isn't better than before. Even my roughly shortened quarry:query/7250 is killed. Btw. I hate this idiotic editor. Achim (talk) 19:35, 26 June 2017 (UTC)

@Milimetric (WMF), maybe it should be better a whole cleaning: queries like [3], [4]or [5]are still running! Mess (talk) 20:54, 26 June 2017 (UTC)

I will now reboot the boxes, maybe that will help. Which editor? The quarry code editor? Milimetric (WMF) (talk) 16:16, 27 June 2017 (UTC)

Oh no, sorry, the editing of this page I had in mind. It differs a lot from wikis' and adding links is not an optimal solution as it isn't made for intuitive use. Dan, thank you for your efforts here! Achim (talk) 16:24, 27 June 2017 (UTC)

Ok, I rebooted all the boxes, and verified that some queries are running ok. I still see the status of the queries that @Mess pointed out as "Running" but that can't be because the box was rebooted. So that must just be invalid state in the db, those aren't running anymore. Milimetric (WMF) (talk) 16:25, 27 June 2017 (UTC)

@Achim55, yeah, Flow is a work in progress. For me, I'm a newcomer to wikitext so it's much much easier to work with Flow. If you're interested in working on it, I know the people on the team and they're super nice and some of the smartest people I know. Here's some links if you're interested:

browse the code: https://github.com/wikimedia/mediawiki-extensions-Flow

contribute: https://gerrit.wikimedia.org/r/#/admin/projects/mediawiki/extensions/Flow

The team hangs out in IRC under #wikimedia-collaboration Milimetric (WMF) (talk) 16:34, 27 June 2017 (UTC)

@Milimetric (WMF), I tried to re-run several times [my query], but nothing happened - and it's the same also for the [other one] re-executed by @Achim55. I'm starting to doubt about the codes of our queries, but they can't be faulty, as they worked regularly in the past. Mess (talk) 22:12, 29 June 2017 (UTC)

Confirmed. Doesn't work yet. Achim (talk) 20:35, 1 July 2017 (UTC)

I'm trying also to use the "LIMIT" clause to reduce the output in [this query test], but it's useless. It's like in some particular cases statements as "NOT IN" (or maybe what follows inside the parentheses) influence negatively the whole query (if I remove that, the query test runs without problems). Anyway, I don't know where to bang my head and solve this crazy puzzle. Mess (talk) 22:00, 1 July 2017 (UTC)

@Mess & @Achim55, I should have looked at your queries earlier. Yes, those NOT IN clauses are very intense to compute over categorylinks and pagelinks tables, those tables have a huge number of records. I would suggest re-writing. Sorry I don't have much time now to help. What I would try to do is re-write the NOT INs as JOINs. This might give the optimizer a better chance to make a plan that runs faster. If that doesn't work, do me a favor and explain in plain English what you're looking for and I can think about it when I have some more time. Milimetric (WMF) (talk) 22:16, 7 July 2017 (UTC)

@Milimetric (WMF), you're absolutely right. I've recently created two separated queries ( 20065and 20068) as a temporary solution for my task, even if I've already realized there was something "strange" in the query codes for their excessive time-consuming extraction (that we didn't care in the past simply because they didn't stopped themselves like now). I'll work again on the original code to find an ultimate solution, but probably I can carry on also with this alternative. Mess (talk) 05:37, 8 July 2017 (UTC)

@Milimetric (WMF): HOORAY! I DID IT! I've found what was bad in my query: in practice, the part where it searched for orphaned redirects was the real "point of failure", as it was badly implemented, so I just fixed it with a better code and now the query 18648 not only works again, but also accomplishes its task in only 3 minutes (a great improvement if compared with the 15 minutes it took previously). So, @Achim55, follow the advices above and try to find the part of code that takes too much time to run (I suggest you to split up the query and execute every single piece separately). Mess (talk) 12:52, 9 July 2017 (UTC)

Pardon?? "Write better code so you will no longer recognise the malfunction of Quarry." Fine. I think one should fasten the brake a bit more so that some action will become necessary... Achim (talk) 19:14, 10 July 2017 (UTC)

Well, no matter how amazing Quarry is, a statement like select * from revision will always break it on large enough wikis. No tool is substitute for writing good code. Think of writing bad code like throwing lots of kilowatts of energy in the trash. You wouldn't leave a 100W bulb on all day for no reason, so don't write inefficient SQL :) Milimetric (WMF) (talk) 13:06, 20 July 2017 (UTC)

Sorry for having been misunderstood: As I pointed out many months ago queries like "SELECT * FROM page" are regularly not killed but stay "running". That's the point. The "killing mechanism" is broken. In the near future we can celebrate an anniversary, I found a query that "runs" for nearly one year now. I don't know what happens in the background, but it's our experience that quarry gets slower the more queries are "running" for more than one hour instead of being killed. Achim (talk) 20:30, 20 July 2017 (UTC)

Oh, as far as I can tell those queries are killed, the interface just doesn't show that status as "Killed". So there are some bugs but they're not affecting the system performance as far as I can tell. There's a discussion going on about replacing Quarry with a tool developed independent of WMF, so we can take advantage of regular updates. Here's that thread: https://phabricator.wikimedia.org/T169452 Milimetric (WMF) (talk) 15:06, 26 July 2017 (UTC)

Queries are killed, but the status updating system may break due to various reasons. See phab:project/board/800/ column "Bugs (Endless Query Run)". Unfortunately many of the old logs are lost so I'm unable to debug them. Please file a ticket if you see any such behaviour. Zhuyifei1999 (talk) 23:57, 1 August 2017 (UTC)

@Zhuyifei1999, that's fine so far. So let me ask in a different way: Half a year ago [6] needed 10 ... 12 minutes for one run. Today it needs 25...30 minutes. Why is quarry working extremely slowly now? Achim (talk) 20:08, 8 September 2017 (UTC)

This is not Quarry's problem. The query runs on the wiki replicas and they may get slower due to excess use and more data processing, and for Wiki Replica issues it's best to consult the DBA. However, I may try to optimize the query a bit. Zhuyifei1999 (talk) 20:51, 8 September 2017 (UTC)

Never mind. I'd suggest you to ask the DBAs regarding query optimization or wait till quarry switch to the new Wiki Replica servers some time soon (in next next few weeks/months). Zhuyifei1999 (talk) 21:11, 8 September 2017 (UTC)

Quarry has switched to the new Replica servers yesterday, which is said to be 5x faster, so you may want to try the query again. Note that 'USE stopthisrun' won't work on the new servers (and idk what even are 'stopthisrun') Zhuyifei1999 (talk) 19:55, 26 September 2017 (UTC)

Thank you very much for notifying. I use non-existing db 'stopthisrun' for quickly killing my query if I see an error after I already had started it. Achim (talk) 20:06, 26 September 2017 (UTC)

How can i separate the labels without bracket, comma, etc.

This Quarry contains the labels with brackets, comma, etc., How can i separate the labels with brackets, comma and the labels without any special characters? Why i am asking is i want to follow the wikidata rules here. Info-farmer (talk) 17:08, 25 June 2017 (UTC)

Maybe I've understood what you need. You want to separate the ""Q",ips_item_id" and "ips_site_page" in two different columns, right? Well, so write this SELECT command:

SELECT CONCAT("Q",ips_item_id), ips_site_page

and you'll split them without using tildes or other special characters.

If you want also to get rid of commas and brackets into the labels upon the columns, then write like this:

SELECT CONCAT("Q",ips_item_id) AS ips_item_id, ips_site_page

(as I've done over there: https://quarry.wmflabs.org/query/19715) Mess (talk) 19:52, 25 June 2017 (UTC)

Excuse me. sorry for my English language.

Let me furnish my need clearly.

I want ips_site_pages without comma and also without brackets.

Example,

the following pages should not come into my result

Q28235 ~ தூரமேற்கு வளர்ச்சி பிராந்தியம், நேபாளம்

Q41559 ~வெள்ளையன்கள் (பட்டாம்பூச்சிக்குடும்பம்)

If possible, i want the result separately as follows.

ips_site_pages without comma and also without brackets, etc

ips_site_pages with comma .

ips_site_pages with brackets. Info-farmer (talk) 00:58, 26 June 2017 (UTC)

I see. So you want to detect which ips_site_pages values have:

1. no commas or parentheses (you call them brackets, but those are usually [ or {. These: ( are parentheses).

2. only commas but no parentheses

3. only parentheses but no commas

You can use the "if" function in SQL and order your results by that, that's one way. So taking your original query and adding that logic would look like this:

use wikidatawiki_p;
 select CONCAT("Q",ips_item_id,'~',ips_site_page),
        if(ips_site_page not like '%,%' and ips_site_page not like '%(%' and ips_site_page not like '%)%',
            1, if(ips_site_page like '%,%' and ips_site_page not like '%(%' and ips_site_page not like '%)%',
                  2, if(ips_site_page not like '%,%' and (ips_site_page like '%(%' or ips_site_page like '%)%'),
                      3, 4
                  )
            )
        ) as ips_item_id_type
   from wb_items_per_site i
  where i.ips_site_id='tawiki'
    and not exists
        (select term_entity_id
           from wb_terms
          where i.ips_item_id = term_entity_id
            and term_type = "label"
            and term_language='ta')
  group by ips_item_id
  order by ips_item_id_type
  limit 3000;

You can play with it here: https://quarry.wmflabs.org/query/19793. The results are sorted by the type we identified in the if statement, so you have to look at page 30 or so to find results with type 3 for example.

There are other ways to write the string comparisons, but the "like" and "not like" operators are really straightforward so I went with that. Milimetric (WMF) (talk) 14:00, 26 June 2017 (UTC)

Thanks indeed. How can i get all the labels of ta.wikipedia or te.wikipedia? Is it possible to get all labels in batches? For example, 1 to 10,000 then, 10,000 to 20,000 and so on. Info-farmer (talk) 16:38, 27 June 2017 (UTC)

Well, your where clause has "where i.ips_site_id='tawiki'" so that's limiting results to ta.wikipedia. Just change it to tewiki for te.wikipedia, or do "where i.ips_site_id in ('tawiki', 'tewiki')" to get both, but then be careful with your term_language = 'ta' clause, you might want to do it one at a time to keep it simple.

It would be easier for the system to just return all the results at once rather than get batches of 1-10000, 10000-20000, and so on. Is there a problem with just using the pagination in the results? You can figure out what page to go to based on the results count per page. Milimetric (WMF) (talk) 15:22, 28 June 2017 (UTC)

hyperlinks in Quarry results

Tracked in Phabricator
Task T74874

Hi, is there any way to create queries which results would be rendered as links in Quarry?

Eg. if i make query like this i need to copy paste it to the wiki to render wikitext to hyperlinks. However it is extra step if same thing can be done directly inside Quarry.

- https://quarry.wmflabs.org/query/19995 Zache (talk) 08:42, 4 July 2017 (UTC)

Currently no. https://phabricator.wikimedia.org/T74874 XXN (talk) 10:31, 4 July 2017 (UTC)

Index pages not linked with authors page.

I need one quarries, where BNWS have few pages not linked the authors' page. We are manually added this with the following template Template:Small scan link at Authors page. Jayantanth (talk) 21:18, 23 July 2017 (UTC)

I'm sorry. I'm not sure I understand. What's BNWS? What are you hoping the query will return? Halfak (WMF) (talk) 13:53, 25 July 2017 (UTC)

Restore 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. I have a huge problem. I deleted unintentionally a very important query, a couple of minutes ago. Is there a way to restore it? Some kind of archive? It was there for months, maybe years. Is this possible? I really do not want to start to restore it from scratch. Please, help me. Thank you very much. IKhitron (talk) 18:42, 5 September 2017 (UTC)

Which query? Do you want the results or the SQL? I may be able to check the Quarry database and see if they are archived somewhere. Zhuyifei1999 (talk) 19:22, 5 September 2017 (UTC)

Thank you very much for your answer, Zhuyifei1999. 4441. It's SQL only. Crossing fingers. IKhitron (talk) 19:49, 5 September 2017 (UTC)

The second to last revision (id 206077, timestamp 2017-09-05 18:38:01) SQL was, AFAICT:

use hewiki_p;
select replace(page_title, "_", " ") as article, el_to as address from page join externallinks on page_id = el_from
where (el_to like "%action=edit%" or el_to like "%org/w/index.php%" or (el_to like "%wik%org/w%" and not el_to like "%upload.wikimedia.org%")
       or el_to like "%upload.wikimedia.org/wikipedia/he%"or el_to like "%upload.wikimedia.org/wikipedia/commons%"
       or el_to like "%stats.wikimed%" or el_to like "https://www.google.co.il/url?%")
and page_namespace = 0 and not el_to like "%rationalwiki.%" and not el_to like "%action=history%"
and not el_to like "%jugglewiki.%" and not el_to like "%openoffice%" and not el_to like "%wikileaks.%" and not el_to like "%strategywiki%"
and not el_to like "%osx86project.%" and not el_to like "%postgresql.%" and not el_to like "%wikigallery.%" and not el_to like "%wikisage.%"
and not el_to like "%wikiberal.%" and not el_to like "http://www.yeshiva.org.il/wiki/%" and not el_to like "http://clinfowiki.org/%"
and not el_to like "https://www.wikidata.org/wiki/Q%?uselang=he#P%" and not el_to like "%stswiki.%" and not el_to like "%inkscape.%"
and not el_to = "http://la.wikisource.org/wiki/Disputatio_pro_declaratione_virtutis_indulgentiarum?match=en" and not el_to like "%antwiki.%"
and not el_to like "%whatwg.%" and not el_to like "%ejwiki%" and not page_title in ("אולואיד", "ויקיפדיה_הבולגרית")
and not el_to like "%translate.google.com%" and not el_to like "%uselang=he%"  and not el_to like "%page=%" order by article # 21263

Zhuyifei1999 (talk) 23:42, 5 September 2017 (UTC)

Zhuyifei1999, Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! IKhitron (talk) 23:53, 5 September 2017 (UTC)

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

Excluding blue links come from a template?

I wonder is it possible to exclude blue links come from a template? The querry is here: https://quarry.wmflabs.org/query/21396 Estopedist1 (talk) 10:07, 6 September 2017 (UTC)

EXCEL download bug

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 again, Zhuyifei1999. Maybe you remember, a couple of month ago you created for me a new download option, excel. I see now it has a bug. I prefer to ask you here, not in phab, because maybe you aware of this. When I download the same 4441 query you saved, the result has all the article names, but only some URLs. What do you think about this? Thank you. IKhitron (talk) 14:24, 6 September 2017 (UTC)

Uh, works for me (LibreOffice Calc). I don't have Microsoft Excel so I have no idea why it is not working on Excel. Zhuyifei1999 (talk) 15:10, 6 September 2017 (UTC)

So what's we can do, Zhuyifei1999? Nobody can work when half a data is deleted. Thank you. IKhitron (talk) 15:12, 6 September 2017 (UTC)

I'll ask some friends of mine who has Excel to check them; might take some time though. If I forget (or take too long) please file a ticket in Phabricator. Zhuyifei1999 (talk) 15:16, 6 September 2017 (UTC)

Very well, Zhuyifei1999. Can I suggest a possible cause? It's from my work experience as programmer, it has nothing to do with wiki. IKhitron (talk) 15:18, 6 September 2017 (UTC)

Cannot reproduce for https://quarry.wmflabs.org/run/202117/output/0/xlsx?download=true on Microsoft Excel for Mac, Version 15.37 Zhuyifei1999 (talk) 22:41, 6 September 2017 (UTC)

Well, Zhuyifei1999, that means it's more possible I'm right. If I am, the problem is with some fields with more than 256 bytes length. Thank you. IKhitron (talk) 22:51, 6 September 2017 (UTC)

Can you test that out? Eg. with SELECT "SOMETEXT"; varying the length of the text. Since there is no way for me to find any text missing I cannot easily help you, unfortunately. Zhuyifei1999 (talk) 22:59, 6 September 2017 (UTC)

Try 21263, please. I have there a lot of long links. Thank you. IKhitron (talk) 13:36, 7 September 2017 (UTC)

Ok I can reproduce this on LibreOffice Calc, Version 5.1.6.2. Filed phab:T175285 to track this. Zhuyifei1999 (talk) 16:09, 7 September 2017 (UTC)

Thank you. IKhitron (talk) 16:44, 7 September 2017 (UTC)

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

QSL approve

Hello, https://quarry.wmflabs.org/query/21866 i need the log_action or log_type of patrol

subtype=patrol

and review

subtype=accept

subtype=unaccept

like this https://en.wikipedia.org/w/index.php?title=Special%3ALog&type=patrol&user=&page=&year=&month=-1&tagfilter=&subtype=patrol

and this https://en.wikipedia.org/w/index.php?title=Special%3ALog&type=review&user=&page=&year=&month=-1&tagfilter=&subtype=accept

https://en.wikipedia.org/w/index.php?title=Special%3ALog&type=review&user=&page=&year=&month=-1&tagfilter=&subtype=unaccept جار الله (talk) 18:47, 1 October 2017 (UTC)

Hello, how can we help you ? :) Framawiki (talk) 18:21, 2 October 2017 (UTC)

@Framawiki I wonder if there is log_action or log_type for review of accept

and unaccept edit? جار الله (talk) 02:42, 3 October 2017 (UTC)

Looking for templates' names 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 am trying to extract pages in Wikisource which contain a particular template. I have the page table, the templatelinks table, but can't find the table which stores the template names. — Ineuw talk 03:48, 3 November 2017 (UTC)

Templates are also pages in page table. The tl_namespace and tl_title columns are pointers back to the page_namespace and page_title columns of the page table. The "Template" namespace is namespace 10. So if you wanted to find pages in the main namespace that transclude the Template:Table_style template:

USE enwikisource_p;
SELECT page_title
FROM templatelinks
LEFT JOIN page ON (tl_from = page_id AND tl_from_namespace = page_namespace)
WHERE tl_namespace = 10
  AND tl_title = 'Table_style'
  AND tl_from_namespace = 0;

BDavis (WMF) (talk) 04:44, 3 November 2017 (UTC)

Much thanks, I guess you saw me struggling with it. :-) — Ineuw talk 06:08, 3 November 2017 (UTC)

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

text table

Hello,

I need to get the text from many pages. To start understanding the scheme I wanted to get the text from a specific page.

However when I go to revision table all of the rev_text_id column in filled with zeros.

Also, there is an exception thrown when I'm trying to use text table saying it doesn't exist.

Do you know why does this happen? are we not allowed to mine that data? does anyone know how to solve this (via Quarry or alternative ways)?

Thanks for any help! Gfdgss (talk) 12:24, 4 November 2017 (UTC)

It is my understanding that the text table contains the wikitext of all revisions of all pages, including deleted pages. Deleted content only is meant to be available for admins, so I'd imagine that the restrictions you are facing are in place for that reason. Stefan2 (talk) 12:32, 4 November 2017 (UTC)

Thanks Srefan2.

Meaning there is not anyway to access the text of a page?

I know Wikipedia let's you download dumps. Don't these files include all the page text as a part of them? If so- if we can get the text from those files, why can't we do it from here as well?

Moreover, in revision table you have a field saying if the action done on the edited page was deleting it, therefore they could screen those deleted pages out real easy and still give us access to text table. Gfdgss (talk) 13:13, 4 November 2017 (UTC)

I don't know what the database dumps contain or how they differ from the database copy available on Toollabs.

The revision table has a field called rev_deleted which tells if the revision has been deleted using revision deletion. If the entire page is deleted, the revisions end up in the archive table instead. In both cases, the text seems to reside in the text table (provided that the content wasn't deleted before upgrading the servers to MediaWiki 1.5, in 2005 or something).

I think I tried to look up something in the text table some time ago but failed, although I don't remember exactly what happened. Note that there is no key on the old_text field, so the execution time of a query will be proportional to the number of entries in the table (very slow) as opposed to the logarithm of the number of entries (faster). Also, the documentation says that the text can be difficult to get (for example, it may be gzipped). I don't know if WMF stores page content in compressed form or not. Stefan2 (talk) 16:31, 4 November 2017 (UTC)

I highly appreciate your in-depth reply.

Do you know of any way I can use the current scheme to find which users have Barnstars or Service Awards on their user page?

If so- is there any way of knowing what type exactly and when was it given?

Thanks Gfdgss (talk) 17:52, 4 November 2017 (UTC)

The linked page says that the barnstar templates normally are substituted, so you can't search for template transclusions. However, you could use the imagelinks table to find all user pages which transclude File:Original Barnstar.png (or other barnstar images). If the page contains a barnstar image, then there's a fair chance that the person has been granted a barnstar.

For service awards, you could similarly use the templatelinks table to find all pages which transclude w:Template:Registered Editor and similar templates.

Once you have a list of user pages, then I suppose that you could write a computer program which downloads specific information (such as the wikitext) from the API if there's more information you need. Stefan2 (talk) 18:08, 4 November 2017 (UTC)

Is the query actually running or not?

I gave a query to quarry about all the articles and their categories - it's logical it will need some time. The query status is running, but when I press explain, it says "Error: Hmm... Is the SQL actually running?! ". Which is it then? Mmaarrkkooss (talk) 08:54, 9 November 2017 (UTC)

Which query? Could you give a link? Zhuyifei1999 (talk) 15:05, 9 November 2017 (UTC)

It says running but here you go : https://quarry.wmflabs.org/query/22865 Mmaarrkkooss (talk) 15:21, 9 November 2017 (UTC)

I might been doing this wrong however. Mmaarrkkooss (talk) 15:23, 9 November 2017 (UTC)

From the logs, the query most likely generated too many results for quarry to store them. You hit phab:T172086. Zhuyifei1999 (talk) 18:05, 9 November 2017 (UTC)

Where can I see the logs? How could I run a query that big? Mmaarrkkooss (talk) 19:57, 9 November 2017 (UTC)

MariaDB [enwiki_p]> SELECT COUNT(1) FROM categorylinks INNER JOIN page p1 ON (p1.page_id = cl_from AND (p1.page_namespace = 0)) LEFT JOIN page p2 ON (p2.page_namespace = '14' AND (p2.page_title = cl_to)) LEFT JOIN page_props ON ((pp_page=p2.page_id) AND pp_propname = 'hiddencat') WHERE   (pp_propname IS NULL);
+----------+
| COUNT(1) |
+----------+
| 28917820 |
+----------+
1 row in set (9 min 7.29 sec)
MariaDB [enwiki_p]> Select COUNT(1) from page;
+----------+
| COUNT(1) |
+----------+
| 43592182 |
+----------+
1 row in set (2 min 1.31 sec)

Quarry will not and should not be able to store result sets this large. As for the logs, they are as files on the runner instances, and I do not know of an easy way to expose them. Zhuyifei1999 (talk) 23:19, 9 November 2017 (UTC)

Thanks for the insights. Do you know how could obtain such large data from that query? Mmaarrkkooss (talk) 07:22, 10 November 2017 (UTC)

Also, the above shell is it quarry? Cause it doesn't look so. Mmaarrkkooss (talk) 07:26, 10 November 2017 (UTC)

For large amounts of offline data, you can use https://dumps.wikimedia.org/enwiki/. The above shell is directly querying the Wiki Replicas servers on Toolforge, but quarry will produce the same results if the same query is run at the same time, since it connects to the exact same servers. Zhuyifei1999 (talk) 14:31, 10 November 2017 (UTC)

I have in fact downloaded these sql tables from the dumps and I am writing them in a database (which is a slow process) but I have a fear that this query won't work either. Mmaarrkkooss (talk) 15:50, 10 November 2017 (UTC)

Hello again.

I am thinking of using pagination and limiting the results. How many do you think I could get away with? 1 million records? More ? Less? Mmaarrkkooss (talk) 14:21, 11 November 2017 (UTC)

500k is doable. Mmaarrkkooss (talk) 23:11, 11 November 2017 (UTC)

Even though 500k was doable with Limit 5000000 (I don't have the link because I queried from the same link) giving Limit 500k,500k in https://quarry.wmflabs.org/query/22970 is not working. Damn is this frustrating

edit : first 500k results : https://quarry.wmflabs.org/query/22969

edit2: nvm Mmaarrkkooss (talk) 01:17, 12 November 2017 (UTC)

List of users with at least 10 edits

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! We're planning on running a survey on the Northern Sami Wikipedia, and I think the most efficient way to get a list of who to get in touch with would be to run a query on Quarry. But I don't know how to construct such a query (never used SQL…), so I'm wondering if someone could help me. What I want is basically just a list of all registered users who have made more than 10 edits ever. Can someone help me with that? Jon Harald Søby (WMNO) (talk) 14:41, 16 November 2017 (UTC)

https://quarry.wmflabs.org/query/23113 Edgars2007 (talk) 15:18, 16 November 2017 (UTC)

Note that user_editcount let's say isn't very precise, it could be some(?) edits off the real numbers. But for this purpose it probably will be fine. Edgars2007 (talk) 15:20, 16 November 2017 (UTC)

Thank you very much, Edgars2007! Jon Harald Søby (WMNO) (talk) 16:54, 16 November 2017 (UTC)

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

User Table

Hi guys!

I want to fetch the "user" table Wikipedia has. I know Wikipedia limits the fields we can view but the ones open are just fine for me.

I understood such large queries will not work in "QUARRY" and also when looking in the dumps I could not find any SQL or XML dump of that table.

Any help you might have will be appreciated! Bsdla (talk) 07:54, 29 November 2017 (UTC)

Indeed I don't believe there is a dump of this information which, it seems to me, is an oversight. So I've created a task: T181720 Halfak (WMF) (talk) 14:49, 30 November 2017 (UTC)

Looks like this was already requested a long time ago. I've merged my ticket with the old one: T51132 Halfak (WMF) (talk) 14:52, 30 November 2017 (UTC)

Thank you so much!

You think this is something that would be done anytime soon? As a part of my research, I'm trying to have some insights on Wikipedia users which would benefit the community and it's a bit urgent. Bsdla (talk) 11:52, 2 December 2017 (UTC)

I might be able to help you get a one time dump of the current user table. Could you tell me more about the study?

If you're planning to share the results with the community, I recommend starting up a project page at m:R:New project.  :) Halfak (WMF) (talk) 14:59, 8 December 2017 (UTC)

Sure! thanks about that.

I'll try to write you an email soon :) Bsdla (talk) 19:28, 11 December 2017 (UTC)

Revision Table

Hi guys!

One more important question I forgot to ask..

I am also looking for "revision" table. Again- the same problem with large queries :)

So I looked in the dumps and found the following:

when searching "revision" I got to 2 dumps that I think might help me- "Recombine first-pass for page XML data dumps" and "First-pass for page XML data dumps"

What is the difference between them? they both state at the beginning "These files contain no page text, only revision metadata."

Also, when looking into these sections the files there are divided to 3: stub-meta-history,  stub-meta-current, stub-articles.

What is the difference between them in the means of what fields they include? of what sections in the site and of what time-frame?

My goal is to fetch for every user his whole edit history including- on which pages, how long was the edit, and the date of the edit.

Thanks for any help that may come! :) Bsdla (talk) 08:10, 29 November 2017 (UTC)

I would advise to ask this specific question at enwiki w:WP:VPT, if nobody answers here. Edgars2007 (talk) 08:36, 29 November 2017 (UTC)

Thanks,

I'll make sure to ask there now as well. Bsdla (talk) 15:54, 29 November 2017 (UTC)

stub-meta-history

Contains metadata about revisions to all pages

stub-meta-current

contains metadata for only the most recent revision of all pages.

stub-articles

contains metadata for only the most recent revision of "content" pages (articles, templates, etc.) Halfak (WMF) (talk) 14:55, 30 November 2017 (UTC)

Thank!

Therefore I understand stub-meta-history contains recent and old version together right? Bsdla (talk) 11:44, 2 December 2017 (UTC)

That's right. :) Halfak (WMF) (talk) 23:42, 7 December 2017 (UTC)

A kind of What Links here

Hello,

Do you know how to get the list of pages that reuse a Commons image? A kind of "What Links here". I would like to know which wikipedia (or any other wiki projet) uses images from a certain category. Any clue? African Hope (talk) 23:49, 3 December 2017 (UTC)

USE commonswiki_p;

DESCRIBE globalimagelinks;

Unfortunately there is no detailed description at Manual:Database layout. Achim (talk) 17:02, 4 December 2017 (UTC)

Thanks very much@Achim55, Implemented here to get the number of images reused by Côte d'Ivoire during Wiki Loves Africa. Few adjustments left but it looks good so far. African Hope (talk) 23:14, 6 December 2017 (UTC)

Medical Encyclopedia..

Hi,

I need to collect all medical related articles in medical categories and put in a separate website containing a medical encyclopedia.. any clue how to collect such data in an sql query?

Thanks.. Balligho (talk) 14:16, 7 December 2017 (UTC)

any reply please? I have mediawiki installed I need the database only..

Thanks.. Balligho (talk) 11:07, 8 December 2017 (UTC)

I can pay money if required.. Balligho (talk) 11:07, 8 December 2017 (UTC)

This query will get you all the titles of Medical Related articles. https://quarry.wmflabs.org/query/23504 From this, you can export/import using Special:Export. For example, en:Special:Export/Warren_Fales_Draper

If you do copy content over to another site, make sure to carry the attribution from Wikipedia as per CC-By-SA https://creativecommons.org/licenses/by-sa/4.0/legalcode or you might get Wikimedia's lawyers to come to shut you down. Halfak (WMF) (talk) 14:56, 8 December 2017 (UTC)

I'm sorry I didn't understand the legal thing, I'm gonna keep everything as it is and people will know its wikipedia.. Balligho (talk) 19:12, 8 December 2017 (UTC)

I believe that if you clearly link directly back to the article you copy over, that will work just fine.  :)

(I am not a lawyer. This is not legal advice.) Halfak (WMF) (talk) 22:41, 12 December 2017 (UTC)

missing revisions

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,

I installed Mediawiki and imported the ENTIRE database.. but alot of articles are missing, it shows a message like this:

***************************************************

Medicine

From Wikipedia, the free encyclopedia

The revision #804651416 of the page named "Medicine" does not exist.

This is usually caused by following an outdated history link to a page that has been deleted. Details can be found in the deletion log.

***************************************************

any help please?

Thanks.. Balligho (talk) 21:46, 13 December 2017 (UTC)

Hello, it looks like it is out of scope of Talk:Quarry. Perhaps you can find more information at Support Desk. Framawiki (talk) 10:05, 17 December 2017 (UTC)

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

Number of entries

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.

---

Hey guys,

Is there a way to know the number of entries/rows of a table stright away without running a count(*) query?

Right now I'm trying to understand the size of templatelinks but could be relevant for any other table.

Thanks in advance! 132.73.198.144 (talk) 19:36, 26 December 2017 (UTC)

Can anybody please help? any clue? I'm sure the table size is mentioned somewhere.

Thanks Bsdla (talk) 09:26, 2 January 2018 (UTC)

The database engine keeps track of estimated table sizes but you can't access those through Quarry because the information is in the system database. Giving access to the system database is not a great idea, so select count(*) is about the only way you can see the size of the table. Milimetric (WMF) (talk) 15:43, 2 January 2018 (UTC)

You can do something to get the count more quickly. If you run a query like this: https://quarry.wmflabs.org/query/23874

SELECT COUNT(*) FROM revision;

And then click on "Explain" while the query executes, you'll see that the query engine predicts that it will need to scan 716678936 rows -- which is a good approximation of how tall the table is. Halfak (WMF) (talk) 22:25, 2 January 2018 (UTC)

Thank you both.

Halfak, I'll try that. Bsdla (talk) 17:27, 8 January 2018 (UTC)

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

Help with 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.

---

Hi Everyone,

I was wondering if anyone can help me with a query i am trying to run on Quarry. I would like to get a dataset of all Oil & Gas companies and their (name, location, total assets, total revenues, total reserves). Eventually i would also like to get a list of oil and gas service providers with the same parameters/columns.

Anyway anyone can help me build this query or guide me on how to create it? I am new to sql and i don't understand the database schema very well for wikipedia.

thanks again for your help Nickshel81 (talk) 05:13, 30 December 2017 (UTC)

Quarry deals with technical information, like links between pages. Structured data is provided by Wikidata via Wikidata Query Service. Matěj Suchánek (talk) 09:40, 30 December 2017 (UTC)

Thanks Matej. I have actually tried using Wikidata query service and the results were not consistent. I will post my question their along with the query i executed and see if anyone can help me refine it.

thanks again for your help. Nickshel81 (talk) 15:44, 30 December 2017 (UTC)

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