-- card_id: SQL-MY-08
-- title: MySQL 8.x 复制接收、应用与延迟状态快照（只读、聚合、脱敏）
-- engine: MySQL
-- versions: 8.0, 8.4
-- risk: R2
-- purpose: 在复制延迟、接收线程或应用线程异常时，形成单行实例级状态快照，供负责人继续核对时间线、链路、资源和复制配置。
-- input: 无；只读取 Performance Schema 的复制连接、应用协调器与应用工作线程状态。
-- output: 已配置通道数、接收/应用开关数量、工作线程数量、错误单位数量、重试计数和配置延迟秒数。
-- permission: 对三个复制状态表的 SELECT；不得为本卡授予业务库、复制控制或管理权限。
-- resource_limit: 单行结果，建议客户端超时 5 秒。
-- validation: 已在 MySQL 8.0/8.4 双节点异步复制隔离场景验证；5.7、组复制、多源、生产及客户环境未验证。
-- failure: 未配置复制时返回 not_configured；缺少表级可见性时由数据库明确拒绝，不得扩大权限绕过。
-- prohibited: 不得输出主机、端口、账号、通道、GTID、binlog 文件/位点、错误文本、SQL 或业务对象；不得停止、启动、重置、改写复制关系、跳日志、写入或改参数。

WITH connection_snapshot AS (
    SELECT
        count(*) AS configured_channels,
        coalesce(sum(service_state = 'ON'), 0) AS receiver_on_channels,
        coalesce(sum(service_state <> 'ON'), 0) AS receiver_off_channels,
        coalesce(sum(last_error_number <> 0), 0) AS receiver_error_channels
    FROM performance_schema.replication_connection_status
),
applier_snapshot AS (
    SELECT
        count(*) AS applier_channels,
        coalesce(sum(service_state = 'ON'), 0) AS applier_on_channels,
        coalesce(sum(service_state <> 'ON'), 0) AS applier_off_channels,
        coalesce(sum(count_transactions_retries), 0) AS transaction_retries,
        max(remaining_delay) AS configured_delay_seconds
    FROM performance_schema.replication_applier_status
),
worker_snapshot AS (
    SELECT
        count(*) AS applier_workers,
        coalesce(sum(service_state = 'ON'), 0) AS applier_workers_on,
        coalesce(sum(last_error_number <> 0), 0) AS applier_error_units
    FROM performance_schema.replication_applier_status_by_worker
)
SELECT
    CASE
        WHEN @@performance_schema = 0 THEN 'performance_schema_disabled'
        WHEN connection_snapshot.configured_channels = 0
          AND applier_snapshot.applier_channels = 0 THEN 'not_configured'
        WHEN connection_snapshot.receiver_off_channels > 0
          OR applier_snapshot.applier_off_channels > 0
          OR connection_snapshot.receiver_error_channels > 0
          OR worker_snapshot.applier_error_units > 0 THEN 'attention'
        WHEN applier_snapshot.configured_delay_seconds > 0 THEN 'configured_delay'
        ELSE 'ready'
    END AS observation_status,
    connection_snapshot.configured_channels,
    connection_snapshot.receiver_on_channels,
    connection_snapshot.receiver_off_channels,
    applier_snapshot.applier_on_channels,
    applier_snapshot.applier_off_channels,
    worker_snapshot.applier_workers,
    worker_snapshot.applier_workers_on,
    connection_snapshot.receiver_error_channels,
    worker_snapshot.applier_error_units,
    applier_snapshot.transaction_retries,
    applier_snapshot.configured_delay_seconds
FROM connection_snapshot
CROSS JOIN applier_snapshot
CROSS JOIN worker_snapshot
LIMIT 1;

