{"record":{"id":"9bae299aba0f369e","repo":"gastownhall/beads","slug":"creating-idx-wisps-is-blocked-w","errorCode":null,"errorMessage":"creating idx_wisps_is_blocked: %w","messagePattern":"creating idx_wisps_is_blocked: %w","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"critical","filePath":"internal/storage/schema/migration_repairs.go","lineNumber":297,"sourceCode":"\t}\n\tif _, err := db.ExecContext(ctx, \"ALTER TABLE wisps ADD COLUMN is_blocked TINYINT(1) NOT NULL DEFAULT 0\"); err != nil {\n\t\treturn fmt.Errorf(\"adding wisps.is_blocked: %w\", err)\n\t}\n\treturn nil\n}\n\n// ensureWispIsBlockedIndex is ignored/0006's CREATE INDEX statement,\n// translated to Go alongside ensureWispIsBlockedColumn above.\nfunc ensureWispIsBlockedIndex(ctx context.Context, db DBConn) error {\n\thasIndex, err := schemaIndexExists(ctx, db, \"wisps\", \"idx_wisps_is_blocked\")\n\tif err != nil {\n\t\treturn fmt.Errorf(\"checking idx_wisps_is_blocked index: %w\", err)\n\t}\n\tif hasIndex {\n\t\treturn nil\n\t}\n\tif _, err := db.ExecContext(ctx, \"CREATE INDEX idx_wisps_is_blocked ON wisps(is_blocked, status)\"); err != nil {\n\t\treturn fmt.Errorf(\"creating idx_wisps_is_blocked: %w\", err)\n\t}\n\treturn nil\n}\n\n// wispsTableDDLForMigration0047 is 0020_create_wisps.up.sql's shape plus every\n// column a main migration <=52 subsequently adds to wisps: no_history (0023)\n// and started_at (0027). wisps is dolt_ignore'd (never replicated), so\n// creating it here when it is missing cannot fork any synced clone: the\n// table itself is always clone-local by design, and the ignored sequence's\n// own guarded create (ignored/0001: CREATE a __temp__wisps, then RENAME it\n// to wisps only if wisps does not already exist, else DROP the temp table)\n// simply takes the DROP branch once this has run.\nconst wispsTableDDLForMigration0047 = `CREATE TABLE IF NOT EXISTS wisps (\n    id VARCHAR(255) PRIMARY KEY,\n    content_hash VARCHAR(64),\n    title VARCHAR(500) NOT NULL,\n    description TEXT NOT NULL DEFAULT '',\n    design TEXT NOT NULL DEFAULT '',","sourceCodeStart":279,"sourceCodeEnd":315,"githubUrl":"https://github.com/gastownhall/beads/blob/71377f276968b452ee607177637970a4ff888584/internal/storage/schema/migration_repairs.go#L279-L315","documentation":"This wraps failure of `CREATE INDEX idx_wisps_is_blocked ON wisps(is_blocked, status)` after the repair confirmed the index does not exist. This is a real DDL failure: the schema is intentionally being changed and the statement was rejected. Common causes are duplicate-index races (another process created it concurrently), missing INDEX privilege, lock contention, or key-length/limit issues on the indexed columns.","triggerScenarios":"ensureWispIsBlockedIndex ran where idx_wisps_is_blocked was absent and ExecContext CREATE INDEX fails: ER_DUP_KEYNAME from a concurrent creator, missing INDEX/ALTER privilege, DDL lock timeout, or storage-engine limits on composite index size.","commonSituations":"Concurrent tool instances repairing the same clone; running as a user that can write data but not schema; large wisps tables making index builds exceed lock_wait_timeout on older MySQL.","solutions":["Check the wrapped error: ER_DUP_KEYNAME means the index now exists — re-run the repair and it will no-op","Grant INDEX privilege to the migration user","Serialize repair runs so only one process performs DDL","Raise lock_wait_timeout or run the index build during a maintenance window"],"exampleFix":"// before: blind retry loop hammering DDL\nfor { if err := runRepair(db); err == nil { break } }\n// after: re-run once after confirming state\nif err := runRepair(db); err != nil {\n    if existsErr := recheckIndex(db, \"idx_wisps_is_blocked\"); existsErr == nil {\n        return nil // other process created it\n    }\n    return err\n}","handlingStrategy":"try-catch","validationCode":"// check for an equivalent composite index before letting repair build it\nrows, err := db.QueryContext(ctx,\n    \"SELECT index_name FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'wisps' AND column_name IN ('is_blocked','status')\")\nif err == nil {\n    defer rows.Close() // log any existing covering indexes\n}","typeGuard":null,"tryCatchPattern":"if err := ensureWispIsBlockedForRecompute(ctx, db); err != nil {\n    var drv *mysql.MySQLError\n    if errors.As(err, &drv) {\n        switch drv.Number {\n        case 1061: // ER_DUP_KEYNAME — index created concurrently\n            return ensureWispIsBlockedForRecompute(ctx, db) // re-run will no-op\n        case 1205:\n            return fmt.Errorf(\"index build blocked by lock; retry later: %w\", err)\n        case 1071: // key too long\n            return fmt.Errorf(\"engine key-length limit: %w\", err)\n        }\n    }\n    return err\n}","preventionTips":["Serialize DDL repairs across processes","Grant INDEX privilege to the repair user","Treat ER_DUP_KEYNAME as success and re-run the repair to confirm","Build indexes on large tables during low-traffic windows"],"tags":["database","ddl","mysql","index","schema-migration"],"backgroundTag":"create-index-failed","analyzedSha":"71377f276968b452ee607177637970a4ff888584","analyzedAt":"2026-08-30T18:55:39.744Z","schemaVersion":2},"datasetVersion":"2026-08-30T23:17:21.991Z"}