[{reqId}] {exception_url} Wikimedia\Rdbms\DBQueryError: Error 1062: Duplicate entry 'X-X-X' for key 'PRIMARY' Function: MediaWiki\CheckUser\Services\UserAgentClientHintsManager::insertMappingRows
Closed, ResolvedPublicPRODUCTION ERROR
Actions

Description

Error

message

[{reqId}] {exception_url}   Wikimedia\Rdbms\DBQueryError: Error 1062: Duplicate entry 'X-X-X' for key 'PRIMARY'
Function: MediaWiki\CheckUser\Services\UserAgentClientHintsManager::insertMappingRows
Query: INSERT INTO `cu_useragent_clienthints_

trace

from /srv/mediawiki/php-1.41.0-wmf.22/includes/libs/rdbms/database/Database.php(1236)
#0 /srv/mediawiki/php-1.41.0-wmf.22/includes/libs/rdbms/database/Database.php(1220): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /srv/mediawiki/php-1.41.0-wmf.22/includes/libs/rdbms/database/Database.php(1194): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /srv/mediawiki/php-1.41.0-wmf.22/includes/libs/rdbms/database/Database.php(679): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /srv/mediawiki/php-1.41.0-wmf.22/includes/libs/rdbms/database/Database.php(1509): Wikimedia\Rdbms\Database->query(Wikimedia\Rdbms\Query, string)
#4 /srv/mediawiki/php-1.41.0-wmf.22/includes/libs/rdbms/database/DBConnRef.php(119): Wikimedia\Rdbms\Database->insert(string, array, string)
#5 /srv/mediawiki/php-1.41.0-wmf.22/includes/libs/rdbms/database/DBConnRef.php(402): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#6 /srv/mediawiki/php-1.41.0-wmf.22/extensions/CheckUser/src/Services/UserAgentClientHintsManager.php(170): Wikimedia\Rdbms\DBConnRef->insert(string, array, string)
#7 /srv/mediawiki/php-1.41.0-wmf.22/extensions/CheckUser/src/Services/UserAgentClientHintsManager.php(105): MediaWiki\CheckUser\Services\UserAgentClientHintsManager->insertMappingRows(MediaWiki\CheckUser\ClientHints\ClientHintsData, integer, string, boolean)
#8 /srv/mediawiki/php-1.41.0-wmf.22/extensions/CheckUser/src/Api/Rest/Handler/UserAgentClientHintsHandler.php(94): MediaWiki\CheckUser\Services\UserAgentClientHintsManager->insertClientHintValues(MediaWiki\CheckUser\ClientHints\ClientHintsData, integer, string)
#9 /srv/mediawiki/php-1.41.0-wmf.22/includes/Rest/SimpleHandler.php(38): MediaWiki\CheckUser\Api\Rest\Handler\UserAgentClientHintsHandler->run(string, integer)
#10 /srv/mediawiki/php-1.41.0-wmf.22/includes/Rest/Router.php(517): MediaWiki\Rest\SimpleHandler->execute()
#11 /srv/mediawiki/php-1.41.0-wmf.22/includes/Rest/Router.php(422): MediaWiki\Rest\Router->executeHandler(MediaWiki\CheckUser\Api\Rest\Handler\UserAgentClientHintsHandler)
#12 /srv/mediawiki/php-1.41.0-wmf.22/includes/Rest/EntryPoint.php(195): MediaWiki\Rest\Router->execute(MediaWiki\Rest\RequestFromGlobals)
#13 /srv/mediawiki/php-1.41.0-wmf.22/includes/Rest/EntryPoint.php(135): MediaWiki\Rest\EntryPoint->execute()
#14 /srv/mediawiki/php-1.41.0-wmf.22/rest.php(31): MediaWiki\Rest\EntryPoint::main()
#15 /srv/mediawiki/w/rest.php(3): require(string)
#16 {main}

Impact

Stops storage of Client Hints data.

Notes

Based on an inspection of the query, it seems like the INSERT query attempts to insert the same row twice. This query is not an INSERT IGNORE query, but the attempt to insert the same row in the same query should be fixed instead of just specifying an ignore.

Details

Subject	Repo	Branch	Lines +/-
clienthints: Remove duplicate entries when converting to DB rows	mediawiki/extensions/CheckUser	wmf/1.41.0-wmf.22	+122 -1
clienthints: Remove duplicate entries when converting to DB rows	mediawiki/extensions/CheckUser	wmf/1.41.0-wmf.23	+122 -1
clienthints: Remove duplicate entries when converting to DB rows	mediawiki/extensions/CheckUser	master	+122 -1

Customize query in gerrit

Related Objects
Search...

		Status	Subtype	Assigned	Task
		Resolved		kostajh	T257893 [EPIC] Support User-Agent Client Hints header in CheckUser
		Resolved	PRODUCTION ERROR	Dreamy_Jazz	T344787 [{reqId}] {exception_url} Wikimedia\Rdbms\DBQueryError: Error 1062: Duplicate entry 'X-X-X' for key 'PRIMARY' Function: MediaWiki\CheckUser\Services\UserAgentClientHintsManager::insertMappingRows

Event Timeline

Dreamy_Jazz created this task.Aug 23 2023, 6:30 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 23 2023, 6:30 AM

Dreamy_Jazz updated the task description. (Show Details)Aug 23 2023, 6:32 AM

Dreamy_Jazz updated the task description. (Show Details)

JJMC89 merged a task: T344788: Error 1062 from MediaWiki\CheckUser\Services\UserAgentClientHintsManager::insertMappingRows, Duplicate entry '1147-0-1171766075' for key 'PRIMARY' INSERT INTO `cu_useragent_clienthints_map`.Aug 23 2023, 6:36 AM

JJMC89 added a subscriber: kostajh.

Dreamy_Jazz added a parent task: T257893: [EPIC] Support User-Agent Client Hints header in CheckUser.Aug 23 2023, 6:44 AM

Change 951822 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/CheckUser@master] clienthints: Remove duplicate entries when inserting to map table

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

gerritbot added a project: Patch-For-Review.Aug 23 2023, 6:55 AM

Dreamy_Jazz moved this task from Untriaged to Aug 2023 on the Wikimedia-production-error board.Aug 23 2023, 6:55 AM

Dreamy_Jazz added a project: Anti-Harassment (AHaT Sprint 32 - Baseball Cap).

Dreamy_Jazz moved this task from Ready 🎬 (ONLY IF YOU HAVE NO MORE CODE TO REVIEW) to Code Review 🔍 on the Anti-Harassment (AHaT Sprint 32 - Baseball Cap) board.

Change 951822 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] clienthints: Remove duplicate entries when converting to DB rows

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

Change 951846 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/CheckUser@wmf/1.41.0-wmf.22] clienthints: Remove duplicate entries when converting to DB rows

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

Change 951847 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/CheckUser@wmf/1.41.0-wmf.23] clienthints: Remove duplicate entries when converting to DB rows

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

Dreamy_Jazz mentioned this in T344797: Decrease CheckUserClientHintsRestApiMaxTimeLag config on production wikis.Aug 23 2023, 8:29 AM

Dreamy_Jazz moved this task from Code Review 🔍 to QA/Testing 🐞 on the Anti-Harassment (AHaT Sprint 32 - Baseball Cap) board.Aug 23 2023, 8:41 AM

kostajh assigned this task to Dreamy_Jazz.Aug 23 2023, 10:12 AM

Change 951847 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@wmf/1.41.0-wmf.23] clienthints: Remove duplicate entries when converting to DB rows

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

Mentioned in SAL (#wikimedia-operations) [2023-08-23T13:17:42Z] <lucaswerkmeister-wmde@deploy1002> Started scap: Backport for [[gerrit:951847|clienthints: Remove duplicate entries when converting to DB rows (T344787)]]

Mentioned in SAL (#wikimedia-operations) [2023-08-23T13:19:17Z] <lucaswerkmeister-wmde@deploy1002> lucaswerkmeister-wmde and dreamyjazz: Backport for [[gerrit:951847|clienthints: Remove duplicate entries when converting to DB rows (T344787)]] synced to the testservers mwdebug1001.eqiad.wmnet, mwdebug2001.codfw.wmnet, mwdebug2002.codfw.wmnet, mwdebug1002.eqiad.wmnet, and mw-debug kubernetes deployment (accessible via k8s-experimental XWD option)

Mentioned in SAL (#wikimedia-operations) [2023-08-23T13:38:55Z] <lucaswerkmeister-wmde@deploy1002> Finished scap: Backport for [[gerrit:951847|clienthints: Remove duplicate entries when converting to DB rows (T344787)]] (duration: 21m 12s)

Change 951846 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@wmf/1.41.0-wmf.22] clienthints: Remove duplicate entries when converting to DB rows

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

Mentioned in SAL (#wikimedia-operations) [2023-08-23T13:53:19Z] <lucaswerkmeister-wmde@deploy1002> Started scap: Backport for [[gerrit:951846|clienthints: Remove duplicate entries when converting to DB rows (T344787)]]

Mentioned in SAL (#wikimedia-operations) [2023-08-23T13:54:49Z] <lucaswerkmeister-wmde@deploy1002> dreamyjazz and lucaswerkmeister-wmde: Backport for [[gerrit:951846|clienthints: Remove duplicate entries when converting to DB rows (T344787)]] synced to the testservers mwdebug1002.eqiad.wmnet, mwdebug2002.codfw.wmnet, mwdebug1001.eqiad.wmnet, mwdebug2001.codfw.wmnet, and mw-debug kubernetes deployment (accessible via k8s-experimental XWD option)

Mentioned in SAL (#wikimedia-operations) [2023-08-23T14:06:30Z] <lucaswerkmeister-wmde@deploy1002> Finished scap: Backport for [[gerrit:951846|clienthints: Remove duplicate entries when converting to DB rows (T344787)]] (duration: 13m 10s)

Maintenance_bot removed a project: Patch-For-Review.Aug 23 2023, 2:10 PM

Dreamy_Jazz mentioned this in T344875: [{reqId}] {exception_url} Wikimedia\Rdbms\DBQueryError: Error 1062: Duplicate entry 'X-X-X' for key 'PRIMARY' Function: MediaWiki\CheckUser\Services\UserAgentClientHintsManager::insertMappingRows Query: INSERT INTO `cu_useragent_clienthints_map where query doesn't include duplicated rows.Aug 24 2023, 12:07 AM

@Dreamy_Jazz I think I can reproduce this, in a slightly different way, although I am not exactly sure how. The JSON payloads:

{"brands": {"-70": "n", "null": -19480, "false": "\\u00887\\u0007", "\\u0016\\ud95c\\udda46BX": true, "m": ""}, "fullVersionList": ["\\ud99f\\udfdb", "\\u00f8\\n\\ud8e2\\udf67\\u001e", null]}
{"brands": {"-70": "n"}, "fullVersionList": ["\\ud99f\\udfdb"]}
{'bitness': None, 'platformVersion': 29280, 'brands': {-70: 'n', None: -19480, False: '\x887\x07', '\x16\U000671a46BX': True, 'm': ''}, 'platform': '', 'architecture': None, 'fullVersionList': ['\U00077fdb', 'ø\n\U00048b67\x1e', None], 'mobile': 'r', 'model': False}

Returns

{
  "message": "Error: exception of type Wikimedia\\Rdbms\\DBQueryError: Error 1062: Duplicate entry '0-0-16881' for key 'PRIMARY'\nFunction: MediaWiki\\CheckUser\\Services\\UserAgentClientHintsManager::insertMappingRows\nQuery: INSERT INTO `cu_useragent_clienthints_map` (uachm_uach_id,uachm_reference_type,uachm_reference_id) VALUES (0,0,16881),(0,0,16881)\n",
  "exception": {
    "id": "2e8bb688564afc0877ef4559",
    "type": "Wikimedia\\Rdbms\\DBQueryError",
    "file": "/home/drw/wikimedia/srv/bare/includes/libs/rdbms/database/Database.php",
    "line": 1236,
    "message": "Error 1062: Duplicate entry '0-0-16881' for key 'PRIMARY'\nFunction: MediaWiki\\CheckUser\\Services\\UserAgentClientHintsManager::insertMappingRows\nQuery: INSERT INTO `cu_useragent_clienthints_map` (uachm_uach_id,uachm_reference_type,uachm_reference_id) VALUES (0,0,16881),(0,0,16881)\n",
    "code": 0,
    "url": "/bare/rest.php/checkuser/v0/useragent-clienthints/revision/16881",
    "caught_by": "other",
    "backtrace": [
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/libs/rdbms/database/Database.php",
        "line": 1220,
        "function": "getQueryException",
        "class": "Wikimedia\\Rdbms\\Database",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/libs/rdbms/database/Database.php",
        "line": 1194,
        "function": "getQueryExceptionAndLog",
        "class": "Wikimedia\\Rdbms\\Database",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/libs/rdbms/database/Database.php",
        "line": 679,
        "function": "reportQueryError",
        "class": "Wikimedia\\Rdbms\\Database",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/libs/rdbms/database/Database.php",
        "line": 1509,
        "function": "query",
        "class": "Wikimedia\\Rdbms\\Database",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/libs/rdbms/database/DBConnRef.php",
        "line": 119,
        "function": "insert",
        "class": "Wikimedia\\Rdbms\\Database",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/libs/rdbms/database/DBConnRef.php",
        "line": 402,
        "function": "__call",
        "class": "Wikimedia\\Rdbms\\DBConnRef",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/libs/rdbms/querybuilder/InsertQueryBuilder.php",
        "line": 213,
        "function": "insert",
        "class": "Wikimedia\\Rdbms\\DBConnRef",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/extensions/CheckUser/src/Services/UserAgentClientHintsManager.php",
        "line": 180,
        "function": "execute",
        "class": "Wikimedia\\Rdbms\\InsertQueryBuilder",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/extensions/CheckUser/src/Services/UserAgentClientHintsManager.php",
        "line": 114,
        "function": "insertMappingRows",
        "class": "MediaWiki\\CheckUser\\Services\\UserAgentClientHintsManager",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/extensions/CheckUser/src/Api/Rest/Handler/UserAgentClientHintsHandler.php",
        "line": 113,
        "function": "insertClientHintValues",
        "class": "MediaWiki\\CheckUser\\Services\\UserAgentClientHintsManager",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/Rest/SimpleHandler.php",
        "line": 38,
        "function": "run",
        "class": "MediaWiki\\CheckUser\\Api\\Rest\\Handler\\UserAgentClientHintsHandler",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/Rest/Router.php",
        "line": 517,
        "function": "execute",
        "class": "MediaWiki\\Rest\\SimpleHandler",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/Rest/Router.php",
        "line": 422,
        "function": "executeHandler",
        "class": "MediaWiki\\Rest\\Router",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/Rest/EntryPoint.php",
        "line": 195,
        "function": "execute",
        "class": "MediaWiki\\Rest\\Router",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/includes/Rest/EntryPoint.php",
        "line": 135,
        "function": "execute",
        "class": "MediaWiki\\Rest\\EntryPoint",
        "type": "->"
      },
      {
        "file": "/home/drw/wikimedia/srv/bare/rest.php",
        "line": 31,
        "function": "main",
        "class": "MediaWiki\\Rest\\EntryPoint",
        "type": "::"
      }
    ]
  },
  "httpCode": 500,
  "httpReason": "Internal Server Error"
}

Perhaps like T344979, we should only record the keys from brands and fullVersionList which we expect to get (brand and version) and ignore or reject anything else.

In T344787#9120241, @dom_walden wrote:
@Dreamy_Jazz I think I can reproduce this, in a slightly different way, although I am not exactly sure how. The JSON payloads:
{"brands": {"-70": "n", "null": -19480, "false": "\\u00887\\u0007", "\\u0016\\ud95c\\udda46BX": true, "m": ""}, "fullVersionList": ["\\ud99f\\udfdb", "\\u00f8\\n\\ud8e2\\udf67\\u001e", null]}
{"brands": {"-70": "n"}, "fullVersionList": ["\\ud99f\\udfdb"]}

I got the warning from T344993 as an exception. This is likely due to different PHP versions. My docker instance seems to be running PHP 8.1.20. PHP docs for the function say that Passing the separator after the array is no longer supported. beyond version 8.

{'bitness': None, 'platformVersion': 29280, 'brands': {-70: 'n', None: -19480, False: '\x887\x07', '\x16\U000671a46BX': True, 'm': ''}, 'platform': '', 'architecture': None, 'fullVersionList': ['\U00077fdb', 'ø\n\U00048b67\x1e', None], 'mobile': 'r', 'model': False}

Running this on the master branch gives me a 400 error to do with a JSON parsing error

What I can see from the error you've shown is that it tries to insert for a row ID 0. This shouldn't occur, so I will make a patch that will not insert a map row if it has an invalid client hints data row ID.

Dreamy_Jazz mentioned this in T345024: When a cu_useragent_clienthints row isn't found, attempts are made to insert to cu_useragent_clienhints_map with uachm_uach_id as 0.Aug 26 2023, 12:48 PM

In T344787#9121455, @Dreamy_Jazz wrote:

What I can see from the error you've shown is that it tries to insert for a row ID 0. This shouldn't occur, so I will make a patch that will not insert a map row if it has an invalid client hints data row ID.

Created as T345024.

dom_walden mentioned this in T345144: If replica is lagged, duplicate entry exception in cu_useragent_clienthints_map.Aug 29 2023, 10:46 AM

ReleaseTaggerBot added a project: MW-1.41-notes (1.41.0-wmf.25; 2023-09-05).Aug 30 2023, 1:02 AM

I no longer see an exception when the client hints data contains duplicate items in the brands or fullVersionList lists.

For example:

{"brands": [{"brand": "foo", "version": "bar"},{"brand": "foo", "version": "bar"},{"brand": "foo", "version": "bar"},{"brand": "foo", "version": "bar"}]}

Test environment: Local docker and bare-metal CheckUser 2.5 (9ed2b65) 19:12, 29 August 2023.

Dreamy_Jazz moved this task from Backlog to Release 0 (Pilot wikis) on the http-client-hints board.Sep 26 2023, 12:00 PM

Dreamy_Jazz edited projects, added http-client-hints (Release 0 (Pilot wikis)); removed http-client-hints.

kostajh closed this task as Resolved.Sep 26 2023, 12:47 PM

[{reqId}] {exception_url} Wikimedia\Rdbms\DBQueryError: Error 1062: Duplicate entry 'X-X-X' for key 'PRIMARY' Function: MediaWiki\CheckUser\Services\UserAgentClientHintsManager::insertMappingRowsClosed, ResolvedPublicPRODUCTION ERRORActions