Skip to content
Python
25.5 KB
Raw Blame
"""
Agent Memory V2 - MysqlCache

基于 aiomysql 的 MySQL 审计/观测落库后端。
适用于标准 MySQL 实例,支持多实例共享状态。

设计模式:
- aiomysql 原生 async(无需线程池包装)
- 连接池 aiomysql.create_pool
- autocommit=True

三张表(与 SQLite 版本一一对应):
- memory_operations: 知识库变动日志
- pipeline_logs:     LLM 调用链中间结果
- system_metrics:    分钟粒度系统指标
"""

import json
import logging
import os
from datetime import datetime
from typing import Optional, List, Dict, Any

from .cache_base import CacheBase
from ..config import MemoryConfig

logger = logging.getLogger(__name__)

# DDL (MySQL syntax)
_CREATE_TABLES_SQL = [
    """
    CREATE TABLE IF NOT EXISTS memory_operations_v2 (
        id            BIGINT AUTO_INCREMENT,
        request_id    VARCHAR(128) NOT NULL,
        user_id       VARCHAR(128) NOT NULL,
        agent_id      VARCHAR(128) NOT NULL DEFAULT '',
        op            VARCHAR(32) NOT NULL,
        memory_id     VARCHAR(128) NOT NULL,
        old_memory_id VARCHAR(128) DEFAULT NULL,
        content       LONGTEXT NOT NULL,
        layer         VARCHAR(32) NOT NULL DEFAULT '',
        reason        TEXT NOT NULL,
        supersedes    TEXT NOT NULL,
        created_at    VARCHAR(64) NOT NULL,
        created_date  DATE NOT NULL,
        PRIMARY KEY (id, created_date),
        INDEX idx_memop_request (request_id),
        INDEX idx_memop_memory (memory_id),
        INDEX idx_memop_user (user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    PARTITION BY RANGE (TO_DAYS(created_date)) (
        PARTITION pmax VALUES LESS THAN MAXVALUE
    )
    """,
    """
    CREATE TABLE IF NOT EXISTS pipeline_logs_v2 (
        id                BIGINT AUTO_INCREMENT,
        request_id        VARCHAR(128) NOT NULL,
        namespace         VARCHAR(128) NOT NULL DEFAULT '',
        user_id           VARCHAR(128) NOT NULL,
        agent_id          VARCHAR(128) NOT NULL DEFAULT '',
        session_id        VARCHAR(128) NOT NULL DEFAULT '',
        step              VARCHAR(64) NOT NULL,
        prompt            LONGTEXT NOT NULL,
        response          LONGTEXT NOT NULL,
        parsed            LONGTEXT NOT NULL,
        memory_ids        TEXT NOT NULL,
        elapsed_ms        DOUBLE NOT NULL DEFAULT 0,
        prompt_tokens     INT NOT NULL DEFAULT 0,
        completion_tokens INT NOT NULL DEFAULT 0,
        total_tokens      INT NOT NULL DEFAULT 0,
        event_at_ms       BIGINT NOT NULL DEFAULT 0,
        created_at        VARCHAR(64) NOT NULL,
        created_date      DATE NOT NULL,
        PRIMARY KEY (id, created_date),
        INDEX idx_plog_request (request_id),
        INDEX idx_plog_user (user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    PARTITION BY RANGE (TO_DAYS(created_date)) (
        PARTITION pmax VALUES LESS THAN MAXVALUE
    )
    """,
    """
    CREATE TABLE IF NOT EXISTS write_outcomes (
        request_id VARCHAR(256) NOT NULL,
        user_id    VARCHAR(255) NOT NULL,
        agent_id   VARCHAR(255) NOT NULL DEFAULT '',
        session_id VARCHAR(255) NOT NULL DEFAULT '',
        operation  VARCHAR(32) NOT NULL,
        status     VARCHAR(32) NOT NULL,
        result_json LONGTEXT NOT NULL,
        created_at VARCHAR(64) NOT NULL,
        updated_at VARCHAR(64) NOT NULL,
        PRIMARY KEY (request_id),
        KEY idx_write_outcomes_owner (user_id, agent_id, session_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """,
    """
    CREATE TABLE IF NOT EXISTS system_metrics (
        minute_ts   VARCHAR(32) NOT NULL,
        data        LONGTEXT NOT NULL,
        created_at  VARCHAR(64) NOT NULL,
        PRIMARY KEY (minute_ts)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """,
    # Episode(内部会话)状态:跨 write 批次把"一件事"归到同一个 episode_id。
    # 缺了这张表,get_episode_state 永远返回 None → 每次写入都开新 episode,
    # 划分等于没生效(线上实测 8798 次决策全是 new)。
    """
    CREATE TABLE IF NOT EXISTS episode_state (
        episode_key    VARCHAR(255) NOT NULL,
        user_id        VARCHAR(255) NOT NULL DEFAULT '',
        agent_id       VARCHAR(255) NOT NULL DEFAULT '',
        session_id     VARCHAR(255) NOT NULL DEFAULT '',
        episode_id     VARCHAR(64)  NOT NULL DEFAULT '',
        last_write_ts  DOUBLE       NOT NULL DEFAULT 0,
        memory_count   INT          NOT NULL DEFAULT 0,
        updated_at     VARCHAR(64)  NOT NULL,
        PRIMARY KEY (episode_key),
        KEY idx_episode_user (user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """,
]


class MysqlCache(CacheBase):
    """
    MySQL 审计/观测落库后端(memory_operations / pipeline_logs / system_metrics)。

    基于 aiomysql 的原生异步 MySQL 后端。
    """

    def __init__(self, config: MemoryConfig):
        self.config = config
        self._pool = None  # aiomysql.Pool

        # 读取 MySQL 连接参数
        cache_cfg = getattr(config, "cache", None)
        self._host = getattr(cache_cfg, "mysql_host", None) or os.getenv("MEMORY_MYSQL_HOST", "localhost")
        self._port = getattr(cache_cfg, "mysql_port", None) or int(os.getenv("MEMORY_MYSQL_PORT", "3306"))
        self._user = getattr(cache_cfg, "mysql_user", None) or os.getenv("MEMORY_MYSQL_USER", "root")
        self._password = getattr(cache_cfg, "mysql_password", None) or os.getenv("MEMORY_MYSQL_PASSWORD", "")
        self._database = getattr(cache_cfg, "mysql_database", None) or os.getenv("MEMORY_MYSQL_DATABASE", "hy_memory")
        self._pool_size = getattr(cache_cfg, "mysql_pool_size", None) or int(os.getenv("MEMORY_MYSQL_POOL_SIZE", "10"))
        self._pool_recycle = getattr(cache_cfg, "mysql_pool_recycle", None) or int(os.getenv("MEMORY_MYSQL_POOL_RECYCLE", "3600"))

    # ================================================================
    # 生命周期
    # ================================================================

    async def initialize(self) -> None:
        """立即创建 pool(必须在正确的 event loop 上调用,即 _LoopThread 的 loop)"""
        import aiomysql
        self._pool = await aiomysql.create_pool(
            host=self._host,
            port=self._port,
            user=self._user,
            password=self._password,
            db=self._database,
            minsize=2,
            maxsize=self._pool_size,
            pool_recycle=self._pool_recycle,
            autocommit=True,
            charset='utf8mb4',
        )
        await self._ensure_tables()
        logger.info(
            f"MysqlCache initialized: {self._user}@{self._host}:{self._port}/{self._database} "
            f"pool_size={self._pool_size}"
        )

    async def _ensure_tables(self) -> None:
        """确保所有表存在"""
        for ddl in _CREATE_TABLES_SQL:
            await self._execute(ddl)
        await self._migrate_pipeline_logs_columns()

    async def _migrate_pipeline_logs_columns(self) -> None:
        """给旧库的 pipeline_logs_v2 补 namespace/session/event time 列。

        用 AFTER 指定列位置,与 CREATE TABLE 一致:
        namespace 紧跟 request_id、session_id 紧跟 agent_id。
        """
        migrations = (
            ("namespace", "ALTER TABLE pipeline_logs_v2 ADD COLUMN namespace "
                       "VARCHAR(128) NOT NULL DEFAULT '' AFTER request_id"),
            ("session_id", "ALTER TABLE pipeline_logs_v2 ADD COLUMN session_id "
                           "VARCHAR(128) NOT NULL DEFAULT '' AFTER agent_id"),
            ("event_at_ms", "ALTER TABLE pipeline_logs_v2 ADD COLUMN event_at_ms "
                            "BIGINT NOT NULL DEFAULT 0 AFTER total_tokens"),
        )
        for col, ddl in migrations:
            try:
                await self._execute(ddl)
            except Exception as e:
                # 已存在 (Duplicate column) 等 → 忽略
                logger.debug(f"[mysql] add column {col} skipped: {e}")

    async def close(self) -> None:
        if self._pool is not None:
            self._pool.close()
            await self._pool.wait_closed()
            self._pool = None
        logger.info("MysqlCache closed")

    # ================================================================
    # 内部 DB helpers
    # ================================================================

    async def _execute(self, sql: str, args=None) -> int:
        """执行写操作,返回 affected rows"""
        async with self._pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(sql, args)
                return cur.rowcount

    async def _fetchone(self, sql: str, args=None) -> Optional[Dict[str, Any]]:
        """查询单行,返回 dict 或 None"""
        import aiomysql
        async with self._pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                await cur.execute(sql, args)
                row = await cur.fetchone()
                # 防御:极端情况下 DictCursor 可能返回 tuple
                if row is not None and not isinstance(row, dict):
                    cols = [d[0] for d in cur.description] if cur.description else []
                    row = dict(zip(cols, row))
                return row

    async def _fetchall(self, sql: str, args=None) -> List[Dict[str, Any]]:
        """查询多行,返回 list of dict"""
        import aiomysql
        async with self._pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                await cur.execute(sql, args)
                rows = await cur.fetchall()
                # 防御:确保返回 list of dict
                if rows and not isinstance(rows[0], dict):
                    cols = [d[0] for d in cur.description] if cur.description else []
                    rows = [dict(zip(cols, r)) for r in rows]
                return rows

    # ================================================================
    # 统计
    # ================================================================

    async def get_stats(self) -> Dict[str, Any]:
        return {
            "backend": "mysql",
            "host": self._host,
            "database": self._database,
        }

    # ================================================================
    # Memory Operations Log
    # ================================================================

    async def store_memory_operation(
        self,
        request_id: str,
        user_id: str,
        agent_id: str,
        op: str,
        memory_id: str,
        content: str,
        layer: str = "",
        old_memory_id: Optional[str] = None,
        reason: str = "",
        supersedes: Optional[List[str]] = None,
    ) -> bool:
        from ..utils.pipeline_observability import is_memory_operations_enabled
        if not is_memory_operations_enabled():
            return True
        try:
            now = datetime.now()
            created_at = now.isoformat()
            created_date = now.strftime("%Y-%m-%d")
            supersedes_str = json.dumps(supersedes or [], ensure_ascii=False)
            await self._execute(
                """INSERT INTO memory_operations_v2
                   (request_id, user_id, agent_id, op, memory_id, old_memory_id,
                    content, layer, reason, supersedes, created_at, created_date)
                   VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
                (request_id, user_id, agent_id, op, memory_id,
                 old_memory_id, content, layer, reason, supersedes_str,
                 created_at, created_date),
            )
            return True
        except Exception as e:
            logger.warning(f"store_memory_operation failed: {e}")
            return False

    async def get_memory_operations(
        self,
        request_id: Optional[str] = None,
        memory_id: Optional[str] = None,
        user_id: Optional[str] = None,
        limit: int = 100,
    ) -> List[Dict[str, Any]]:
        try:
            conditions = []
            params = []
            if request_id:
                conditions.append("request_id = %s")
                params.append(request_id)
            if memory_id:
                conditions.append("memory_id = %s")
                params.append(memory_id)
            if user_id:
                conditions.append("user_id = %s")
                params.append(user_id)

            if not conditions:
                return []

            where = " AND ".join(conditions)
            rows = await self._fetchall(
                f"SELECT * FROM memory_operations_v2 WHERE {where} "
                f"ORDER BY id DESC LIMIT %s",
                params + [limit],
            )
            # 反序列化 supersedes
            for row in rows:
                raw_sup = row.get("supersedes", "")
                try:
                    row["supersedes"] = json.loads(raw_sup) if raw_sup else []
                except Exception:
                    row["supersedes"] = []
            return rows
        except Exception as e:
            logger.warning(f"get_memory_operations failed: {e}")
            return []

    # ================================================================
    # Pipeline Logs
    # ================================================================

    # ── Episode(内部会话)状态 ──

    async def get_episode_state(self, episode_key: str) -> Optional[Dict[str, Any]]:
        try:
            row = await self._fetchone(
                "SELECT episode_id, last_write_ts, memory_count "
                "FROM episode_state WHERE episode_key = %s",
                (episode_key,),
            )
            if not row:
                return None
            return {
                "episode_id": row.get("episode_id") or "",
                "last_write_ts": float(row.get("last_write_ts") or 0),
                "memory_count": int(row.get("memory_count") or 0),
            }
        except Exception as e:
            logger.warning(f"get_episode_state failed: {e}")
            return None

    async def upsert_episode_state(
        self,
        episode_key: str,
        *,
        user_id: str,
        agent_id: str,
        session_id: str,
        episode_id: str,
        last_write_ts: float,
        memory_count: int,
    ) -> bool:
        try:
            from datetime import datetime as _dt

            await self._execute(
                """
                INSERT INTO episode_state (
                    episode_key, user_id, agent_id, session_id,
                    episode_id, last_write_ts, memory_count, updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    episode_id = VALUES(episode_id),
                    last_write_ts = VALUES(last_write_ts),
                    memory_count = VALUES(memory_count),
                    updated_at = VALUES(updated_at)
                """,
                (episode_key, user_id, agent_id, session_id, episode_id,
                 float(last_write_ts), int(memory_count), _dt.now().isoformat()),
            )
            return True
        except Exception as e:
            logger.warning(f"upsert_episode_state failed: {e}")
            return False

    async def store_pipeline_log(
        self,
        request_id: str,
        user_id: str,
        agent_id: str,
        step: str,
        prompt: str,
        response: str,
        parsed: str = "",
        memory_ids: Optional[List[str]] = None,
        elapsed_ms: float = 0,
        prompt_tokens: int = 0,
        completion_tokens: int = 0,
        total_tokens: int = 0,
        session_id: str = "",
        event_at_ms: int = 0,
    ) -> bool:
        try:
            from ..utils.pipeline_observability import split_namespace_user
            now = datetime.now()
            created_at = now.isoformat()
            created_date = now.strftime("%Y-%m-%d")
            event_at_ms = int(event_at_ms) if event_at_ms else int(now.timestamp() * 1000)
            mem_ids_json = json.dumps(memory_ids or [])
            namespace, uid = split_namespace_user(user_id)
            await self._execute(
                """INSERT INTO pipeline_logs_v2
                   (request_id, namespace, user_id, agent_id, session_id,
                    step, prompt, response,
                    parsed, memory_ids, elapsed_ms,
                    prompt_tokens, completion_tokens, total_tokens,
                    event_at_ms, created_at, created_date)
                   VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
                (request_id, namespace, uid, agent_id, session_id or "",
                 step, prompt, response,
                 parsed, mem_ids_json, elapsed_ms,
                 prompt_tokens, completion_tokens, total_tokens,
                 event_at_ms, created_at, created_date),
            )
            return True
        except Exception as e:
            logger.warning(f"store_pipeline_log failed: {e}")
            return False

    async def get_pipeline_logs(
        self,
        request_id: Optional[str] = None,
        user_id: Optional[str] = None,
        step: Optional[str] = None,
        limit: int = 100,
    ) -> List[Dict[str, Any]]:
        try:
            conditions = []
            params = []
            if request_id:
                conditions.append("request_id = %s")
                params.append(request_id)
            if user_id:
                conditions.append("user_id = %s")
                params.append(user_id)
            if step:
                conditions.append("step = %s")
                params.append(step)

            if not conditions:
                return []

            where = " AND ".join(conditions)
            rows = await self._fetchall(
                f"SELECT * FROM pipeline_logs_v2 WHERE {where} "
                f"ORDER BY id ASC LIMIT %s",
                params + [limit],
            )
            # 派生可读的 event_at(ISO ms)供查询接口直接用;event_at_ms 为 0 时留空
            for _r in rows:
                _ms = _r.get("event_at_ms") or 0
                _r["event_at"] = (
                    datetime.fromtimestamp(_ms / 1000.0).isoformat(timespec="milliseconds")
                    if _ms else ""
                )
            return rows
        except Exception as e:
            logger.warning(f"get_pipeline_logs failed: {e}")
            return []

    async def claim_write_outcome(
        self,
        request_id: str,
        user_id: str,
        agent_id: str,
        session_id: str,
        operation: str,
    ) -> Optional[Dict[str, Any]]:
        now = datetime.now().isoformat()
        inserted = await self._execute(
            """
            INSERT IGNORE INTO write_outcomes (
                request_id, user_id, agent_id, session_id, operation,
                status, result_json, created_at, updated_at
            ) VALUES (%s, %s, %s, %s, %s, 'running', '{}', %s, %s)
            """,
            (request_id, user_id, agent_id, session_id, operation, now, now),
        )
        if inserted == 1:
            return None
        return await self.get_write_outcome(request_id)

    async def finish_write_outcome(
        self,
        request_id: str,
        status: str,
        result: Dict[str, Any],
    ) -> bool:
        updated = await self._execute(
            """
            UPDATE write_outcomes
            SET status = %s, result_json = %s, updated_at = %s
            WHERE request_id = %s
            """,
            (
                status,
                json.dumps(result, ensure_ascii=False, default=str),
                datetime.now().isoformat(),
                request_id,
            ),
        )
        return updated == 1

    async def get_write_outcome(self, request_id: str) -> Optional[Dict[str, Any]]:
        row = await self._fetchone(
            "SELECT * FROM write_outcomes WHERE request_id = %s",
            (request_id,),
        )
        if row is None:
            return None
        try:
            row["result"] = json.loads(row.pop("result_json") or "{}")
        except (TypeError, json.JSONDecodeError):
            row["result"] = {}
        return row

    # ================================================================
    # System Metrics(分钟粒度落盘)
    # ================================================================

    async def store_metrics_minute(self, minute_ts: str, data: dict) -> None:
        import aiomysql

        try:
            now = datetime.now().isoformat()
            # Read-merge-write must be one transaction: two instances flushing
            # the same minute used to overwrite each other's counters, and a
            # concurrent insert could lose the whole batch to a duplicate key.
            for attempt in range(2):
                try:
                    async with self._pool.acquire() as conn:
                        await conn.begin()
                        try:
                            async with conn.cursor(aiomysql.DictCursor) as cur:
                                await cur.execute(
                                    "SELECT data FROM system_metrics "
                                    "WHERE minute_ts = %s FOR UPDATE",
                                    (minute_ts,),
                                )
                                row = await cur.fetchone()
                                if row is not None:
                                    old_raw = (
                                        row["data"]
                                        if isinstance(row, dict)
                                        else row[0]
                                    )
                                    merged = self._merge_metric_buckets(
                                        json.loads(old_raw), data,
                                    )
                                    await cur.execute(
                                        "UPDATE system_metrics SET data = %s, "
                                        "created_at = %s WHERE minute_ts = %s",
                                        (
                                            json.dumps(merged, default=str),
                                            now,
                                            minute_ts,
                                        ),
                                    )
                                else:
                                    await cur.execute(
                                        "INSERT INTO system_metrics "
                                        "(minute_ts, data, created_at) "
                                        "VALUES (%s, %s, %s)",
                                        (
                                            minute_ts,
                                            json.dumps(data, default=str),
                                            now,
                                        ),
                                    )
                            await conn.commit()
                            return
                        except Exception:
                            await conn.rollback()
                            raise
                except (aiomysql.IntegrityError, aiomysql.OperationalError) as error:
                    code = error.args[0] if error.args else None
                    deadlock = code in (1205, 1213)
                    if attempt or (isinstance(error, aiomysql.OperationalError) and not deadlock):
                        raise
                    # A competing writer inserted the row, or InnoDB chose us
                    # as the deadlock victim. Retry so this batch merges.
                    continue
        except Exception as e:
            logger.warning(f"store_metrics_minute failed: {e}")

    async def load_metrics_range(self, start_ts: str, end_ts: str) -> list:
        try:
            rows = await self._fetchall(
                "SELECT data FROM system_metrics WHERE minute_ts >= %s AND minute_ts <= %s ORDER BY minute_ts",
                (start_ts, end_ts),
            )
            results = []
            for row in rows:
                try:
                    results.append(json.loads(row["data"]))
                except (json.JSONDecodeError, TypeError):
                    pass
            return results
        except Exception as e:
            logger.warning(f"load_metrics_range failed: {e}")
            return []

    async def cleanup_old_metrics(self, before_ts: str) -> None:
        try:
            await self._execute(
                "DELETE FROM system_metrics WHERE minute_ts < %s", (before_ts,)
            )
        except Exception as e:
            logger.warning(f"cleanup_old_metrics failed: {e}")

    # ================================================================
    # Summary Buffer
    # ================================================================




    @staticmethod
    def _merge_metric_buckets(old: dict, new: dict) -> dict:
        """合并两个 metric bucket(同一分钟多次 flush)"""
        merged = dict(old)
        for key in ("sys1_started", "sys1_completed", "sys1_failed",
                    "sys2_started", "sys2_completed", "sys2_failed",
                    "vdb_ops", "graph_ops"):
            merged[key] = merged.get(key, 0) + new.get(key, 0)
        for key in ("vdb_ops_sum_ms", "graph_ops_sum_ms"):
            merged[key] = merged.get(key, 0) + new.get(key, 0)
        # timing sums
        for bucket_key in ("sys1_timing_sums", "sys2_timing_sums"):
            old_sums = merged.get(bucket_key, {})
            new_sums = new.get(bucket_key, {})
            for k, v in new_sums.items():
                old_sums[k] = old_sums.get(k, 0) + v
            merged[bucket_key] = old_sums
        return merged