Query Execution Directly Causes OOM

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 执行查询 直接oom

| username: TiDBer_yyy

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] Execute SQL, directly OOM
Every execution results in OOM
SQL:

SELECT tt.TABLE_SCHEMA, tt.TABLE_NAME, tt.CONSTRAINT_NAME, CONCAT_WS('\n', Database, Table, Condition, Ignored Fields, Unique, Task Name, Chinese Identifier) conflunce, COLUMN_NAME
FROM (
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, tc.CONSTRAINT_NAME,
concat('Database:', t.TABLE_SCHEMA) Database, # Database: Database Name
concat('Table:', t.TABLE_NAME) Table, # Table: Table Name
# where condition: where condition to validate data from the last 2 months
if(dt.COLUMN_NAME IS null, '', concat('where ( ', dt.COLUMN_NAME, " >= CONVERT(concat(date_format(subdate(now(), INTERVAL 1 month), '%Y%m'), '01'), unsigned) AND", dt.COLUMN_NAME, " <= CONVERT(date_format(subdate(now(), INTERVAL 1 day), '%Y%m%d'), unsigned)) OR ( ", dt.COLUMN_NAME, ">= CONVERT(date_format(subdate(now(), INTERVAL 1 month), '%Y%m'), unsigned)  AND ", dt.COLUMN_NAME, "<= CONVERT(date_format(subdate(now(), INTERVAL 1 day), '%Y%m%d'), unsigned)) OR ( ", dt.COLUMN_NAME, " >= CONVERT(date_format(subdate(now(), INTERVAL 1 day), '%Y'), unsigned))")) Condition,
# Ignored Fields: i_id, d_created_at, d_updated_at
CONCAT_WS('、', 'Ignored Fields: i_id, d_created_at, d_updated_at', GROUP_CONCAT(c.COLUMN_NAME SEPARATOR '、')) Ignored Fields,
concat('Unique Key Column Name:', GROUP_CONCAT(cu.COLUMN_NAME)) Unique, # Unique Key Column Name: ch_batch_no
concat('Task Name: prod_datacenter_', CONCAT_WS('_', t.TABLE_SCHEMA, t.TABLE_NAME, tc.CONSTRAINT_NAME)) Task Name, # Task Name: Task English Name, suggested format prod_datacenter_{task_name}_xxx, Task Name must be unique t.TABLE_SCHEMA, t.TABLE_NAME
concat('Task Chinese Identifier:', CONCAT_WS('_', t.TABLE_COMMENT, t.TABLE_SCHEMA, t.TABLE_NAME, tc.CONSTRAINT_NAME)) Chinese Identifier # Task Chinese Identifier: Chinese description of the task name, used for WeChat notifications t.TABLE_COMMENT, t.TABLE_NAME
,dt.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
ON t.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND t.TABLE_NAME = tc.TABLE_NAME
AND tc.CONSTRAINT_TYPE = 'UNIQUE'
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
ON tc.TABLE_SCHEMA = cu.TABLE_SCHEMA
AND tc.TABLE_NAME = cu.TABLE_NAME
AND tc.CONSTRAINT_SCHEMA = cu.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c 
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
AND c.DATA_TYPE in ('mediumblob','blob','longblob','json')
LEFT JOIN (
SELECT * 
FROM (
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME, rank() over(PARTITION BY a.TABLE_SCHEMA, a.TABLE_NAME ORDER BY FIELD(a.COLUMN_NAME, 'I_DATE', 'I_MONTH', 'I_YEAR')) r
FROM INFORMATION_SCHEMA.COLUMNS a 
WHERE a.COLUMN_NAME IN ('I_DATE', 'I_MONTH', 'I_YEAR')
) t
WHERE r = 1
) dt
ON t.TABLE_SCHEMA = dt.TABLE_SCHEMA
AND t.TABLE_NAME = dt.TABLE_NAME
WHERE t.TABLE_SCHEMA IN ('db1', 'db2', 'db3')
AND dt.COLUMN_NAME IS NOT null
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME, tc.CONSTRAINT_NAME
) tt;

[Resource Configuration]
Related Error

[2023/07/27 16:40:29.442 +08:00] [INFO] [printer.go:33] ["Welcome to TiDB."] ["Release Version"=v5.0.4] [Edition=Community] ["Git Commit Hash"=53251a9731da02ad9ee5abed9f27a14c7dea33a4] ["Git Branch"=heads/refs/tags/v5.0.4] ["UTC Build Time"="2021-09-14 08:56:02"] [GoVersion=go1.13] ["Race Enabled"=false] ["Check Table Before Drop"=false] ["TiKV Min Version"=v3.0.0-60965b006877ca7234adaced7890d7b029ed1306]
[2023/07/27 16:40:29.442 +08:00] [INFO] [trackerRecorder.go:28] ["Mem Profile Tracker started"]
| username: tidb菜鸟一只 | Original post link

Is it only this SQL that reports OOM or does any executed SQL report it? Also, I didn’t see any OOM-related information in your error message.

| username: TiDBer_yyy | Original post link

Other queries are normal.

This SQL causes an OOM (Out of Memory) error every time it is executed.

| username: zhanggame1 | Original post link

Please share the execution plan. Also, did you change the default 1G OOM parameter?

| username: tidb菜鸟一只 | Original post link

It doesn’t seem to be an OOM issue. It looks like this SQL triggered a bug, causing TiDB to restart immediately upon execution. However, when I executed it a second time, it didn’t restart…

| username: TiDBer_yyy | Original post link

I’ve encountered OOM 8-9 times in a row.

| username: TiDBer_yyy | Original post link

64G kill, no OOM limit

| username: TiDBer_yyy | Original post link

Do you have any related bug descriptions?

In actual testing, version 7.1.0 has no issues.

| username: TiDB_C罗 | Original post link

Can you view the execution plan? Or try breaking this statement into several parts and executing them separately.

| username: Jellybean | Original post link

If you confirm it is tidb-server, post the execution plan of this SQL. First, analyze whether the issue is caused by the execution plan leading to a large amount of data access.

| username: redgame | Original post link

Out of memory…