执行特定查询时遇到连接中断

我在对 AWS 实例上托管的 MySQL 进行查询期间收到 2013 Lost Connection to MySQL server 的消息。下面的查询是唯一导致此错误的查询（其他查询运行正常），并且此查询在运行 MySQL 的 Synology Docker 容器上运行也没有问题。我发现的唯一独特之处是该查询使用 CTE，而其他成功运行的查询则没有。 AWS MySQL 是 8.0.23，NAS Docker MySQL 是 8.0.28。我已经检查了第一件事，例如最大连接数、超时等，并且 AWS 实例使用的值与 NAS Docker 实例上的设置相同或更高。我还尝试过使用较小的数据表并重新组织数据表以消除数据损坏的可能性。我已经搜索了几天，但无法找到有关问题所在的任何提示。这里有人对我接下来应该去哪里有什么建议吗？谢谢！

USE ce_test;
SET @lowlim = 0;
SET @upplim = 0;
with orderedList AS (
SELECT
    576_VMC_Sol_Savings_Pct,
    ROW_NUMBER() OVER (ORDER BY 576_VMC_Sol_Savings_Pct) AS row_n
FROM vmctco
),

quartile_breaks AS (
SELECT
    576_VMC_Sol_Savings_Pct,
    (
    SELECT 576_VMC_Sol_Savings_Pct AS quartile_break
    FROM orderedList
    WHERE row_n = FLOOR((SELECT COUNT(*) FROM vmctco)*0.75)
    ) AS q_three_lower,
    (
    SELECT 576_VMC_Sol_Savings_Pct AS quartile_break
    FROM orderedList
    WHERE row_n = FLOOR((SELECT COUNT(*) FROM vmctco)*0.75) + 1
    ) AS q_three_upper,
    (
    SELECT 576_VMC_Sol_Savings_Pct AS quartile_break
    FROM orderedList
    WHERE row_n = FLOOR((SELECT COUNT(*) FROM vmctco)*0.25)
    ) AS q_one_lower,
    (
    SELECT 576_VMC_Sol_Savings_Pct AS quartile_break
    FROM orderedList
    WHERE row_n = FLOOR((SELECT COUNT(*) FROM vmctco)*0.25) + 1
    ) AS q_one_upper
    FROM orderedList
    ),

iqr AS (
SELECT
    576_VMC_Sol_Savings_Pct,
    (
    (SELECT MAX(q_three_lower)
        FROM quartile_breaks) +
    (SELECT MAX(q_three_upper)
        FROM quartile_breaks)
    )/2 AS q_three,
    (
    (SELECT MAX(q_one_lower)
        FROM quartile_breaks) +
    (SELECT MAX(q_one_upper)
        FROM quartile_breaks)
    )/2 AS q_one,
    1.5 * ((
    (SELECT MAX(q_three_lower)
        FROM quartile_breaks) +
    (SELECT MAX(q_three_upper)
        FROM quartile_breaks)
    )/2 - (
    (SELECT MAX(q_one_lower)
        FROM quartile_breaks) +
    (SELECT MAX(q_one_upper)
        FROM quartile_breaks)
    )/2) AS outlier_range
FROM quartile_breaks
)

SELECT MAX(q_one) OVER () - MAX(outlier_range) OVER () AS lower_limit,
    MAX(q_three) OVER () + MAX(outlier_range) OVER () AS upper_limit
INTO @lowlim, @upplim
FROM iqr
LIMIT 1;

SELECT @lowlim, @upplim;

P粉148782096276 天前452

执行特定查询时遇到连接中断

全部回复(1)我来回复