69 lines
4.0 KiB
SQL
69 lines
4.0 KiB
SQL
|
-- Migration: 0014_stuck_objective
|
|||
|
|
-- Date: 2026-06-16
|
|||
|
|
-- Purpose: Persistent, fleet-aggregated STUCK-OBJECTIVE LEDGER (#7 follow-up).
|
|||
|
|
-- The WedgeWatchdog self-remediation layer abandons a bot's current
|
|||
|
|
-- objective when it has been wedged (GoalUnreachable / CombatLoop) past
|
|||
|
|
-- RemediationMs. That abandon is a 5-minute in-memory blacklist (the
|
|||
|
|
-- bot RETRIES automatically once it expires) and, until now, the only
|
|||
|
|
-- record of WHICH content stranded bots was the ephemeral
|
|||
|
|
-- [wedge_remediate] log line (Playerbot.log is truncated every boot).
|
|||
|
|
--
|
|||
|
|
-- This table turns the fleet into a self-documenting content-QA system:
|
|||
|
|
-- every remediation upserts a row keyed by (quest_id, obj_id), so an
|
|||
|
|
-- operator can later run e.g.
|
|||
|
|
-- SELECT quest_id, obj_id, category, hit_count, sample_map,
|
|||
|
|
-- sample_zone, sample_x, sample_y, sample_bot, last_seen
|
|||
|
|
-- FROM playerbot_v2_stuck_objective ORDER BY hit_count DESC LIMIT 50;
|
|||
|
|
-- to find the quests/objectives that strand the most bots and root-cause
|
|||
|
|
-- them (navmesh gap, off-mesh bridge, bad POI data, friendly-only kill
|
|||
|
|
-- target, permanent blacklist, ...). It is the durable, queryable
|
|||
|
|
-- successor to the manual log-forensics + character_queststatus queries
|
|||
|
|
-- used in the 2026-06-15 cross-region investigation (4494 / 876 / 55660).
|
|||
|
|
--
|
|||
|
|
-- Columns:
|
|||
|
|
-- quest_id / obj_id The stranded objective (obj_id 0 = quest-level /
|
|||
|
|
-- no specific sub-objective). Composite PK so each
|
|||
|
|
-- distinct objective is one accumulating row.
|
|||
|
|
-- category Last WedgeCategory that remediated it
|
|||
|
|
-- (GoalUnreachable / CombatLoop).
|
|||
|
|
-- hit_count Cumulative remediation events across the whole
|
|||
|
|
-- fleet AND across restarts (the writer flushes a
|
|||
|
|
-- per-interval delta via hit_count = hit_count +
|
|||
|
|
-- VALUES(hit_count)). A high count = chronically
|
|||
|
|
-- stuck content worth investigating.
|
|||
|
|
-- first_seen When this objective first stranded a bot (set on
|
|||
|
|
-- insert, never overwritten).
|
|||
|
|
-- last_seen Most recent remediation (updated every flush). A
|
|||
|
|
-- stale last_seen ~ the issue resolved itself.
|
|||
|
|
-- sample_* A representative stuck location + bot for the
|
|||
|
|
-- objective, so an investigator can fly there /
|
|||
|
|
-- reproduce without grepping logs.
|
|||
|
|
--
|
|||
|
|
-- Reverts: yes (DROP TABLE).
|
|||
|
|
--
|
|||
|
|
-- Retention: the writer does NOT prune — rows are small, bounded by the number
|
|||
|
|
-- of DISTINCT stuck objectives (hundreds–low thousands), and the long
|
|||
|
|
-- history is the point. Operators may DELETE on last_seen if desired.
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS playerbot_v2_stuck_objective (
|
|||
|
|
quest_id INT UNSIGNED NOT NULL,
|
|||
|
|
obj_id INT UNSIGNED NOT NULL DEFAULT 0,
|
|||
|
|
category VARCHAR(24) NOT NULL DEFAULT '',
|
|||
|
|
hit_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|||
|
|
first_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
last_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
sample_map INT UNSIGNED NOT NULL DEFAULT 0,
|
|||
|
|
sample_zone INT UNSIGNED NOT NULL DEFAULT 0,
|
|||
|
|
sample_x FLOAT NOT NULL DEFAULT 0,
|
|||
|
|
sample_y FLOAT NOT NULL DEFAULT 0,
|
|||
|
|
sample_bot VARCHAR(48) NOT NULL DEFAULT '',
|
|||
|
|
PRIMARY KEY (quest_id, obj_id),
|
|||
|
|
KEY idx_hit_count (hit_count),
|
|||
|
|
KEY idx_last_seen (last_seen)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|||
|
|
|
|||
|
|
-- Record this migration as applied (mirrors the 0011/0012/0013 pattern).
|
|||
|
|
INSERT INTO playerbot_v2_schema_version (version, sha256) VALUES
|
|||
|
|
(14, REPEAT('0', 64))
|
|||
|
|
ON DUPLICATE KEY UPDATE applied_at = CURRENT_TIMESTAMP;
|