Talk:Quarry/2020

Previous discussion was archived at Talk:Quarry/Archive 1 on 17 April 2015. Discussion area for discussion about Quarry itself and help about individual queries.

Pages that have been deleted and restored?

I'm trying to find pages which a given user has deleted, and were subsequently restored (by anybody). This seems like it should be the right query:

select count(*)

from logging_userindex lu

join logging_logindex ll on lu.log_page = ll.log_page

join actor on lu.log_actor = actor_id

where actor_name = 'RoySmith'

and lu.log_type = 'delete'

and lu.log_action = 'delete'

and ll.log_type = 'delete'

and ll.log_action = 'restore'

;

but it's taking forever to run. I'm assuming it's the self-join on logging that's the problem. Is there a better way to do this? RoySmith (talk) 20:09, 2 January 2020 (UTC)

EXPLAIN:

+------+-------------+---------------------+-------+----------------------------------------------------------------------------+---------------------+---------+------------------------+-----------+------------------------------------+
| id   | select_type | table               | type  | possible_keys                                                              | key                 | key_len | ref                    | rows      | Extra                              |
+------+-------------+---------------------+-------+----------------------------------------------------------------------------+---------------------+---------+------------------------+-----------+------------------------------------+
|    1 | PRIMARY     | actor               | const | PRIMARY,actor_name                                                         | actor_name          | 257     | const                  |         1 |                                    |
|    1 | PRIMARY     | logging             | ref   | type_time,actor_time,log_actor_type_time,log_actor_deleted,log_type_action | log_actor_type_time | 42      | const,const            |     12316 | Using index condition; Using where |
|    1 | PRIMARY     | logging             | ref   | type_time,log_page_id_time,log_type_action                                 | log_page_id_time    | 5       | func                   |         2 | Using index condition; Using where |
|   13 | SUBQUERY    | revision            | ALL   | PRIMARY                                                                    | NULL                | NULL    | NULL                   | 778735536 | Using where                        |
|   13 | SUBQUERY    | revision_actor_temp | ref   | PRIMARY,revactor_rev                                                       | PRIMARY             | 4       | enwiki.revision.rev_id |         1 | Using index                        |
|   12 | SUBQUERY    | logging             | range | type_time,actor_time,log_actor_type_time,log_actor_deleted,log_type_action | log_actor_type_time | 42      | NULL                   |     15784 | Using index condition; Using where |
|   11 | SUBQUERY    | recentchanges       | ref   | rc_actor,rc_actor_deleted                                                  | rc_actor_deleted    | 8       | const                  |       878 | Using where; Using index           |
|   10 | SUBQUERY    | filearchive         | ref   | fa_actor_timestamp,fa_actor_deleted                                        | fa_actor_deleted    | 8       | const                  |        15 | Using where; Using index           |
|    9 | SUBQUERY    | oldimage            | ref   | oi_actor_timestamp,oi_actor_deleted                                        | oi_actor_deleted    | 8       | const                  |         1 | Using where; Using index           |
|    8 | SUBQUERY    | image               | ref   | img_actor_timestamp                                                        | img_actor_timestamp | 8       | const                  |        14 | Using index                        |
|    7 | SUBQUERY    | ipblocks            | ref   | ipb_actor_deleted                                                          | ipb_actor_deleted   | 9       | const,const            |       317 | Using index                        |
|    6 | SUBQUERY    | archive             | ref   | ar_actor_timestamp,ar_actor_deleted                                        | ar_actor_deleted    | 8       | const                  |      3268 | Using where; Using index           |
|    5 | SUBQUERY    | user                | const | PRIMARY                                                                    | PRIMARY             | 4       | const                  |         1 | Using index                        |
+------+-------------+---------------------+-------+----------------------------------------------------------------------------+---------------------+---------+------------------------+-----------+------------------------------------+

The most expensive subquery is the ALL on revision, which is a result from the view actor.

A simple fix is with https://quarry.wmflabs.org/query/41110:

+------+-------------+---------+-------+----------------------------------------------------------------------------+---------------------+---------+-------------+-------+------------------------------------+
| id   | select_type | table   | type  | possible_keys                                                              | key                 | key_len | ref         | rows  | Extra                              |
+------+-------------+---------+-------+----------------------------------------------------------------------------+---------------------+---------+-------------+-------+------------------------------------+
|    1 | PRIMARY     | actor   | const | PRIMARY,actor_name                                                         | actor_name          | 257     | const       |     1 | Using index                        |
|    1 | PRIMARY     | logging | ref   | type_time,actor_time,log_actor_type_time,log_actor_deleted,log_type_action | log_actor_type_time | 42      | const,const | 12316 | Using index condition; Using where |
|    1 | PRIMARY     | logging | ref   | type_time,log_page_id_time,log_type_action                                 | log_page_id_time    | 5       | func        |     2 | Using index condition; Using where |
|    5 | SUBQUERY    | logging | range | type_time,actor_time,log_actor_type_time,log_actor_deleted,log_type_action | log_actor_type_time | 42      | NULL        | 15784 | Using index condition; Using where |
+------+-------------+---------+-------+----------------------------------------------------------------------------+---------------------+---------+-------------+-------+------------------------------------+

Zhuyifei1999 (talk) 04:17, 4 January 2020 (UTC)

Looks like it is still too slow :( Zhuyifei1999 (talk) 09:11, 4 January 2020 (UTC)

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

The log_page is always NULL for deleted pages so you have to join on namespace & title. Zhuyifei1999 (talk) 09:25, 4 January 2020 (UTC)

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

3.33 seconds. Zhuyifei1999 (talk) 09:26, 4 January 2020 (UTC)

How to query the titles of articles of specific 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.

---

Can anyone suggest please?? Madhurimukund (talk) 05:33, 9 January 2020 (UTC)

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

Generating HTML from 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.

---

How do I get Quarry to generate an HTML table of results? I tried the obvious, "Download Data" and selected "HTML" as the format, but I ended up with all the HTML markup escaped:

<td><a href="http://en.wikipedia.org/wiki/Wikipedia:Articles_for_deletion/Du_Kang&#34;&gt;Articles_for_deletion/Du_Kang&lt;/a&gt;</td>

I'm running https://quarry.wmflabs.org/query/41231 RoySmith (talk) 16:24, 9 January 2020 (UTC)

I don't think we can allow unescaped HTML for XSS reasons. However, the tabular formats CSV and TSV are mostly unescaped so you might want to try that. Zhuyifei1999 (talk) 17:48, 9 January 2020 (UTC)

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

Get revision by revision comment id

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 to get revision details by revision comment id in fast way?

I tried the following query, but it's very slow:

USE enwiki_p;

SELECT * from revision WHERE rev_comment_id = 60565 LIMIT 1;

Matanel11111 (talk) 17:03, 11 January 2020 (UTC)

There is no index starting with revcomment_comment_id, so no I don't think that is possible. Zhuyifei1999 (talk) 17:22, 11 January 2020 (UTC)

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

Results not changing for several 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've noticed that my query at https://quarry.wmflabs.org/query/40539 is only returning a few new results on top of the usual set, while it normally has around 300 new results per day. Is there currently a lag in the database of a few days? Mike Peel (talk) 20:44, 22 January 2020 (UTC)

Current replication lag for the Wiki Replica servers can be found at toolforge:replag/. As I write this it appears that the "s4" slice which hosts the commonswiki data is lagged by ~60 hours. That much lag usually means some maintenance job is running on the lead server for that slice. I this case I would guess that phab:T232446 is the reason. BDavis (WMF) (talk) 22:44, 22 January 2020 (UTC)

Thanks. I've submitted it at phab at https://phabricator.wikimedia.org/T243488 - hope that helps keep track of it / resolve it. Mike Peel (talk) 08:05, 23 January 2020 (UTC)

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

My old query is not working now

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.

---

[ 1 ] and [ 2 ]. Can anyone solve this error? By erdo can (talk) 20:59, 23 January 2020 (UTC)

See Actor migration for changes to how *_user fields in tables have changed in the past months. BDavis (WMF) (talk) 21:01, 23 January 2020 (UTC)

3 So what is this page's problem? It should be show patroller with the most reviews . By erdo can (talk) 21:24, 23 January 2020 (UTC)

user_id doesn't correspond to *_actor. You need to join with actor.actor_id. Matěj Suchánek (talk) 16:31, 1 February 2020 (UTC)

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

Extract data from soccer player wikipedia infoboxes

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 wanna know how to extract data values like (name, team, team number, apperances, goals) from soccer player wikipedia infoboxes without using wikidata or dbpedia 41.200.52.163 (talk) 15:33, 7 February 2020 (UTC)

Hi, it's not possible to get these values from Wikipedia on Quarry (except using categories, that would result to crappy queries). Wikidata was designed to do that, you can query in query.wikidata.org or on Quarry. Last possibility to parse infobloxs would be to use dumps with offline processing. Framawiki (talk) 17:51, 8 February 2020 (UTC)

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

How to get the page titles based on page views in descending?

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 anyone suggest??? Madhurimukund (talk) 04:53, 10 February 2020 (UTC)

Page views are supplied by https://tools.wmflabs.org/pageviews/ Matěj Suchánek (talk) 09:45, 10 February 2020 (UTC)

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

Listing unused images within a 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.

---

For Wikimedia Commons, I'm trying to compile a list of unused images within a specified category. "il_from IS NULL" should allow to identify locally unused images, however, this does not take global use into account. Is there a way to check for global use? Thanks! GFJ (talk) 11:12, 19 February 2020 (UTC)

On Commons there is a table 'globalimagelinks', see

USE commonswiki_p;

DESCRIBE globalimagelinks; Achim (talk) 12:34, 19 February 2020 (UTC)

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

Converting url:s to links

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 it be possible to convert https://en.wikipedia.org/example_page style url:s to hyperlinks in the Quarry's result list? Currently they are plaintext. Zache (talk) 18:22, 19 February 2020 (UTC)

That would be phab:T74874 BDavis (WMF) (talk) 02:42, 20 February 2020 (UTC)

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

Lost connection to MySQL server during query

https://quarry.wmflabs.org/query/8938 now throws a "Query status: failed...Lost connection to MySQL server during query" error when it was working yesterday - have there been more changes in the last day to revision or something? Le Deluge (talk) 11:52, 3 March 2020 (UTC)

I get the same error: https://quarry.wmflabs.org/query/12945 The Polish (talk) 18:30, 3 March 2020 (UTC)

This error message indicates that there was a loss of connection between the backend worker process for your Quarry query and the Wiki Replica database server that the query was running on. This can happen for a large number of reasons, but none of them are actionable by the Quarry end user.

My best advice is to try submitting your failed query for execution again. BDavis (WMF) (talk) 21:40, 3 March 2020 (UTC)

Still happening. I did wonder if it was the new way it was timing out, as that query is a slow one that can sometimes timeout when the US is awake, but it's still doing it at 4am EST. Other queries are working just fine here. Le Deluge (talk) 09:14, 4 March 2020 (UTC)

I'm having the same issue consistently with #40539. Mike Peel (talk) 09:26, 4 March 2020 (UTC)

It's on phabricator at https://phabricator.wikimedia.org/T246970 Mike Peel (talk) 20:46, 5 March 2020 (UTC)

Still getting it with #8938, even in Europe am. Also got it occasionally with #29106 which normally takes 2-3 minutes. I'll emphasise - #8938 was working just fine (albeit tending to time out when US was awake) before 3 days ago, and has not been changed since then.Le Deluge (talk) 16:25, 6 March 2020 (UTC)

In the investigation of phab:T246970 we have determined that Quarry is running its queries against the "web" Wiki Replica hosts which have a 300 second (5 minutes) maximum query execution time rather than the expected "analytics" Wiki Replica hosts which have a 14400 second (4 hour) maximum query execution time. This is the likely cause of a number of 'Lost connection' errors. We will continue to investigate and try to get things back into a more stable state. BDavis (WMF) (talk) 16:54, 6 March 2020 (UTC)

Thanks for finding the issue, is there an ETA when things will be running normally again, please? Mike Peel (talk) 18:09, 6 March 2020 (UTC)

So for the last 24h even #29106 which normally takes 2-3 minutes is now consistently taking 30+minutes and so is timing out. Quarry seems to be working for a lot of other people though.Le Deluge (talk) 10:27, 9 March 2020 (UTC)

I suspect it's just a server load issue now... Mike Peel (talk) 15:08, 9 March 2020 (UTC)

It feels like more than that. #29106 started degrading last weekend - it had been running consistently at 2-3 minutes for months, then suddenly maybe 1 in 5 would start timing out at random (then would run fine minutes later), and it hasn't run at all for 6 days. Even starting it at 08.16 GMT on a Saturday morning, with no other Quarry queries running, didn't help. Doesn't seem to be an issue with the underlying data being wildly different either, looking at Special:WantedCategories which it replicates. Le Deluge (talk) 10:39, 14 March 2020 (UTC)

Clean up my profile

Is it possible to clean up my profile draft queries? Regards, ZI Jony ^(Talk) 09:03, 9 March 2020 (UTC)

Wikidata query returning unfiltered results

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 use this quarry to find pages that don't have Arabic label at Wikidata, but it's started showing them all, and not filtering items that already have labels. Any idea why, and how to fix it? Thanks in advance. @Edgars2007 :) FShbib (talk) 17:45, 1 April 2020 (UTC)

wb_terms is not updated anymore. See d:Wikidata:PC#Important for tool maintainers: last steps of wb terms table migration. Matěj Suchánek (talk) 18:13, 1 April 2020 (UTC)

Thank you @Matěj Suchánek, is there a replacement for this quarry that can do the same task? FShbib (talk) 18:37, 1 April 2020 (UTC)

@FShbib phab:T221767 may have some ideas for you on how to adjust your queries. That would be a reasonable place to ask the Wikidata folks for help if you don't find what you need too. BDavis (WMF) (talk) 19:07, 1 April 2020 (UTC)

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

Query help needed to get page name, industry and founded.

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 pls help me with a query that gets name(page name), industry and founded from wiki pages for companies. Can be limited to US, if it improves performance.

Ex: https://en.wikipedia.org/wiki/Microsoft

Company Name/Page Name: Microsoft

Industry: Software developement (Can list all or one industry)

Founded: April 4 1975 (Year is fine)

Need this list for al us based companies. 2601:600:8780:66D0:A517:F6A7:D074:4C5D (talk) 00:58, 2 April 2020 (UTC)

Data in wiki pages cannot be queried (except for categories). For querying Wikidata, d:Wikidata:Request a query is the place to ask for queries. Matěj Suchánek (talk) 08:11, 2 April 2020 (UTC)

Thanks a lot for quick response and appropriate link. Padi2020 (talk) 16:53, 2 April 2020 (UTC)

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

Does the pagelinks table not get purged of links that no longer exist?

Maybe i'm missing something but take this example:

Page "Westplex" redirects to "St._Charles_County,_Missouri": https://quarry.wmflabs.org/query/43707

It apparently does so since 2018: https://en.wikipedia.org/w/index.php?title=Westplex&type=revision&diff=828554258&oldid=827301936

Yet in pagelinks: https://quarry.wmflabs.org/query/43706 MSUGRA (talk) 01:11, 8 April 2020 (UTC)

The redirect page contains some navboxes, the links seem to come from those... Matěj Suchánek (talk) 07:15, 8 April 2020 (UTC)

Ah right, I missed those. Thanks for clearing that up! MSUGRA (talk) 13:06, 8 April 2020 (UTC)

On a related note: the redirect table is said to only track redirects that have been changed after 2007. Without deeper knowledge of the subject, I wouldn't necessarily assume that old redirects on Wikipedia have been touched since then. That's why I opted to reconstruct them from the pagelinks+page tables instead. Someone on IRC said they think every page will have experienced an automated update of some sort since then, prompting them to be tracked.

Do you know more about this? Can I rely on the redirect table tracking all (relevant) redirects the way the pagelinks table does? MSUGRA (talk) 13:18, 8 April 2020 (UTC)

This is news for me. But we can quickly check if all redirects have an entry:

SELECT page.* FROM page
LEFT JOIN redirect ON rd_from = page_id
WHERE page_is_redirect = 1 AND rd_title IS NULL

Matěj Suchánek (talk) 07:21, 9 April 2020 (UTC)

databases schema does not match to database fields

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 an updated database schema because in this schemahttps://upload.wikimedia.org/wikipedia/commons/thumb/4/42/MediaWiki_1.20_%2844edaa2%29_database_schema.svg/2500px-MediaWiki_1.20_%2844edaa2%29_database_schema.svg.png

the revision table in enwikisource changed. At least one field no longer exists.

— Ineuw talk 20:29, 15 April 2020 (UTC)

There is some information on the Actor migration changes and how they affected the views exposed on the Wiki Replica servers at wikitech:News/Actor storage changes on the Wiki Replicas. There are likely quite a number of other changes which have happened since the MediaWiki 1.20 schema that is documented in that diagram. A similarly major one would be Manual:Comment table. BDavis (WMF) (talk) 17:12, 16 April 2020 (UTC)

Thanks for the info. It's much appreciated. My interest is limited to the English Wikisource. I am flying blind trying to extract data. Perhaps there is current list of the table structures used by Wikisource? — Ineuw talk 22:48, 16 April 2020 (UTC)

The most up to date information is generally the schema documentation in the MediaWiki source tree: maintenance/tables.sql. Manual:Database layout and Manual:Database layout/diagram are generally updated soon after major changes are introduced. BDavis (WMF) (talk) 03:12, 17 April 2020 (UTC)

Thanks again. The Manual:Database layout/diagram is excellent. That is what I was looking for — Ineuw talk 05:08, 17 April 2020 (UTC)

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

Request bad queries deletion

These two queries are stuck in running mode for months. can someone delete them please?

• 4215 old psm images and their categories
• 22557 old psm images and their categories — Ineuw talk 22:23, 15 April 2020 (UTC)

Hi, perhaps they were already fixed by another admin, but they are no longer shown as Running.

Note that seeing Running for a long time likely just means status was not updated, due to an error. Query owner can just click on Run button again. Framawiki (talk) 21:15, 18 April 2020 (UTC)

Thanks. That's what I thought, but it remained running and when I copied the sql statement into another query, it repeated the same. — Ineuw talk 03:57, 19 April 2020 (UTC)

Query results not updated

I used a few queries to do some wiki gnoming so I usually just submit the same query again to get an updated list of results. I noticed since a few days back the results are not updated at all. It still showed the same results even though it should be lesser now.

Is there a caching issue or something else? Justanothersgwikieditor (talk) 07:22, 6 May 2020 (UTC)

Might be caused by the huge replag of 30+ hours: https://replag.toolforge.org/ Achim (talk) 07:43, 6 May 2020 (UTC)

Where can I find a documentation/tutorial for wikidata database

Hi.

Where can I learn about these tables

wb_changes;

wb_changes_dispatch;

wb_changes_subscription;

wb_id_counters;

wb_items_per_site;

wb_property_info;

wb_terms;

wbc_entity_usage;

wbqc_constraints;

wbs_propertypairs;

wbt_item_terms;

wbt_property_terms;

wbt_term_in_lang;

wbt_text;

wbt_text_in_lang;

wbt_type;

For example, we have this category for MediaWiki Database tables. Thanks ^_^ Ashot (talk) 22:44, 11 June 2020 (UTC)

See mw:Category:Wikibase schema but some of the tables are missing and even if there is a page for the database table, the table is often poorly documented. Stefan2 (talk) 23:42, 11 June 2020 (UTC)

Examples for a subset for them is in phab:T221767. Matěj Suchánek (talk) 07:53, 12 June 2020 (UTC)

New People in Wiki

Hello,

I wonder if my research is possible.

I'm just trying to get a list of the names of all the 'new' persons that have been entered in wiki in the last two months (so I should find there Stella Morris, George Floyd, Derek Chauvin etc... and a lot of people who were unknown 2 months ago).

I suppose I should adapt this query but I don't know how.

Thanks for your help if you can

USE wikidatawiki_p;

SELECT rev_id AS first_edit FROM revision WHERE rev_timestamp BETWEEN "20200501" AND "20200624" ORDER BY RAND() LIMIT 5000; 91.160.58.193 (talk) 09:18, 24 June 2020 (UTC)

User account creation log is stored on logging table. Try this query!

quarry.wmflabs.org/query/53090 Quarry User (talk) 05:19, 9 March 2021 (UTC)

Inordinately long Wikidata short descriptions

Hello all,

I would like to search for Wikidata items with inordinately long descriptions (say, more than 250 characters). I have already tried this query with no success. (Unfortunately, my knowledge of SQL is minimal.) Genericusername57 (talk) 17:44, 26 June 2020 (UTC)

USE wikidatawiki_p;
SELECT wbit_item_id, wbxl_language, wbx_text
FROM wbt_item_terms
INNER JOIN wbt_term_in_lang ON wbit_term_in_lang_id = wbtl_id
INNER JOIN wbt_text_in_lang ON wbtl_text_in_lang_id = wbxl_id
INNER JOIN wbt_text ON wbxl_text_id = wbx_id
WHERE wbtl_type_id = 2
-- AND wbxl_language = ''
AND LENGTH(wbx_text) > 250

You can also filter by language (as hinted). You'll probably want to add some limit. Matěj Suchánek (talk) 13:31, 6 July 2020 (UTC)

Cannot start 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.

---

Exactly same behavior as 8 months ago, see Talk:Quarry/2019#h-Has_something_been_changed_during_the_past_4_days?-2019-11-14T19:27:00.000Z when the web node ran out of disk space.

Ah, now it runs again Achim (talk) 13:49, 8 July 2020 (UTC)

For the record, that was phab:T257453 Framawiki (talk) 20:08, 10 July 2020 (UTC)

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

Looking for info about table links

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 info available on the database table links to other tables? I saw the new schema (very nice) here on MW, and studied the field names but couldn't figure out the links as I have been able. Was there a redesign of the table links in the past couple of years? — Ineuw talk 01:17, 19 July 2020 (UTC)

What do you mean with "links"? On Quarry, you can use SHOW TABLES; to display all available tables and DESCRIBE some_table; to see the list of columns. Matěj Suchánek (talk) 09:14, 19 July 2020 (UTC)

You are right. I was used to the old schema where the related fields were graphically linked.

P.S: I found what I was looking for but it was for version 1.20 — Ineuw talk 04:11, 20 July 2020 (UTC)

While I'm more used to the "command" interface, there is also "Database tables" tab on the top the pages on Quarry which provides a link to the page with "Explore database schema" and also to https://sql-optimizer.toolforge.org/ which includes "Schema browser" feature. Matěj Suchánek (talk) 13:28, 21 July 2020 (UTC)

Is there anyone here can tell me which table to use to link page and user tables? Thanks in advance.

If it's not known, perhaps someone can point me to a list of users' published queries, to study them? — Ineuw talk 05:58, 17 August 2020 (UTC)

The trick is to normalize spaces/underscores.

Either:

SELECT * FROM page JOIN user ON page_namespace = 2 AND user_name = REPLACE(page_title, '_', ' ')

or

SELECT * FROM user JOIN page ON page_namespace = 2 AND page_title = REPLACE(user_name, ' ', '_')

Matěj Suchánek (talk) 10:46, 17 August 2020 (UTC)

I am just late, but did not forget. Thanks for the great help. — Ineuw talk 07:58, 24 August 2020 (UTC)

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

diff

Hello, how the added text of a revision could be requested? Habitator terrae (talk) 16:11, 26 July 2020 (UTC)

Hi, you can't. These "replicas" databases only contains metadata (everything public except text or diffs). Use m:Dumps or Api (ie. Pywikibot) in case you really want some text. First is downloading file to process offline on your computer, second is to query each single page you need from the server. Both requires you to write a script in some programming language. Framawiki (talk) 17:19, 26 July 2020 (UTC)

Need help for a querry.

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, Xaoflux gently made me a quarry a year ago (quarry:query/33757), but now I would like to find all french translation pages on Meta with a "·" in its content. And where could I find an index of the different databases replicas of commons, outreach, species, mv, fr.wikt... to fork the quary for different wikis ?

Thank you for the help CreativeC (talk) 14:37, 4 August 2020 (UTC)

For a list see query/278 (shows now 916 entries) and https://noc.wikimedia.org/conf/dblists/all.dblist . If you want to search for "·" not only in titles but in real content of the page you will have to use the API. Achim (talk) 14:57, 4 August 2020 (UTC)

Thank you but how would I do such thing ? CreativeC (talk) 15:06, 4 August 2020 (UTC)

Can you give an example of a page you want to be found? Did a simple search for intitle:/fr insource:/·/ fit your needs? Achim (talk) 15:17, 4 August 2020 (UTC)

I didn't know the "insource" parameter in the research ! It perfectly resolve my problem !

Thank you ! CreativeC (talk) 18:47, 4 August 2020 (UTC)

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

Which tables are publicly accessible?

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 really appreciate the great database schema, but it seems to me that some of the tables are not public. For example, it looks like watchlist information is not public, I assume to prevent vandalism. Is there a version of the schema that includes info about what tables are public-facing, or am I confused about the tables in some way? Thank you! Otherwise, looks like I can use any table returned by SHOW tables; Suriname0 (talk) 22:08, 26 August 2020 (UTC)

You should indeed be able to use anything that show tables; lists.

The best documentation of how the Wiki Replicas differ from the raw operational schema of the production wikis is found at https://wikitech.wikimedia.org/wiki/Help:Toolforge/Database#Replica_database_schema_(tables_and_indexes).

The watchlist table specifically is hidden from the Wiki Replica databases because details of who is watching which page are not public on the wikis. A logged in user can only see their own watchlist and anonymous users cannot see any watchlists at all.

There is an open ticket about restoring a derived table that once existed on the Wiki Replicas that would expose aggregate counts how many people are watching a given page. This information is currently available via the Action API using api.php?action=query&prop=info&inprop=watchers&titles=.... That ticket is currently stalled on the DBA and Cloud Services teams finding a reliable way to manage derived tables across the Wiki Replica fleet. BDavis (WMF) (talk) 22:49, 26 August 2020 (UTC)

Thank you, Bryan! Will be interesting to see if that derived table ever goes live. In addition to the technical challenges, I wonder about the risks of exposing low-count pages in namespace 0 to potential vandals. Suriname0 (talk) 04:50, 27 August 2020 (UTC)

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

Something wrong again with Excel XLSX download format

A while ago there was an issue with Excel XLSX download format, which was fixed by cleaning some temp files from somewhere. It seems to be happening again and an attempt to open generated spreadsheets gives invalid file format or extension error. Jarekt (talk) 02:30, 27 August 2020 (UTC)

Hello Jarekt, can you confirm the problem was solved in the meantime ? Thanks! Framawiki (talk) 11:43, 20 September 2020 (UTC)

yes it is solved Jarekt (talk) 00:22, 21 September 2020 (UTC)

@Framawiki: , same problem is happening again Jarekt (talk) 05:12, 16 January 2021 (UTC)

Facing the same problem since yesterday Chaduvari (talk) 09:11, 16 January 2021 (UTC)

Hi, thanks for the notification. We're facing issue (phab:T238375) where we've to manually fix when problem happens, until we found a workaround. So don't hesitate to ping again if you're facing the issue. Thanks! Framawiki (talk) 21:08, 27 January 2021 (UTC)

Case-insensitive search of usernames

1. I did select user_name from user where user_name like '%chandandolui%'; and got two results: Iamchandandolui and Mr.chandandolui.

2. I then did select user_name from user where UPPER(user_name) like '%CHANDANDOLUI%'; which ran for about a minute and returned no rows. Shouldn't I get at least the same two rows? I know of at least two more (Chandandolui and Chandandolui1) that should match the case-insens search.

3. If I run select user_name from user where UPPER(user_name) like '%chandandolui%';, which shouldn't match anything I think, it returns the same two rows as the first case.

I'm a little rusty but what am I missing? —[AlanM1(talk)]— 08:35, 5 September 2020 (UTC)

user_name is varbinary. According to [1], something like UPPER(CONVERT(user_name USING utf8mb4)) could do the trick. Matěj Suchánek (talk) 09:31, 5 September 2020 (UTC)

Thanks! The varbinary type didn't occur to me, but I can guess at the reason. en:WP:VPT came up with using RLIKE '(?i)chandandolui', which seem like an easier solution for me to remember (regex comparisons were always on my SQL wishlist when I was working with it :) ). —[AlanM1(talk)]— 11:26, 5 September 2020 (UTC)

Articles written by a single editor

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 enwiki articles whose revisions have all been written by a unique editor (excluding editors with case-insensitive "bot" in their name).

How would you write such a query?

I get Unknown column 'rev_user' in 'where clause' when I try USE enwiki_p; SELECT rev_user FROM revision LIMIT 1 Syced (talk) 13:22, 23 September 2020 (UTC)

rev_user has been replaced by rev_actor, see Manual:Revision table. Achim (talk) 13:53, 23 September 2020 (UTC)

Wonderful, thanks a lot :-) Syced (talk) 12:20, 25 September 2020 (UTC)

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

Articles written by a single editor: How to improve query speed?

In my quest to find all articles that have only been edited by one human editor, I wrote this query:

USE enwiki_p;
SELECT page_title FROM (
  SELECT p.page_title, r.rev_actor, a.actor_name
  FROM (
    SELECT page_title, page_len, page_id
    FROM page
    WHERE page_namespace = 0 # Mainspace
      AND NOT page_is_redirect
  ) AS p # All mainspace pages
  LEFT JOIN revision_userindex r ON r.rev_page = p.page_id
  LEFT JOIN actor a ON r.rev_actor = a.actor_id
  WHERE NOT IS_IPV4(a.actor_name) # Ignore IP editors and bots
    AND NOT IS_IPV6(a.actor_name)
    AND LOWER(a.actor_name) NOT LIKE '%bot%'
    AND LOWER(a.actor_name) NOT LIKE '%script%'
) AS pra
GROUP BY (page_title)
HAVING COUNT(rev_actor) < 2 # Only 1 editor

Problem: It times out.
Question: How to make it run faster?

For instance, to improve speed I have thought about skipping pages that have more than 50 revisions, but I am not sure how to implement it. Syced (talk) 12:49, 25 September 2020 (UTC)

Hey,

I think you made it too complicated.

I wrote a simpler version (https://quarry.wmflabs.org/query/48484) which also does not work without the "LIMIT" D. Saeidpourbabak (talk) 18:17, 25 September 2020 (UTC)

Thanks, your is faster indeed :-) Syced (talk) 14:33, 27 September 2020 (UTC)

execute command denied for DATE_TRUNC?

why is this error showing up? execute command denied to user 's52788'@'%' for routine 'enwiki_p.DATE_TRUNC'

I am trying to get a query of monthly active contributors. Ospachi (talk) 21:39, 3 October 2020 (UTC)

DATE_TRUNC is a standard function in Postgres, but it is not available in MySQL. You might want to try a web search for "mysql DATE_TRUNC" to see some possible alternatives. BDavis (WMF) (talk) 23:05, 3 October 2020 (UTC)

Access denied

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 query at https://quarry.wmflabs.org/query/48686 keeps failing with Access denied for user 's52788'@'%' to database 'commonswiki_p'. I'm new to SQL, so I might have an error in my code, but two other queries to the same database did work for me.

Edit:

code for reference

USE commonswiki_p;
CREATE FUNCTION panoramiocatcount(a INT)
RETURNS INT
BEGIN
     RETURN(SELECT COUNT(cl_to)
     FROM categorylinks
     WHERE cl_from = a
     AND cl_to like 'Photos_from_Panoramio_ID_%');
END;

SELECT page_title, panoramiocatcount(page_id)
FROM page
WHERE panoramiocategorynumber(page_id) > 1
AND page_namespace = 6
ORDER BY 2 DESC;

𝟙𝟤𝟯𝟺𝐪𝑤𝒆𝓇𝟷𝟮𝟥𝟜𝓺𝔴𝕖𝖗𝟰 (𝗍𝗮𝘭𝙠) 12:01, 4 October 2020 (UTC)

I am pretty sure SQL functions are not allowed. Matěj Suchánek (talk) 12:46, 4 October 2020 (UTC)

Hm, that may be the issue. Do you have any suggestions how to make the query without declaring a function? 𝟙𝟤𝟯𝟺𝐪𝑤𝒆𝓇𝟷𝟮𝟥𝟜𝓺𝔴𝕖𝖗𝟰 (𝗍𝗮𝘭𝙠) 12:49, 4 October 2020 (UTC)

Something like:

USE commonswiki_p;
SELECT page_title, COUNT(cl_to) AS panoramiocatcount
FROM page
JOIN categorylinks ON cl_from = page_id
WHERE page_namespace = 6
AND cl_to LIKE 'Photos\_from\_Panoramio\_ID\_%'
GROUP BY page_id
HAVING panoramiocatcount > 1;

Matěj Suchánek (talk) 12:56, 4 October 2020 (UTC)

Thanks (it works)! 𝟙𝟤𝟯𝟺𝐪𝑤𝒆𝓇𝟷𝟮𝟥𝟜𝓺𝔴𝕖𝖗𝟰 (𝗍𝗮𝘭𝙠) 12:59, 4 October 2020 (UTC)

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

How to get ORES article quality predictions for an article?

I'm having trouble understand how ORES predictions are represented in the ores_classification table.

For example, using the ORES API to get "articlequality" (57) classifications for enwiki revision 982883941 (https://ores.wikimedia.org/scores/enwiki/?models=articlequality&revids=982883941) returns six scores and a prediction.

But running what should be an identical query (as far as I understand) using quarry returns only one row, with none of the values seeming to match up: https://quarry.wmflabs.org/query/49243.

Can anyone help? What I would ultimately like to do is get the predicted article quality for a set of revision IDs. Joe Roe (talk) 12:49, 22 October 2020 (UTC)

Finding duplicated files which are available and locally and on Commons

@Edgars2007 has provided answer and this question is resolved. :)

I would like to find files on Serbian Wikipedia which are available and locally and on Commons (duplicates). Which query will work this job? Kizule (talk) 14:07, 8 November 2020 (UTC)

example for lvwiki Edgars2007 (talk) 14:30, 8 November 2020 (UTC)

Thank you @Edgars2007, this works well. :) Kizule (talk) 18:34, 8 November 2020 (UTC)

Please note: this query will no longer work beginning in January/February 2021: https://wikitech.wikimedia.org/wiki/News/Wiki_Replicas_2020_Redesign AntiCompositeNumber (talk) 03:50, 11 November 2020 (UTC)

ahh :( forgot about those. thought that those redesign changes won't affect me :D Edgars2007 (talk) 09:36, 11 November 2020 (UTC)

INDIA360

I would like to create a list of Articles/pages related to India with page title and link(https://en.wikipedia.org)

Now there are two lists on to this

1- list where title has india (where page_title like '%India%' )

2- list where a string 'India' is present or mentioned.

Can anyone help out here with the quarry .

2 columns & 2 lists 183.83.138.3 (talk) 17:24, 19 November 2020 (UTC)

URL mysterious subdirectory

Newbie here --

I almost have all the data I need to reconstruct URLs like this:

https://upload.wikimedia.org/wikipedia/commons/1/1b/Sally_Field_%2811205%29_%28cropped%29.jpg

But, I cannot find where .../1/1b/... comes from. I assume it's in the database somewhere. Any ideas?

Thank you.

- Matt Brainbout (talk) 00:59, 2 December 2020 (UTC)

Those path components are not directly stored in the database. They are instead derived data resulting from the use of $wgHashedUploadDirectory = true; in the settings for Wikimedia wikis. These values are taken from the md5 hash of the filename. See Manual:$wgHashedUploadDirectory for more details. BDavis (WMF) (talk) 01:46, 2 December 2020 (UTC)

Thank you BDavis. I'll have a look at that man page. Brainbout (talk) 19:39, 2 December 2020 (UTC)

need help / quarry does not start at all

Hi

https://quarry.wmflabs.org/query/50304 does not start at all (since a few days). Before that ( made some change) it ran and produced sound results without any timeout. See similar https://quarry.wmflabs.org/query/50070 , whích ran and where the result is still in the cache.

There are two selects, running only the first will start and finish. Running only the second will not start. without any hint or error message, it just fails silently.

Can somebody help to find / fix the problem? best

Try https://quarry.wmflabs.org/query/50253 (quarries that are not executed are not safed, so https://quarry.wmflabs.org/query/50304 is empty). Was empty, I now played around a little bit and it seems that there is a size limitation for the sql script, / for each select statement.

got the following messages in the console:

/api/query/run:1 Failed to load resource: the server responded with a status of 500 ()
50304:1 Refused to load the image 'data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==' because it violates the following Content Security Policy directive: "img-src 'self'".
/api/query/run:1 Failed to load resource: the server responded with a status of 500 ()
jquery.js:8623 POST https://quarry.wmflabs.org/api/query/run 500
send @ jquery.js:8623
ajax @ jquery.js:8152
n.<computed> @ jquery.js:8298
(anonymous) @ view.js:80
dispatch @ jquery.js:4409
r.handle @ jquery.js:4095

(its chrome)

message for status 500 is:

500 Internal Error

Sadly Quarry is currently experiencing issues and is unavailable. Please try again later.

But I have to dig into the browser debugger to see this. No message on the UI. ISO 3166 Bot (talk) 21:06, 4 December 2020 (UTC)

Hi @Herzi Pinki: , could you re-try now? If it's working, it was probably a temporary issue. Framawiki (talk) 21:06, 27 January 2021 (UTC)

Thanks, it is working now.

For a temporary issue it took quite long to fix it. It would be an improvement, if a status of 500 is propagated to the UI anyway. Herzi Pinki (talk) 01:38, 28 January 2021 (UTC)

cookbook for "classes" of queries

Not sure if i am missing some docs somewhere but i am looking to perform a set of queries where by i can select the title based upon the content available in the infobox (vcard? looking at the html).

It is unclear to me how to do this base upon the schema. No doubt others would like to do this as well.

I am very familiar with MYSQL but most of the queries I have seen appear to be very complex for data that seems to be straightforward.

For example I want to find all companies that are listed in the S&P_500 .not the page listing them but a list based upon a query that lists the url in the infobox.

Is this hard? easy? Can someone point me to a sql query that can do this?

thanks 2601:1C1:867F:EA40:D896:7EC1:1E1F:AA9A (talk) 00:12, 18 December 2020 (UTC)

Infobox cannot be queried using SQL (in fact, using nothing). You can query structured data on Wikidata using SPARQL endpoint. Matěj Suchánek (talk) 09:25, 18 December 2020 (UTC)