CategoryChangesAsRdf::handleDeletes fails with Error 1176: Key 'rc_new_name_timestamp' doesn't exist in table 'recentchanges'
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	dcausse
	Feb 10 2025, 3:43 PM

Description

Seen in logs of the categoriesrdf dump systemd timer on snapshot1016@/var/log/categoriesrdf/zhwiki-20250210-daily-daily.log:

Wikimedia\Rdbms\DBQueryError from line 1230 of /srv/mediawiki/php-1.44.0-wmf.15/includes/libs/rdbms/database/Database.php: Error 1176: Key 'rc_new_name_timestamp' doesn't exist in table 'recentchanges'
Function: CategoryChangesAsRdf::handleDeletes
Query: SELECT  rc_cur_id,rc_title,rc_timestamp  FROM `recentchanges` FORCE INDEX (rc_new_name_timestamp)    WHERE rc_namespace = 14 AND rc_new = 0 AND rc_log_type = 'delete' AND rc_log_action = 'delete' AND rc_type = 3 AND (NOT EXISTS (SELECT * FROM page WHERE page_id = rc_cur_id)) AND (rc_timestamp >= '20250209050000') AND (rc_timestamp < '20250210050001')  ORDER BY rc_timestamp ASC LIMIT 200

#0 /srv/mediawiki/php-1.44.0-wmf.15/includes/libs/rdbms/database/Database.php(1214): Wikimedia\Rdbms\Database->getQueryException('Key 'rc_new_nam...', 1176, 'SELECT  rc_cur_...', 'CategoryChanges...')
#1 /srv/mediawiki/php-1.44.0-wmf.15/includes/libs/rdbms/database/Database.php(1188): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('Key 'rc_new_nam...', 1176, 'SELECT  rc_cur_...', 'CategoryChanges...')
#2 /srv/mediawiki/php-1.44.0-wmf.15/includes/libs/rdbms/database/Database.php(647): Wikimedia\Rdbms\Database->reportQueryError('Key 'rc_new_nam...', 1176, 'SELECT  rc_cur_...', 'CategoryChanges...', false)
#3 /srv/mediawiki/php-1.44.0-wmf.15/includes/libs/rdbms/database/Database.php(1365): Wikimedia\Rdbms\Database->query(Object(Wikimedia\Rdbms\Query), 'CategoryChanges...')
#4 /srv/mediawiki/php-1.44.0-wmf.15/includes/libs/rdbms/database/DBConnRef.php(127): Wikimedia\Rdbms\Database->select(Array, Array, Array, 'CategoryChanges...', Array, Array)
#5 /srv/mediawiki/php-1.44.0-wmf.15/includes/libs/rdbms/database/DBConnRef.php(351): Wikimedia\Rdbms\DBConnRef->__call('select', Array)
#6 /srv/mediawiki/php-1.44.0-wmf.15/includes/libs/rdbms/querybuilder/SelectQueryBuilder.php(762): Wikimedia\Rdbms\DBConnRef->select(Array, Array, Array, 'CategoryChanges...', Array, Array)
#7 /srv/mediawiki/php-1.44.0-wmf.15/includes/utils/BatchRowIterator.php(213): Wikimedia\Rdbms\SelectQueryBuilder->fetchResultSet()
#8 /srv/mediawiki/php-1.44.0-wmf.15/includes/utils/BatchRowIterator.php(184): BatchRowIterator->next()
#9 /srv/mediawiki/php-1.44.0-wmf.15/maintenance/categoryChangesAsRdf.php(433): BatchRowIterator->rewind()
#10 /srv/mediawiki/php-1.44.0-wmf.15/maintenance/categoryChangesAsRdf.php(147): CategoryChangesAsRdf->handleDeletes(Object(Wikimedia\Rdbms\DBConnRef), Resource id #1446)
#11 /srv/mediawiki/php-1.44.0-wmf.15/maintenance/includes/MaintenanceRunner.php(695): CategoryChangesAsRdf->execute()
#12 /srv/mediawiki/php-1.44.0-wmf.15/maintenance/run.php(51): MediaWiki\Maintenance\MaintenanceRunner->run()
#13 /srv/mediawiki/multiversion/MWScript.php(156): require_once('/srv/mediawiki/...')
#14 {main}

Seems to be affecting these sites:

dcausse@wdqs1020:~$ curl -s -XPOST --data-urlencode 'query=SELECT ?wiki  WHERE { ?wiki schema:dateModified ?date FILTER (?date < "2025-02-08T00:00:01Z"^^xsd:dateTime) }' http://localhost/bigdata/namespace/categories/sparql?format=json | jq '.results.bindings[] | .wiki.value' -r
https://bg.wikipedia.org/wiki/Special:CategoryDump
https://bg.wiktionary.org/wiki/Special:CategoryDump
https://cs.wikipedia.org/wiki/Special:CategoryDump
https://en.wikiquote.org/wiki/Special:CategoryDump
https://en.wiktionary.org/wiki/Special:CategoryDump
https://eo.wikipedia.org/wiki/Special:CategoryDump
https://fi.wikipedia.org/wiki/Special:CategoryDump
https://id.wikipedia.org/wiki/Special:CategoryDump
https://it.wikipedia.org/wiki/Special:CategoryDump
https://nl.wikipedia.org/wiki/Special:CategoryDump
https://no.wikipedia.org/wiki/Special:CategoryDump
https://pl.wikipedia.org/wiki/Special:CategoryDump
https://pt.wikipedia.org/wiki/Special:CategoryDump
https://sv.wikipedia.org/wiki/Special:CategoryDump
https://th.wikipedia.org/wiki/Special:CategoryDump
https://tr.wikipedia.org/wiki/Special:CategoryDump
https://zh.wikipedia.org/wiki/Special:CategoryDump

And from the snapshot1016 logs (which seems to be agree):

for f in /var/log/categoriesrdf/*20250208*; do grep rc_new_name_timestamp $f > /dev/null && echo $f | xargs -I{} basename {} -20250208-daily-daily.log.1 ; done
bgwiki
bgwiktionary
cswiki
enwikiquote
enwiktionary
eowiki
fiwiki
idwiki
itwiki
nlwiki
nowiki
plwiki
ptwiki
svwiki
thwiki
trwiki
zhwiki

Related Objects

Mentioned Here: T276292: Schema change for renaming new_name_timestamp to rc_new_name_timestamp in recentchanges
T270033: Fix and enforce table prefix usage in columns and indexes in core

Event Timeline

dcausse created this task.Feb 10 2025, 3:43 PM

Restricted Application added subscribers: Stang, Aklapper. · View Herald TranscriptFeb 10 2025, 3:43 PM

bking subscribed.Feb 10 2025, 3:47 PM

Gehel moved this task from needs triage to Current work on the Discovery-Search board.Feb 10 2025, 4:24 PM

Gehel edited projects, added Discovery-Search (Current work); removed Discovery-Search.

dcausse updated the task description. (Show Details)Feb 10 2025, 4:48 PM

dcausse added a project: Data-Platform-SRE.

Related to T270033 and https://gerrit.wikimedia.org/r/c/mediawiki/core/+/667358 specifically I think

It looks like this was the ticket tracking the changes required: T276292: Schema change for renaming new_name_timestamp to rc_new_name_timestamp in recentchanges

All of the affected databases seem to be in section 2: https://noc.wikimedia.org/db.php#tabs-s2

@Ladsgroup @Marostegui - Is it possible that s2 on dbstore1007 did not receive this index rename change?

If so, how should we handle this? Would you prefer to apply it yourselves, or would you prefer us to do it? I'd be happy to apply it in principle, but I'm not very familiar with the tooling that you use to automate it.

We have a system to track drifts between schema and production. So far it only checks on mediawiki replicas. I can expand it to dbstore* too (but it's actually not super easy). For this specific case, I will fix it there.

In T386005#10538565, @Ladsgroup wrote:

We have a system to track drifts between schema and production. So far it only checks on mediawiki replicas. I can expand it to dbstore* too (but it's actually not super easy). For this specific case, I will fix it there.

Many thanks @Ladsgroup - A manual fixup would be great for now, thanks.
Should we make a ticket for adding analytics replica support to the schema drift monitor?

I stumbled upon this: https://drift-tracker.toolforge.org/report/core/ - is this the same system to which you were referring?

In T386005#10538578, @BTullis wrote:

In T386005#10538565, @Ladsgroup wrote:

We have a system to track drifts between schema and production. So far it only checks on mediawiki replicas. I can expand it to dbstore* too (but it's actually not super easy). For this specific case, I will fix it there.

Many thanks @Ladsgroup - A manual fixup would be great for now, thanks.

I get to it now.

Should we make a ticket for adding analytics replica support to the schema drift monitor?

Yeah, that'd be nice

I stumbled upon this: https://drift-tracker.toolforge.org/report/core/ - is this the same system to which you were referring?

That's the web frontend of it yes. The main piece is the script that produces a json file and this web interface just shows that file in a pretty way.

Done now. On all dbs of s2 in dbstore1007:3312. The original schema change predates auto_schema so I can't run the schema change file with --check to see what's missing in other hosts. I'll have to go drift tracker route on this.

Gehel triaged this task as High priority.Feb 11 2025, 1:58 PM

Gehel edited projects, added Data-Platform-SRE (2025.02.10 - 2025.02.28); removed Data-Platform-SRE.

Gehel moved this task from Backlog - project to In Progress on the Data-Platform-SRE (2025.02.10 - 2025.02.28) board.

Gehel edited projects, added Discovery-Search (2025.02.10 - 2025.02.28); removed Discovery-Search (Current work).Feb 11 2025, 2:20 PM