v5.0.0 EXPLAIN SQL指令後發生 ERROR 2006 (HY000): MySQL server has gone away & Error Code: 1105. runtime error: index out of range [-1]

【TiDB 版本】: 5.0.0

【问题描述】:
在v5.0.0集群 嘗試EXPLAIN以下SQL語句時,導致ERROR 2006 (HY000): MySQL server has gone away
最後出現Error Code: 1105. runtime error: index out of range [-1]
但在v4.0.12版本是可以正常返回Resultset

SELECT
pt.code,
pt.name,
IFNULL(SUM(tmp.order_xxx_count), 0) AS order_xxx_count,
CASE
WHEN tmp.product_type IS NULL THEN 0
ELSE SUM(tmp.point)
END AS point,
CASE
WHEN tmp.product_type IS NULL THEN 0
ELSE SUM(total)
END AS total,
CASE
WHEN tmp.product_type IS NULL THEN 0
ELSE (SUM(total) - SUM(earning))
END AS earning
FROM
(SELECT
code, name, channel_code
FROM
product) AS pt
LEFT JOIN
(SELECT
channel_code,
product_type,
memxxx_login,
COUNT(1) order_xxx_count,
SUM(point) point,
SUM(total) total,
SUM(earning) earning
FROM
order_xxx
LEFT JOIN view_xxxxxxxx ON view_xxxxxxxx.member = order_xxx.memxxx_login
WHERE
1 = 1 AND order_xxx.status = 1
AND order_xxx.payout = 1
AND (IFNULL(“2021-03-01 04:00”, 1) = 1 OR order_xxx.payout_time >= “2021-03-01 04:00”)
AND (IFNULL(“2021-04-01 04:00”, 1) = 1 OR order_xxx.payout_time < “2021-04-01 04:00”)
AND (IFNULL(NULL, 1) = 1 OR order_xxx.memxxx_login = NULL)
AND (IFNULL(NULL, 1) = 1 OR FIND_IN_SET(view_xxxxxxxx.shxxx_login, NULL))
AND (IFNULL(NULL, 1) = 1 OR view_xxxxxxxx.general_agent IS NULL)
AND (IFNULL(NULL, 1) = 1 OR view_xxxxxxxx.agent IS NULL)
GROUP BY memxxx_login , product_type , channel_code) AS tmp ON tmp.product_type = pt.code
AND tmp.channel_code = pt.channel_code
WHERE
(IFNULL(NULL, 1) = 1 OR pt.code = NULL)
AND (IFNULL(“abc123”, 1) = 1 OR pt.channel_code = “abc123”)
GROUP BY pt.code , pt.channel_code;


麻烦提供下 SQL 执行计划以及报错时的 tidb 日志。

顧問好,

Execution Plan(v5.0.0) - 無法顯示 且會出現ERROR 2006 (HY000): MySQL server has gone away

Execution Plan(v4.0.12) :

tidb.log
tidb.log (406.7 KB)

从 tidb 日志中查看这个集群是从 v4.0.11 升级到 v4.0.12 然后再升级到 v5.0.0,在升级之前这个查询都是正常的吗?另外升级时有调整什么参数吗

提供一下現在edit-config內容給顧問:
gcp_tidb_edit_config (4.3 KB)

再請顧問幫忙查核一下 是否有參數需要調整?

好的,我们这边先分析下

这个问题刚才排查了是一个已知的问题:https://github.com/pingcap/tidb/pull/23844;
临时的解决方法是把 sql_mode 中的 only_full_group_by 去掉,修复的话还需要一定时间 :sweat_smile:

1赞

感謝顧問的解惑 我們先調整一下sql_mode
後續還是要針對SQL調整 畢竟這樣比較不嚴謹。

好的,如果 SQL 太复杂的话可以考虑拆分下功能,这样应该也能绕过这个问题。