用 ADB MySQL 分析 OpenClaw Trace：定位 Agent 失败模式与 Token 浪费

SET SESSION group_concat_max_len = 1024 * 1024;

WITH TaskBoundaries AS (
    SELECT
        row_id,
        session_id,
        created_at,
        role,
        content_text,
        tool_name,
        total_tokens,
        stop_reason,
        SUM(CASE WHEN role = 'user' THEN 1 ELSE 0 END)
            OVER (
                PARTITION BY session_id
                ORDER BY row_id
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS chain_id
    FROM openclaw_logs.openclaw_sessions
    WHERE role IS NOT NULL
),
TaskChains AS (
    SELECT
        CONCAT(session_id, '_', chain_id) AS unique_chain_id,
        session_id,
        chain_id,
        MIN(created_at) AS chain_start_at,
        MAX(created_at) AS chain_end_at,
        COUNT(*) AS step_count,
        COUNT(CASE WHEN tool_name IS NOT NULL THEN 1 END) AS tool_usage_count,
        SUM(IFNULL(total_tokens, 0)) AS chain_total_tokens,
        SUBSTRING_INDEX(
            GROUP_CONCAT(IFNULL(stop_reason, '') ORDER BY row_id DESC SEPARATOR ','),
            ',',
            1
        ) AS last_stop_reason,
        GROUP_CONCAT(
            CONCAT(
                '[', role, '] ',
                IFNULL(tool_name, ''),
                CASE WHEN tool_name IS NULL THEN '' ELSE ': ' END,
                LEFT(IFNULL(content_text, ''), 300)
            )
            ORDER BY row_id
            SEPARATOR ' >>> '
        ) AS full_trace
    FROM TaskBoundaries
    WHERE chain_id > 0
    GROUP BY session_id, chain_id
)
SELECT
    unique_chain_id,
    session_id,
    step_count,
    tool_usage_count,
    chain_total_tokens,
    LEFT(full_trace, 200) AS trace_preview
FROM TaskChains
ORDER BY chain_start_at
LIMIT 5;

CREATE TABLE IF NOT EXISTS openclaw_logs.t_ai_audit_results (
    unique_chain_id VARCHAR(256),
    session_id VARCHAR(256),
    chain_id BIGINT,
    failure_label VARCHAR(128),
    root_cause_notes TEXT,
    created_at DATETIME
);

INSERT INTO openclaw_logs.t_ai_audit_results
WITH TaskBoundaries AS (
    SELECT
        row_id,
        session_id,
        created_at,
        role,
        content_text,
        tool_name,
        total_tokens,
        stop_reason,
        SUM(CASE WHEN role = 'user' THEN 1 ELSE 0 END)
            OVER (
                PARTITION BY session_id
                ORDER BY row_id
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS chain_id
    FROM openclaw_logs.openclaw_sessions
    WHERE role IS NOT NULL
),
TaskChains AS (
    SELECT
        CONCAT(session_id, '_', chain_id) AS unique_chain_id,
        session_id,
        chain_id,
        COUNT(CASE WHEN tool_name IS NOT NULL THEN 1 END) AS tool_usage_count,
        SUBSTRING_INDEX(
            GROUP_CONCAT(IFNULL(stop_reason, '') ORDER BY row_id DESC SEPARATOR ','),
            ',',
            1
        ) AS last_stop_reason,
        GROUP_CONCAT(
            CONCAT(
                '[', role, '] ',
                IFNULL(tool_name, ''),
                CASE WHEN tool_name IS NULL THEN '' ELSE ': ' END,
                LEFT(IFNULL(content_text, ''), 300)
            )
            ORDER BY row_id
            SEPARATOR ' >>> '
        ) AS full_trace
    FROM TaskBoundaries
    WHERE chain_id > 0
    GROUP BY session_id, chain_id
)
SELECT
    unique_chain_id,
    session_id,
    chain_id,
    ai_classify(
        'qwen_max_test',
        LEFT(full_trace, 600),
        '["死循环", "工具参数幻觉", "拒绝执行", "逻辑断裂", "成功解决"]'
    ) AS failure_label,
    ai_generate(
        'qwen_max_test',
        CONCAT(
            '你是 Agent Trace 诊断助手。请分析下面的任务链，输出失败根因。',
            '要求：只关注可从 Trace 中观察到的证据；如果是工具问题，请说明工具名、参数或返回值异常；',
            '如果没有明显失败，请说明任务为什么可以视为成功。任务链：',
            LEFT(full_trace, 400)
        )
    ) AS root_cause_notes,
    NOW() AS created_at
FROM TaskChains
WHERE
    tool_usage_count > 0
    OR last_stop_reason IS NULL
    OR last_stop_reason <> 'stop';

WITH TaskBoundaries AS (
    SELECT
        row_id,
        session_id,
        role,
        total_tokens,
        SUM(CASE WHEN role = 'user' THEN 1 ELSE 0 END)
            OVER (
                PARTITION BY session_id
                ORDER BY row_id
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS chain_id
    FROM openclaw_logs.openclaw_sessions
    WHERE role IS NOT NULL
),
ChainTokens AS (
    SELECT
        CONCAT(session_id, '_', chain_id) AS unique_chain_id,
        COUNT(*) AS step_count,
        SUM(IFNULL(total_tokens, 0)) AS chain_total_tokens
    FROM TaskBoundaries
    WHERE chain_id > 0
    GROUP BY session_id, chain_id
)
SELECT
    a.failure_label,
    COUNT(*) AS task_count,
    ROUND(AVG(ct.chain_total_tokens)) AS avg_tokens,
    SUM(ct.chain_total_tokens) AS total_tokens_burned,
    MAX(ct.chain_total_tokens) AS max_chain_tokens
FROM openclaw_logs.t_ai_audit_results a
JOIN ChainTokens ct
    ON a.unique_chain_id = ct.unique_chain_id
GROUP BY a.failure_label
ORDER BY total_tokens_burned DESC;

WITH TaskBoundaries AS (
    SELECT
        row_id,
        session_id,
        role,
        total_tokens,
        SUM(CASE WHEN role = 'user' THEN 1 ELSE 0 END)
            OVER (
                PARTITION BY session_id
                ORDER BY row_id
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS chain_id
    FROM openclaw_logs.openclaw_sessions
    WHERE role IS NOT NULL
),
ChainStats AS (
    SELECT
        CONCAT(session_id, '_', chain_id) AS unique_chain_id,
        COUNT(*) AS step_count,
        SUM(IFNULL(total_tokens, 0)) AS chain_total_tokens
    FROM TaskBoundaries
    WHERE chain_id > 0
    GROUP BY session_id, chain_id
)
SELECT
    a.unique_chain_id,
    a.failure_label,
    cs.step_count,
    cs.chain_total_tokens,
    ROUND(cs.chain_total_tokens / NULLIF(cs.step_count, 0), 2) AS tokens_per_step,
    LEFT(a.root_cause_notes, 300) AS root_cause_preview
FROM openclaw_logs.t_ai_audit_results a
JOIN ChainStats cs
    ON a.unique_chain_id = cs.unique_chain_id
WHERE a.failure_label <> '成功解决'
ORDER BY tokens_per_step DESC
LIMIT 20;

WITH TaskBoundaries AS (
    SELECT
        row_id,
        session_id,
        created_at,
        role,
        content_text,
        total_tokens,
        SUM(CASE WHEN role = 'user' THEN 1 ELSE 0 END)
            OVER (
                PARTITION BY session_id
                ORDER BY row_id
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS chain_id
    FROM openclaw_logs.openclaw_sessions
    WHERE role IS NOT NULL
),
ChainTokens AS (
    SELECT
        CONCAT(session_id, '_', chain_id) AS unique_chain_id,
        SUM(IFNULL(total_tokens, 0)) AS chain_total_tokens
    FROM TaskBoundaries
    WHERE chain_id > 0
    GROUP BY session_id, chain_id
),
FirstUserMsg AS (
    SELECT
        unique_chain_id,
        content_text AS original_prompt
    FROM (
        SELECT
            CONCAT(session_id, '_', chain_id) AS unique_chain_id,
            content_text,
            ROW_NUMBER() OVER (
                PARTITION BY session_id, chain_id
                ORDER BY row_id
            ) AS rn
        FROM TaskBoundaries
        WHERE role = 'user' AND chain_id > 0
    ) t
    WHERE rn = 1
),
LatestFailedChains AS (
    SELECT
        unique_chain_id,
        failure_label,
        root_cause_notes
    FROM (
        SELECT
            unique_chain_id,
            failure_label,
            root_cause_notes,
            ROW_NUMBER() OVER (
                PARTITION BY unique_chain_id
                ORDER BY created_at DESC
            ) AS rn
        FROM openclaw_logs.t_ai_audit_results
        WHERE failure_label <> '成功解决'
    ) t
    WHERE rn = 1
)
SELECT
    fc.unique_chain_id,
    fc.failure_label,
    LEFT(fu.original_prompt, 200) AS original_prompt_preview,
    fc.root_cause_notes AS root_cause,
    ai_generate(
        'qwen_max_test',
        CONCAT(
            '你是 Prompt 优化助手。请基于失败根因改写用户指令。',
            '要求：1. 明确工具参数边界；2. 增加失败退出条件；',
            '3. 要求模型在调用工具前检查必要字段；4. 不改变用户真实目标。',
            '原始指令：', LEFT(fu.original_prompt, 500),
            '失败标签：', fc.failure_label,
            '失败根因：', fc.root_cause_notes
        )
    ) AS optimized_prompt
FROM LatestFailedChains fc
JOIN ChainTokens ct
    ON fc.unique_chain_id = ct.unique_chain_id
JOIN FirstUserMsg fu
    ON fc.unique_chain_id = fu.unique_chain_id
ORDER BY ct.chain_total_tokens DESC
LIMIT 3;

调用工具前必须检查：
1. 必填参数是否存在；
2. 参数值是否来自用户输入、工具返回或已知上下文；
3. 不允许编造文件路径、API Key、资源 ID；
4. 如果缺少必要参数，必须停止工具调用并向用户请求补充信息；
5. 同一个工具连续失败 2 次后，不得继续重试，应输出失败原因。

SET SESSION group_concat_max_len = 1024 * 1024;