我在对 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;
已解决:我继续将 AWS 实例上的版本更新为与 NAS (8.0.28) 相同的版本,并且查询现在可以正确运行。