Page MenuHomePhabricator

Warning: SQLPlatform::isWriteQuery fallback to regex (from Wikibase EntityUsageTable)
Closed, ResolvedPublic

Description

The immediate reason for the warning is that it is calling query() without indication of whether it is a write query or not, thus requiring Rdbms to perform its legacy regexes on the SQL text to guess it.

However, more important than whether it passes the third parameter of query(), is that it calls query() at all, as this should not be used outside Rdbms internals. It exposes raw SQL and should be avoided in favour of safe interfaces like SelectQueryBuilder or IDatabase::select().

It is currently triggered approximately 100K/hour, making it the most common entry on the mediawiki-warnings dashboard in Logstash, across all MW channels and components. The instrumentation was added last month in https://gerrit.wikimedia.org/r/c/mediawiki/core/ /894110 to detect exactly this issue, on the assumption it will likely not trigger anything in production. Wikibase's use was one of two callers we found in production.

message
Wikimedia\Rdbms\Platform\SQLPlatform::isWriteQuery fallback to regex
trace
#0 /srv/mediawiki/php-1.41.0-wmf.1/includes/libs/rdbms/database/Database.php(934): Wikimedia\Rdbms\Platform\SQLPlatform->isWriteQuery(string, integer)
…
#4 /srv/mediawiki/php-1.41.0-wmf.1/extensions/Wikibase/client/includes/Usage/Sql/EntityUsageTable.php(474): Wikimedia\Rdbms\DBConnRef->query(string, string)
#5 /srv/mediawiki/php-1.41.0-wmf.1/extensions/Wikibase/client/includes/Usage/Sql/EntityUsageTable.php(407): Wikibase\Client\Usage\Sql\EntityUsageTable->getUsedEntityIdStringsMySql(array, Wikimedia\Rdbms\DBConnRef)
#6 /srv/mediawiki/php-1.41.0-wmf.1/extensions/Wikibase/client/includes/Usage/Sql/EntityUsageTable.php(389): Wikibase\Client\Usage\Sql\EntityUsageTable->getUsedEntityIdStrings(array)
#7 /srv/mediawiki/php-1.41.0-wmf.1/extensions/Wikibase/client/includes/Usage/Sql/SqlUsageTracker.php(273): Wikibase\Client\Usage\Sql\EntityUsageTable->getUnusedEntities(array)
#8 /srv/mediawiki/php-1.41.0-wmf.1/extensions/Wikibase/client/includes/Usage/ImplicitDescriptionUsageLookup.php(303): Wikibase\Client\Usage\Sql\SqlUsageTracker->getUnusedEntities(array)
#9 /srv/mediawiki/php-1.41.0-wmf.1/extensions/Wikibase/client/includes/Store/UsageUpdater.php(80): Wikibase\Client\Usage\ImplicitDescriptionUsageLookup->getUnusedEntities(array)
#10 /srv/mediawiki/php-1.41.0-wmf.1/extensions/Wikibase/client/includes/Store/AddUsagesForPageJob.php(121): Wikibase\Client\Store\UsageUpdater->addUsagesForPage(integer, array)
#11 /srv/mediawiki/php-1.41.0-wmf.1/extensions/EventBus/includes/JobExecutor.php(79): Wikibase\Client\Store\AddUsagesForPageJob->run()
#12 /srv/mediawiki/rpc/RunSingleJob.php(77): MediaWiki\Extension\EventBus\JobExecutor->execute(array)

Event Timeline

The Wikibase code is using IDatabase::unionQueries(). It seems the Rdbms library currently doesn't offer any other way to run union queries.

Change 902668 had a related patch set uploaded (by Lucas Werkmeister (WMDE); author: Lucas Werkmeister (WMDE)):

[mediawiki/extensions/Wikibase@master] EntityUsageTable: Mark query as read-only

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

Change 902668 merged by jenkins-bot:

[mediawiki/extensions/Wikibase@master] EntityUsageTable: Mark query as read-only

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

Change 903186 had a related patch set uploaded (by Ladsgroup; author: Lucas Werkmeister (WMDE)):

[mediawiki/extensions/Wikibase@wmf/1.41.0-wmf.1] EntityUsageTable: Mark query as read-only

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

Change 903186 merged by jenkins-bot:

[mediawiki/extensions/Wikibase@wmf/1.41.0-wmf.1] EntityUsageTable: Mark query as read-only

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

Mentioned in SAL (#wikimedia-operations) [2023-03-27T08:20:59Z] <ladsgroup@deploy1002> Started scap: Backport for [[gerrit:903186|EntityUsageTable: Mark query as read-only (T332941)]]

Mentioned in SAL (#wikimedia-operations) [2023-03-27T08:30:29Z] <ladsgroup@deploy1002> ladsgroup: Backport for [[gerrit:903186|EntityUsageTable: Mark query as read-only (T332941)]] synced to the testservers: mwdebug2001.codfw.wmnet, mwdebug1001.eqiad.wmnet, mwdebug1002.eqiad.wmnet, mwdebug2002.codfw.wmnet

Mentioned in SAL (#wikimedia-operations) [2023-03-27T08:39:15Z] <ladsgroup@deploy1002> Finished scap: Backport for [[gerrit:903186|EntityUsageTable: Mark query as read-only (T332941)]] (duration: 18m 15s)

Change 904142 had a related patch set uploaded (by Lucas Werkmeister (WMDE); author: Lucas Werkmeister (WMDE)):

[mediawiki/extensions/Wikibase@REL1_40] EntityUsageTable: Mark query as read-only

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

Change 904802 had a related patch set uploaded (by Lucas Werkmeister (WMDE); author: Lucas Werkmeister (WMDE)):

[mediawiki/extensions/Wikibase@master] EntityUsageTable: Mark query as read-only (part 2)

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

Change 904802 merged by jenkins-bot:

[mediawiki/extensions/Wikibase@master] EntityUsageTable: Mark query as read-only (part 2)

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

Change 904695 had a related patch set uploaded (by Lucas Werkmeister (WMDE); author: Lucas Werkmeister (WMDE)):

[mediawiki/extensions/Wikibase@REL1_40] EntityUsageTable: Mark query as read-only (part 2)

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

Change 904142 merged by jenkins-bot:

[mediawiki/extensions/Wikibase@REL1_40] EntityUsageTable: Mark query as read-only

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

Change 904695 merged by jenkins-bot:

[mediawiki/extensions/Wikibase@REL1_40] EntityUsageTable: Mark query as read-only (part 2)

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