-- card_id: SQL-MY-07
-- title: MySQL 8.x 连接错误与拒绝计数快照（只读、聚合、脱敏）
-- engine: MySQL
-- versions: 8.0, 8.4
-- risk: R2
-- purpose: 在 Too many connections、aborted connections 或连接建立错误增加时，形成单行实例级计数快照，供负责人继续核对网络、认证、DNS、证书、内存、应用连接池和时间线。
-- input: 无；只读取 performance_schema 全局状态与只读全局变量。
-- output: 连接上限、当前/运行连接、历史峰值、累计连接、上限拒绝、aborted clients/connects 及全局连接错误计数。
-- permission: MySQL 8.0/8.4 默认可读取该聚合状态的普通已认证会话；隔离测试不授予额外业务库或管理权限。
-- resource_limit: 单行结果，建议客户端超时 5 秒。
-- validation: 已在 MySQL 8.0/8.4 隔离连接、错误认证、异常断开和上限拒绝场景验证；5.7、生产及客户环境未验证。
-- failure: performance_schema 未启用时返回明确状态行，状态缺失时返回 status_unavailable；不得为获得更多明细扩大权限。
-- prohibited: 不得输出 SQL、库表、账号、主机、地址、连接串或会话身份；不得 KILL、SET、FLUSH、开启事务、提交、回滚、修改参数或扩大权限。

WITH status_snapshot AS (
    SELECT
        MAX(CASE WHEN VARIABLE_NAME = 'Threads_connected'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS threads_connected,
        MAX(CASE WHEN VARIABLE_NAME = 'Threads_running'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS threads_running,
        MAX(CASE WHEN VARIABLE_NAME = 'Max_used_connections'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS max_used_connections,
        MAX(CASE WHEN VARIABLE_NAME = 'Connections'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS connections_total,
        MAX(CASE WHEN VARIABLE_NAME = 'Connection_errors_max_connections'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS connection_errors_max_connections,
        MAX(CASE WHEN VARIABLE_NAME = 'Aborted_connects'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS aborted_connects,
        MAX(CASE WHEN VARIABLE_NAME = 'Aborted_clients'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS aborted_clients,
        MAX(CASE WHEN VARIABLE_NAME = 'Connection_errors_internal'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS connection_errors_internal,
        MAX(CASE WHEN VARIABLE_NAME = 'Connection_errors_peer_address'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS connection_errors_peer_address,
        MAX(CASE WHEN VARIABLE_NAME = 'Connection_errors_select'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS connection_errors_select,
        MAX(CASE WHEN VARIABLE_NAME = 'Connection_errors_tcpwrap'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS connection_errors_tcpwrap
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME IN (
        'Threads_connected',
        'Threads_running',
        'Max_used_connections',
        'Connections',
        'Connection_errors_max_connections',
        'Aborted_connects',
        'Aborted_clients',
        'Connection_errors_internal',
        'Connection_errors_peer_address',
        'Connection_errors_select',
        'Connection_errors_tcpwrap'
    )
),
snapshot AS (
    SELECT
        CAST(@@GLOBAL.max_connections AS UNSIGNED) AS max_connections,
        status_snapshot.*
    FROM status_snapshot
)
SELECT
    CASE
        WHEN @@performance_schema = 0 THEN 'performance_schema_disabled'
        WHEN threads_connected IS NULL THEN 'status_unavailable'
        ELSE 'ready'
    END AS observation_status,
    max_connections,
    threads_connected,
    threads_running,
    max_used_connections,
    connections_total,
    CASE
        WHEN threads_connected IS NULL THEN NULL
        ELSE GREATEST(max_connections - threads_connected, 0)
    END AS approximate_connection_headroom,
    connection_errors_max_connections,
    aborted_connects,
    aborted_clients,
    connection_errors_internal,
    connection_errors_peer_address,
    connection_errors_select,
    connection_errors_tcpwrap
FROM snapshot
LIMIT 1;
