BIGINT UNSIGNED value is out of range Function: SiteStatsUpdate::doUpdateQuery: UPDATE `site_stats` SET ss_images=GREATEST(`ss_images`-1,0)
Closed, ResolvedPublicPRODUCTION ERROR
Actions

Assigned To

Authored By

	thcipriani
	Aug 18 2022, 3:31 PM

Description

Error

mwversion: 1.39.0-wmf.25
reqId: 352d5d3b5393131a08321b69
Find reqId in Logstash

normalized_message

[{reqId}] {exception_url}   Wikimedia\Rdbms\DBQueryError: Error 1690: BIGINT UNSIGNED value is out of range in '`testwiki`.`site_stats`.`ss_images` - 1'
Function: SiteStatsUpdate::doUpdate
Query: UPDATE  `site_stats` SET ss_images=GREATEST(`ss_images`-1,0

exception.trace

from /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/libs/rdbms/database/Database.php(1742)
#0 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/libs/rdbms/database/Database.php(1726): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/libs/rdbms/database/Database.php(1700): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/libs/rdbms/database/Database.php(1076): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/libs/rdbms/database/Database.php(1966): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/libs/rdbms/database/DBConnRef.php(103): Wikimedia\Rdbms\Database->update(string, array, array, string)
#5 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/libs/rdbms/database/DBConnRef.php(395): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#6 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/deferred/SiteStatsUpdate.php(148): Wikimedia\Rdbms\DBConnRef->update(string, array, array, string)
#7 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/deferred/AutoCommitUpdate.php(44): SiteStatsUpdate::{closure}(Wikimedia\Rdbms\DBConnRef, string)
#8 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/deferred/SiteStatsUpdate.php(160): AutoCommitUpdate->doUpdate()
#9 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/deferred/DeferredUpdates.php(474): SiteStatsUpdate->doUpdate()
#10 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/deferred/DeferredUpdates.php(399): DeferredUpdates::attemptUpdate(SiteStatsUpdate, Wikimedia\Rdbms\LBFactoryMulti)
#11 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/deferred/DeferredUpdates.php(214): DeferredUpdates::run(SiteStatsUpdate, Wikimedia\Rdbms\LBFactoryMulti, Monolog\Logger, BufferingStatsdDataFactory, MediaWiki\JobQueue\JobQueueGroupFactory, string)
#12 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/deferred/DeferredUpdatesScope.php(267): DeferredUpdates::{closure}(SiteStatsUpdate, integer)
#13 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/deferred/DeferredUpdatesScope.php(196): DeferredUpdatesScope->processStageQueue(integer, integer, Closure)
#14 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/deferred/DeferredUpdates.php(235): DeferredUpdatesScope->processUpdates(integer, Closure)
#15 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/deferred/DeferredUpdates.php(281): DeferredUpdates::doUpdates(NULL, integer)
#16 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/deferred/DeferredUpdates.php(134): DeferredUpdates::tryOpportunisticExecute()
#17 /srv/mediawiki-staging/php-1.39.0-wmf.25/includes/filerepo/file/LocalFile.php(2326): DeferredUpdates::addUpdate(SiteStatsUpdate)
#18 /srv/mediawiki-staging/php-1.39.0-wmf.25/maintenance/deleteBatch.php(109): LocalFile->deleteFile(string, User)
#19 /srv/mediawiki-staging/php-1.39.0-wmf.25/maintenance/includes/MaintenanceRunner.php(309): DeleteBatch->execute()
#20 /srv/mediawiki-staging/php-1.39.0-wmf.25/maintenance/doMaintenance.php(85): MediaWiki\Maintenance\MaintenanceRunner->run()
#21 /srv/mediawiki-staging/php-1.39.0-wmf.25/maintenance/deleteBatch.php(141): require_once(string)
#22 /srv/mediawiki-staging/multiversion/MWScript.php(120): require_once(string)
#23 {main}

Impact

Currently seeing many errors triggered by a run of mwscript deleteBatch.php

Details

Related Changes in Gerrit:

	Subject	Repo	Branch	Lines +/-
	Fix GREATEST usage in site_stats	mediawiki/core	REL1_43	+4 -7
	Fix GREATEST usage in site_stats	mediawiki/core	master	+4 -7

Customize query in gerrit

Related Objects

Mentioned Here: T306589: Add sharding to site_stats table

Event Timeline

thcipriani created this task.Aug 18 2022, 3:31 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 18 2022, 3:31 PM

@Ladsgroup is this possibly a schema change?

Nope but I think I know why it's broken, it's probably due to T306589 but let me double check

Yeah, tit's because of that but I'm a bit confused why maint script is being ran to delete lots of pages and in deploy host (instead of mwmaint?) but that's fine, it's only on testwiki, and the stats numbers are just wrong, nothing major.

thcipriani moved this task from Untriaged to Aug 2022 on the Wikimedia-production-error board.Aug 18 2022, 3:57 PM

This is error is extremely unlikely to happen on any non-test wiki but it might happen in testwiki again. I suggest declining this.

reedy@deploy1002:~$ mwscript initSiteStats.php --wiki=testwiki --update
Refresh Site Statistics

Counting total edits...532187
Counting number of articles...5220
Counting total pages...71049
Counting number of users...53774
Counting number of images...6460

Updating site statistics...done.

Done.
reedy@deploy1002:~$

The GREATEST is there to protect against an out of range and going to negative numbers

select greatest( -1, 0 ) from dual

gives 0,

But when providing a database column the datatype of that is used and it is not possible to have negative number as result of BIGINT 0 -1 before it is going to GREATEST.
That makes the GREATEST useless (for mysql)

In T315573#8166451, @Umherirrender wrote:

The GREATEST is there to protect against an out of range and going to negative numbers

select greatest( -1, 0 ) from dual

gives 0,

But when providing a database column the datatype of that is used and it is not possible to have negative number as result of BIGINT 0 -1 before it is going to GREATEST.
That makes the GREATEST useless (for mysql)

Yup, that was the issue I ran into when I was testing it.

Let me explain to people who don't know. This is logical sharding for updating site_stats, if a wiki has a lot of edits at the same time, the lock contention on that one row holding the info goes pretty high. The change effectively makes the updater to pick a random row (out of ten) and update that instead, it drastically reduced lock contention in wikis that have it.

So in theory, it might end up trying to update a row that's zero trying to make it negative. but this is enabled only on English Wikipedia, Wikidata and test Wikipedia. So unless Wikidata decides to delete 100K pages tomorrow or English Wikipedia deciding to delete 3000 articles, or 50K pages or 200 images back to back, it won't happen. I admit the issue of images in English Wikipedia is a bit scary but keep it in mind that with time, these numbers grow and the chance of it happening becomes smaller. i.e. I'm not sure it's worth fixing.