Normalize templatelinks table
Closed, ResolvedPublic
Actions

Description

After T299416: Normalize link tables: Create linktarget table is done. To learn how to update your queries, see T299417#7814637.

Details

Subject	Repo	Branch	Lines +/-
Remove TemplateLinksSchemaMigrationStage config	mediawiki/core	master	+36 -88
Introduce pruneUnusedLinkTargetRows maint script	mediawiki/core	master	+104 -0
Introduce pruneUnusedLinkTargetRows maint script	mediawiki/core	REL1_39	+104 -0
schema: Drop tl_title and tl_namespace fields from templatelinks	mediawiki/core	REL1_39	+172 -69
schema: Drop tl_title and tl_namespace fields from templatelinks	mediawiki/core	master	+172 -69

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
Open	None	T300222 Implement normalizing MediaWiki link tables
Resolved	Ladsgroup	T299417 Normalize templatelinks table
Resolved	Ladsgroup	T299418 Add tl_target_id as foreign key to lt_id to templatelinks
Resolved	Ladsgroup	T299419 Write code for enabling compat writes for templatelinks and title
Resolved	Ladsgroup	T299420 Turn on write both in beta cluster for templatelinks normalization
Resolved	Ladsgroup	T299421 Turn on write both in production for templatelinks normalization
Resolved	Ladsgroup	T299423 Write maintenance script for backfilling tl_target_id
Resolved	Ladsgroup	T299424 Run maintenance script backfilling tl_target_id
Resolved	Marostegui	T300775 Add tl_target_id column to templatelinks
Resolved	Marostegui	T301313 db2076, db2074 and db1123 crashed while altering templatelinks table
Resolved	Marostegui	T301848 Check for compressed templatelinks tables
Resolved	Marostegui	T301850 Switchover s3 master (db1157 -> db1123)
Resolved	Marostegui	T302222 Check and fix compressed mismatched tables
Resolved	Ladsgroup	T304780 Write code for enabling compat read for templatelinks and linktarget
Resolved	Ladsgroup	T305064 Make linktarget table visible on cloud wiki replicas
Resolved	Ladsgroup	T306673 Turn on read new for templatelinks on beta and production
Resolved	Ladsgroup	T306674 Add support for write new for templatelinks migration
Resolved	Ladsgroup	T308207 ApiQueryInfo::getProtectionInfo is slow on normalized templatelinks
Resolved	Ladsgroup	T312863 Schema change to change primary key of templatelinks
Resolved	Marostegui	T317614 Switchover codfw s1 master (db2103 -> db2112)
Resolved	Ladsgroup	T312865 Turn off writing to the old columns of templatelinks in beta and production
Resolved	Ladsgroup	T314041 Drop old templatelinks columns and indexes
Resolved	Ladsgroup	T314711 Add support for links migration to namespaceDupes.php

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

RhinosF1 subscribed.Mar 26 2022, 4:31 PM

Steps that don't have a subticket yet:

Turn on read new in beta cluster
Turn on read new in production
Schema change to make tl_namespace and tl_title nullable
Deploying the schema change
Write code stopping to write on the old columns via config
Turn off writing to the old columns in beta and then production
Schema change to drop old columns
Deploying the schema change

AntiCompositeNumber subscribed.Mar 29 2022, 1:38 PM

Could you ellaborate, please, how will the new scheme interpret the red templates transclusion, so that quarry could recognize them?

MusikAnimal subscribed.Mar 29 2022, 2:02 PM

I'm not 100% sure I understood your usecase but if a query to templatelinks used to look like this:

SELECT tl_from, tl_namespace, tl_title from templatelinks where tl_from = 5974403;

It will change to:

SELECT tl_from, lt_namespace, lt_title from templatelinks join linktarget on lt_target_id = lt_id where tl_from = 5974403;

It seems the new table has not been replicated to the cloud replicas (I'll check and fix it soon).

If this doesn't answer your question, can you elaborate more? possibly giving the query you run usually.

Superyetkin subscribed.Mar 29 2022, 5:03 PM

In T299417#7814637, @Ladsgroup wrote:
I'm not 100% sure I understood your usecase but if a query to templatelinks used to look like this:
SELECT tl_from, tl_namespace, tl_title from templatelinks where tl_from = 5974403;
It will change to:
SELECT tl_from, lt_namespace, lt_title from templatelinks join linktarget on lt_target_id = lt_id where tl_from = 5974403;
It seems the new table has not been replicated to the cloud replicas (I'll check and fix it soon).

If this doesn't answer your question, can you elaborate more? possibly giving the query you run usually.

Thanks. It doesn't. What happens when the template does not exist?
(For example, here.)

Mormegil subscribed.Mar 30 2022, 9:34 AM

Nothing will change when it comes to existence of a template.
For example, you have this query:

select page_namespace, page_title, tl_title from page inner join templatelinks where page_namespace in (0, 4, 6, 8, 10, 12, 14,
100) and page_id = tl_from and tl_namespace != 8 and not ((page_namespace = 100 and (tl_namespace <> 10 or tl_title like "הידעת_מדינות_%"))
or (page_namespace = 8 and page_title like "%.js") or (page_namespace = 4 and (page_title like "%OTRS%" or page_title like "%בקשות_לבדיקה%"
or page_title like "%דלפק_ייעוץ%" or page_title like "%הכה_את_המומחה%" or page_title like "%זכויות_יוצרים%" or page_title like
"%יומן_מחיקות%" or page_title like "%סדנה_לגרפיקה%"  or (page_title like "%המלצות_קודמות%" and not page_title in
("ערכים_מומלצים/המלצות_קודמות/אפריל_2016", "ערכים_מומלצים/המלצות_קודמות/מרץ_2016", "ערכים_מומלצים/המלצות_קודמות/מאי_2016")) or
page_title like "%אולם_דיונים%" or page_title like "%עבודות_ויקידמיות%" or page_title like "%ארכיון%" or page_title like
"%סקר_ויקיפדיה%" or page_title like "%איך_להקים_אסם%" or page_title like "%מיזמי_ויקיפדיה/נבחרי_ציבור_פוטנציאליים%" or page_title like
"%הומור%" or page_title like "%רשימת%" or page_title like "%חשבון_נפש%" or page_title like "%סופשבוע_ללא_ערכים%" or page_title like
"%ויקימניה%" or page_title like "%תחרות_צילום%" or page_title like "%מתקפת_איכות%" or page_title like "%מזנון%" or page_title like
"%מועדונים%" or page_title like "%מפגשים%" or page_title like "%תבנית%" or page_title like "%שער_לילדים%" or page_title like
"%שיתופי_פעולה%" or page_title like "%מיזמי_ויקיפדיה%" or page_title like "%בוט_החלפות%" or page_title like "הידעת?/סדרה__/%" or
page_title in ("הידעת?/2018/אוגוסט", "חדשות", "הידעת?/2018/ספטמבר", "הידעת?/ממרץ_2011", "ארגז_חול", "הידעת?", "כיכר_העיר", 
"הידעת?/המתנה") or page_title = "ויקימדיה_ישראל/תמונה_נבחרת/גלריה" and page_namespace = 4 and tl_namespace = 4 and tl_title like
"ויקימדיה_ישראל/תמונה_נבחרת/%")) or exists (select * from page where page_namespace = tl_namespace and page_title = tl_title))

In order to make the change easier, move the join condition to ON clause instead of WHERE:

select page_namespace, page_title, tl_title from page inner join templatelinks ON page_id = tl_from where page_namespace in (0, 4, 6, 8, 10, 12, 14,
100) and tl_namespace != 8 and not ((page_namespace = 100 and (tl_namespace <> 10 or tl_title like "הידעת_מדינות_%"))
or (page_namespace = 8 and page_title like "%.js") or (page_namespace = 4 and (page_title like "%OTRS%" or page_title like "%בקשות_לבדיקה%"
or page_title like "%דלפק_ייעוץ%" or page_title like "%הכה_את_המומחה%" or page_title like "%זכויות_יוצרים%" or page_title like
"%יומן_מחיקות%" or page_title like "%סדנה_לגרפיקה%"  or (page_title like "%המלצות_קודמות%" and not page_title in
("ערכים_מומלצים/המלצות_קודמות/אפריל_2016", "ערכים_מומלצים/המלצות_קודמות/מרץ_2016", "ערכים_מומלצים/המלצות_קודמות/מאי_2016")) or
page_title like "%אולם_דיונים%" or page_title like "%עבודות_ויקידמיות%" or page_title like "%ארכיון%" or page_title like
"%סקר_ויקיפדיה%" or page_title like "%איך_להקים_אסם%" or page_title like "%מיזמי_ויקיפדיה/נבחרי_ציבור_פוטנציאליים%" or page_title like
"%הומור%" or page_title like "%רשימת%" or page_title like "%חשבון_נפש%" or page_title like "%סופשבוע_ללא_ערכים%" or page_title like
"%ויקימניה%" or page_title like "%תחרות_צילום%" or page_title like "%מתקפת_איכות%" or page_title like "%מזנון%" or page_title like
"%מועדונים%" or page_title like "%מפגשים%" or page_title like "%תבנית%" or page_title like "%שער_לילדים%" or page_title like
"%שיתופי_פעולה%" or page_title like "%מיזמי_ויקיפדיה%" or page_title like "%בוט_החלפות%" or page_title like "הידעת?/סדרה__/%" or
page_title in ("הידעת?/2018/אוגוסט", "חדשות", "הידעת?/2018/ספטמבר", "הידעת?/ממרץ_2011", "ארגז_חול", "הידעת?", "כיכר_העיר", 
"הידעת?/המתנה") or page_title = "ויקימדיה_ישראל/תמונה_נבחרת/גלריה" and page_namespace = 4 and tl_namespace = 4 and tl_title like
"ויקימדיה_ישראל/תמונה_נבחרת/%")) or exists (select * from page where page_namespace = tl_namespace and page_title = tl_title))

Note that actually the last part of your query (the subquery) is wrong as it doesn't add templatelinks.

And then change things:

select page_namespace, page_title, lt_title from page inner join templatelinks ON page_id = tl_from join linktarget on tl_target_id = lt_id where page_namespace in (0, 4, 6, 8, 10, 12, 14,
100) and lt_namespace != 8 and not ((page_namespace = 100 and (lt_namespace <> 10 or lt_title like "הידעת_מדינות_%"))
or (page_namespace = 8 and page_title like "%.js") or (page_namespace = 4 and (page_title like "%OTRS%" or page_title like "%בקשות_לבדיקה%"
or page_title like "%דלפק_ייעוץ%" or page_title like "%הכה_את_המומחה%" or page_title like "%זכויות_יוצרים%" or page_title like
"%יומן_מחיקות%" or page_title like "%סדנה_לגרפיקה%"  or (page_title like "%המלצות_קודמות%" and not page_title in
("ערכים_מומלצים/המלצות_קודמות/אפריל_2016", "ערכים_מומלצים/המלצות_קודמות/מרץ_2016", "ערכים_מומלצים/המלצות_קודמות/מאי_2016")) or
page_title like "%אולם_דיונים%" or page_title like "%עבודות_ויקידמיות%" or page_title like "%ארכיון%" or page_title like
"%סקר_ויקיפדיה%" or page_title like "%איך_להקים_אסם%" or page_title like "%מיזמי_ויקיפדיה/נבחרי_ציבור_פוטנציאליים%" or page_title like
"%הומור%" or page_title like "%רשימת%" or page_title like "%חשבון_נפש%" or page_title like "%סופשבוע_ללא_ערכים%" or page_title like
"%ויקימניה%" or page_title like "%תחרות_צילום%" or page_title like "%מתקפת_איכות%" or page_title like "%מזנון%" or page_title like
"%מועדונים%" or page_title like "%מפגשים%" or page_title like "%תבנית%" or page_title like "%שער_לילדים%" or page_title like
"%שיתופי_פעולה%" or page_title like "%מיזמי_ויקיפדיה%" or page_title like "%בוט_החלפות%" or page_title like "הידעת?/סדרה__/%" or
page_title in ("הידעת?/2018/אוגוסט", "חדשות", "הידעת?/2018/ספטמבר", "הידעת?/ממרץ_2011", "ארגז_חול", "הידעת?", "כיכר_העיר", 
"הידעת?/המתנה") or page_title = "ויקימדיה_ישראל/תמונה_נבחרת/גלריה" and page_namespace = 4 and lt_namespace = 4 and lt_title like
"ויקימדיה_ישראל/תמונה_נבחרת/%")) or exists (select * from page join linktarget where page_namespace = lt_namespace and page_title = lt_title))

How can it work if linktarget does not have any data about non-existing templates? What will be the id in templatelinks and where will be the tempkate name stored?

How can it work if linktarget does not have any data about non-existing templates?

That's not true. It doesn't care if the page exist or not and store it anyway, it can exist or not. That's exactly one of two reasons we created a new table instead of just using page_id.

I see. Thanks for your help.

Zabe subscribed.Apr 2 2022, 7:11 PM

Ladsgroup closed subtask T304780: Write code for enabling compat read for templatelinks and linktarget as Resolved.Apr 25 2022, 5:16 PM

Ladsgroup closed subtask T305064: Make linktarget table visible on cloud wiki replicas as Resolved.May 4 2022, 10:26 PM

Ladsgroup closed subtask T308207: ApiQueryInfo::getProtectionInfo is slow on normalized templatelinks as Resolved.May 16 2022, 11:12 AM

Marostegui closed subtask T300775: Add tl_target_id column to templatelinks as Resolved.May 20 2022, 5:08 AM

Ladsgroup closed subtask T299421: Turn on write both in production for templatelinks normalization as Resolved.May 23 2022, 6:16 AM

Krinkle edited projects, added Performance-Team (Radar); removed Platform Engineering.May 25 2022, 10:00 PM

Krinkle moved this task from Limbo to Watching on the Performance-Team (Radar) board.

Universal_Omega subscribed.Jun 23 2022, 5:10 PM

Ladsgroup mentioned this in T312666: Remove duplication in externallinks table.Jul 8 2022, 6:40 PM

Ladsgroup closed subtask T306674: Add support for write new for templatelinks migration as Resolved.Jul 12 2022, 3:10 PM

I dropped the column (and indexes) in fawiki in beta cluster:

root@deployment-db08:/srv/sqldata/fawiki# ls -Ssh | grep -i templatelinks
9.1M templatelinks.ibd
4.0K templatelinks.frm
root@deployment-db08:/srv/sqldata/fawiki# ls -Ssh | grep -i templatelinks
528K templatelinks.ibd
4.0K templatelinks.frm

5.7% of its original size ^^

FWIW, linktarget is 180K:

root@deployment-db08:/srv/sqldata/fawiki# ls -Ssh | grep -i linktarget
180K linktarget.ibd
4.0K linktarget.frm

Removed it in all of beta cluster now which removed 1GB from beta cluster:

ladsgroup@deployment-db08:~$ df -h
Filesystem                          Size  Used Avail Use% Mounted on
udev                                7.9G     0  7.9G   0% /dev
tmpfs                               1.6G  177M  1.4G  12% /run
/dev/sda2                            19G  4.7G   13G  27% /
tmpfs                               7.9G     0  7.9G   0% /dev/shm
tmpfs                               5.0M     0  5.0M   0% /run/lock
tmpfs                               7.9G     0  7.9G   0% /sys/fs/cgroup
/dev/mapper/vd-second--local--disk  111G   37G   68G  36% /srv
tmpfs                               1.6G     0  1.6G   0% /run/user/0
tmpfs                               1.6G     0  1.6G   0% /run/user/3182
ladsgroup@deployment-db08:~$ df -h
Filesystem                          Size  Used Avail Use% Mounted on
udev                                7.9G     0  7.9G   0% /dev
tmpfs                               1.6G  177M  1.4G  12% /run
/dev/sda2                            19G  4.7G   13G  27% /
tmpfs                               7.9G     0  7.9G   0% /dev/shm
tmpfs                               5.0M     0  5.0M   0% /run/lock
tmpfs                               7.9G     0  7.9G   0% /sys/fs/cgroup
/dev/mapper/vd-second--local--disk  111G   37G   69G  35% /srv
tmpfs                               1.6G     0  1.6G   0% /run/user/0
tmpfs                               1.6G     0  1.6G   0% /run/user/3182

It is now dropped from testwiki in production and it went from 60MB to 20MB there. Given its number of rows and with linear extrapolation we get ~120GB for commons and cebwiki (they are currently on 500GB and 300GB respectively), this means just fixing cebwiki and commonswiki would free 600GB. If you add some other similar wikis like arzwiki, you'd get around a TB of clean up, probably more.

Wow!!!!!! Great work <3

Dropping on s5 now. Started with dbstore1003:3315 and will continue next week (letting it stay there for a while in case writes happen to the old fields).

Impact report:
Before the second schema change, largest wikis of s5:

217G cebwiki/templatelinks.ibd
8.5G dewiki/templatelinks.ibd
6.6G srwiki/templatelinks.ibd
1.9G shwiki/templatelinks.ibd
1.5G mgwiktionary/templatelinks.ibd

In total: 235.5GB

After:

 95G cebwiki/templatelinks.ibd
3.0G dewiki/templatelinks.ibd
2.5G srwiki/templatelinks.ibd
797M shwiki/templatelinks.ibd
729M mgwiktionary/templatelinks.ibd

In total: 102.0GB, 43% of the original size. Note that this is the second schema change, the first one runs optimization on it which already reduced the total size of the db by 13% and 7% in s5 and s4 respectively. The second has done a whooping 21% redaction in size (from 620GB to 490GB) on top of the previous redaction and will definitely trigger an alert in backups.

It has such an impact that dbstore1003 while holding three sections (and we just dropped this on one section only) has reached its lowest storage utilization in the past year meaning all the growth in size in three sections (s1, s5 and s7) over a year has been already offset by this change in s5.

doctaxon subscribed.Aug 19 2022, 6:29 AM

Can you estimate please, when templatelinks column tl_namespace is available again?

In T299417#8168022, @doctaxon wrote:

Can you estimate please, when templatelinks column tl_namespace is available again?

Never. The column is being removed.

In T299417#8168022, @doctaxon wrote:

Can you estimate please, when templatelinks column tl_namespace is available again?

It won't be. You need to join your query with linktarget.

e.g.

select tl_from, tl_namespace, tl_title from templatelinks where ....;

Must become

select tl_from, lt_namespace, lt_title from templatelinks join linktarget on tl_target_id = lt_id where ....;

HTH

Thank, but it's a little bit confusing:

So I have to change the query:

SELECT page_title, page_namespace FROM page, templatelinks
WHERE tl_from = page_id
  AND page_namespace IN (0,100)
  AND tl_from_namespace IN (0,100)
  AND tl_namespace = 10
  AND tl_title = 'Review';

But how to do?

Am Fr., 19. Aug. 2022 um 08:42 Uhr schrieb Ladsgroup <
[email protected]>:

Ladsgroup added a comment. View Task
https://phabricator.wikimedia.org/T299417

In T299417#8168022 https://phabricator.wikimedia.org/T299417#8168022,
@doctaxon https://phabricator.wikimedia.org/p/doctaxon/ wrote:

Can you estimate please, when templatelinks column tl_namespace is
available again?

It won't be. You need to join your query with linktarget.

e.g.

select tl_from, tl_namespace, tl_title from templatelinks where ....;

Must become

select tl_from, lt_namespace, lt_title from templatelinks join linktarget on tl_target_id = lt_id where ....;

HTH

*TASK DETAIL*
https://phabricator.wikimedia.org/T299417

*EMAIL PREFERENCES*
https://phabricator.wikimedia.org/settings/panel/emailpreferences/

*To: *Ladsgroup
*Cc: *doctaxon, Marostegui, Universal_Omega, Zabe, Mormegil, Superyetkin,
MusikAnimal, IKhitron, AntiCompositeNumber, RhinosF1, ArielGlenn, Izno,
Aklapper, Ladsgroup, Devnull, LSobanski, Hazizibinmahdi, Iflorez,
Vali.matei, Minhnv-2809, Jay8g, Krenair

This query:

SELECT page_title, page_namespace FROM page, templatelinks
WHERE tl_from = page_id

AND page_namespace IN (0,100)
AND tl_from_namespace IN (0,100)
AND tl_namespace = 10
AND tl_title = 'Review';

Must become this:

SELECT page_title, page_namespace FROM page join templatelinks
on tl_from = page_id join linktarget on tl_target_id = lt_id

AND page_namespace IN (0,100)
AND tl_from_namespace IN (0,100)
AND lt_namespace = 10
AND lt_title = 'Review';

Tested and works fine.

checked, thank you very much

and thanks for your great database changing works. It's very helpful.

Umherirrender mentioned this in T316297: Prepare cleanupInvalidDbKeys.php for new linktarget table.Aug 25 2022, 9:41 PM

Ladsgroup closed subtask T299424: Run maintenance script backfilling tl_target_id as Resolved.Sep 2 2022, 9:24 AM

Ladsgroup closed subtask T306673: Turn on read new for templatelinks on beta and production as Resolved.Sep 5 2022, 7:35 AM

Change 830114 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[mediawiki/core@master] schema: Drop tl_title and tl_namespace fields from templatelinks

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

gerritbot added a project: Patch-For-Review.Sep 6 2022, 9:29 AM

Change 830219 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[mediawiki/core@REL1_39] schema: Drop tl_title and tl_namespace fields from templatelinks

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

Change 830114 merged by jenkins-bot:

[mediawiki/core@master] schema: Drop tl_title and tl_namespace fields from templatelinks

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

ReleaseTaggerBot added a project: MW-1.40-notes (1.40.0-wmf.1; 2022-09-12).Sep 6 2022, 7:00 PM

Change 830219 abandoned by Ladsgroup:

[mediawiki/core@REL1_39] schema: Drop tl_title and tl_namespace fields from templatelinks

Reason:

let's try again.

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

Change 830219 restored by Ladsgroup:

[mediawiki/core@REL1_39] schema: Drop tl_title and tl_namespace fields from templatelinks

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

Change 830636 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[mediawiki/core@master] Introduce pruneUnusedLinkTargetRows maint script

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

MusikAnimal mentioned this in T317172: Can't see the statistics of wikipedia page edits.Sep 7 2022, 2:22 PM

JJMC89 mentioned this in T317258: Replica templatelinks table is broken for some sites.Sep 7 2022, 10:33 PM

BrandonXLF subscribed.Sep 8 2022, 8:38 AM

Change 830219 merged by Ladsgroup:

[mediawiki/core@REL1_39] schema: Drop tl_title and tl_namespace fields from templatelinks

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

ReleaseTaggerBot added a project: MW-1.39-notes.Sep 8 2022, 8:00 PM

Legoktm updated the task description. (Show Details)Sep 13 2022, 5:00 AM

Legoktm mentioned this in R2060:703408bd7891: Fix queries to use linktarget.Sep 13 2022, 5:49 AM

Change 832157 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[operations/mediawiki-config@master] Stop writing to the old templatelinks columns of enwiki

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

Ladsgroup closed subtask T312865: Turn off writing to the old columns of templatelinks in beta and production as Resolved.Sep 14 2022, 8:34 AM

Ladsgroup closed subtask T312863: Schema change to change primary key of templatelinks as Resolved.

Any SQL query involving the templatelinks table (e.g. select *) on eswiktionary and plwiktionary (group1) now returns:

ERROR 1356 (HY000): View 'eswiktionary_p.templatelinks' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Yeah, I'm trying to run the maintain views there but there are queries that are stuck. I need to depool them which takes a bit.

It should be fixed now.

I can confirm that, thank you!

Cryptic subscribed.Sep 15 2022, 12:07 PM

Change 830636 merged by jenkins-bot:

[mediawiki/core@master] Introduce pruneUnusedLinkTargetRows maint script

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

Change 832563 had a related patch set uploaded (by Jforrester; author: Amir Sarabadani):

[mediawiki/core@REL1_39] Introduce pruneUnusedLinkTargetRows maint script

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

ReleaseTaggerBot edited projects, added MW-1.40-notes (1.40.0-wmf.2; 2022-09-19); removed MW-1.40-notes (1.40.0-wmf.1; 2022-09-12).Sep 16 2022, 8:00 AM

Change 832563 merged by Jforrester:

[mediawiki/core@REL1_39] Introduce pruneUnusedLinkTargetRows maint script

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

We have been dropping the old columns which now affects users. While this was announced a while ago (plus regular updates afterwards) but let's add a line in tech news about this.

Something like:

Two fields of tl_namespace and tl_title in templatelinks table are now being dropped. Queries relying on these fields need to change to use the new normalization field called tl_target_id. See T299417 for more information. This is part of normalization of links tables [1]

(edit mercilessly)

I also wrote a document explaining why we need to do normalization: https://www.mediawiki.org/wiki/User:ASarabadani_(WMF)/Database_for_devs_toolkit/Concepts/Normalization Maybe that'd be useful.

Quiddity moved this task from To Triage to In current Tech/News draft on the User-notice board.Sep 16 2022, 7:27 PM

valerio.bozzolan subscribed.Sep 20 2022, 7:42 AM

Quiddity moved this task from In current Tech/News draft to Already announced/Archive on the User-notice board.Sep 22 2022, 5:52 PM

Is the new linktarget table publicly available somewhere? My bot regularly updates maintenance lists (https://fr.wikipedia.org/wiki/Projet:Mod%C3%A8le/Maintenance/Listes) based on the template dump of frwiki (https://dumps.wikimedia.org/frwiki/latest/frwiki-latest-templatelinks.sql.gz). After this update, I don't see a way to reconstruct the fields that were present before, because I don't know where to find the data to resolve target_ids.

@Orlodrim: see T315063; the table will be dumped, but some required work hasn't been done yet. In the meantime, the table is available in the Toolforge database replicas.

MusikAnimal mentioned this in T318823: Update tools following templatelinks normalization.Sep 28 2022, 2:57 PM

Steenth subscribed.Oct 2 2022, 1:47 PM

Hi, the change also affected Pi bot's deployment of Wikidata Infoboxes on Commons, help rewriting the query would be appreciated at T320314!