{"record":{"id":"f8ce2104ec8054cf","repo":"mattermost-community/focalboard","slug":"store-addupdatecategoryboard-failed-to-upsert-use","errorCode":null,"errorMessage":"store addUpdateCategoryBoard: failed to upsert user-board-category userID: %s, categoryID: %s, board_count: %d, error: %w","messagePattern":"store addUpdateCategoryBoard: failed to upsert user-board-category userID: (.+?), categoryID: (.+?), board_count: (.+?), error: %w","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"server/services/store/sqlstore/category_boards.go","lineNumber":110,"sourceCode":"\t\t\t0,\n\t\t\tfalse,\n\t\t)\n\t}\n\n\tif s.dbType == model.MysqlDBType {\n\t\tquery = query.Suffix(\n\t\t\t\"ON DUPLICATE KEY UPDATE category_id = ?\",\n\t\t\tcategoryID,\n\t\t)\n\t} else {\n\t\tquery = query.Suffix(\n\t\t\t`ON CONFLICT (user_id, board_id)\n\t\t\t DO UPDATE SET category_id = EXCLUDED.category_id, update_at = EXCLUDED.update_at`,\n\t\t)\n\t}\n\n\tif _, err := query.Exec(); err != nil {\n\t\treturn fmt.Errorf(\n\t\t\t\"store addUpdateCategoryBoard: failed to upsert user-board-category userID: %s, categoryID: %s, board_count: %d, error: %w\",\n\t\t\tuserID, categoryID, len(boardIDs), err,\n\t\t)\n\t}\n\n\treturn nil\n}\n\nfunc (s *SQLStore) categoryBoardsFromRows(rows *sql.Rows) ([]model.CategoryBoardMetadata, error) {\n\tmetadata := []model.CategoryBoardMetadata{}\n\n\tfor rows.Next() {\n\t\tdatum := model.CategoryBoardMetadata{}\n\t\terr := rows.Scan(&datum.BoardID, &datum.Hidden)\n\n\t\tif err != nil {\n\t\t\ts.logger.Error(\"categoryBoardsFromRows row scan error\", mlog.Err(err))\n\t\t\treturn nil, err","sourceCodeStart":92,"sourceCodeEnd":128,"githubUrl":"https://github.com/mattermost-community/focalboard/blob/a84bbb65e32edf972856b329417096ac413518e9/server/services/store/sqlstore/category_boards.go#L92-L128","documentation":"Error returned by SQLStore.addUpdateCategoryBoard (server/services/store/sqlstore/category_boards.go:110) when the upsert into the category_boards table fails at query.Exec(). The store uses INSERT ... ON CONFLICT (user_id, board_id) DO UPDATE to reassign boards to a category; any database-level failure of that statement is wrapped with userID, categoryID, and the number of boards being moved.","triggerScenarios":"Calling AddUpdateCategoryBoard with a batch of boardIDs when the upsert fails: invalid categoryID (foreign-key violation if category was deleted), duplicate entries in boardIDs conflicting on (user_id, board_id) within the same statement, oversized batch, schema mismatch on category_boards, or a DB error (deadlock, connection loss) during Exec.","commonSituations":"Client sends a category update after the category was deleted in another session; batch contains the same board twice (ON CONFLICT DO UPDATE ... cannot affect row a second time in some DBs); migrations left category_boards schema stale; database under heavy write load causing deadlocks.","solutions":["Read the wrapped %w error to get the exact DB cause (FK violation, deadlock, 'ON CONFLICT DO UPDATE command cannot affect row a second time').","Deduplicate boardIDs before calling AddUpdateCategoryBoard.","Verify the categoryID exists in the categories table before the batch upsert.","Retry on transient errors (deadlock/serialization) with backoff; consider chunking large boardID batches.","Run schema migrations so category_boards matches the expected columns and constraints."],"exampleFix":"// before\nboardIDs := []string{\"board-1\", \"board-1\", \"board-2\"}\nerr := store.AddUpdateCategoryBoard(userID, categoryID, boardIDs)\n// after\nseen := map[string]bool{}\nunique := boardIDs[:0]\nfor _, id := range boardIDs {\n\tif !seen[id] {\n\t\tseen[id] = true\n\t\tunique = append(unique, id)\n\t}\n}\nerr := store.AddUpdateCategoryBoard(userID, categoryID, unique)","handlingStrategy":"validation","validationCode":"func validateCategoryUpdate(categoryID string, boardIDs []string) error {\n\tif len(boardIDs) == 0 { return errors.New(\"boardIDs empty\") }\n\tseen := map[string]struct{}{}\n\tfor _, id := range boardIDs {\n\t\tif _, dup := seen[id]; dup { return fmt.Errorf(\"duplicate boardID %s\", id) }\n\t\tseen[id] = struct{}{}\n\t}\n\treturn nil\n}","typeGuard":"func isCategoryUpsertError(err error) bool {\n\treturn err != nil && strings.Contains(err.Error(), \"addUpdateCategoryBoard\")\n}","tryCatchPattern":"err := store.AddUpdateCategoryBoard(userID, categoryID, dedupedBoardIDs)\nif err != nil {\n\tvar pqErr *pq.Error\n\tif errors.As(errors.Unwrap(err), &pqErr) && pqErr.Code.Name() == \"unique_violation\" {\n\t\t// dedupe/re-fetch state and retry once\n\t}\n\treturn err\n}","preventionTips":["Deduplicate boardIDs client-side before batch updates","Verify the category still exists before reassigning boards","Refresh category state after concurrent edits from other sessions","Retry transient DB errors (deadlocks) with exponential backoff"],"tags":["database","sql","upsert","focalboard"],"backgroundTag":"sql-constraint-violation","analyzedSha":"a84bbb65e32edf972856b329417096ac413518e9","analyzedAt":"2026-08-30T09:22:20.720Z","schemaVersion":2},"datasetVersion":"2026-08-30T13:17:10.514Z"}