執行特定查詢時遇到連線中斷

Question

我在 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;

Answer 1

已解決：我繼續將 AWS 實例上的版本更新為與 NAS (8.0.28) 相同的版本，並且查詢現在可以正確運行。