[MDEV-17066] Bytes lost or Assertion `status_var.local_memory_used == 0 after DELETE with subquery with ROLLUP - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.2.35, 10.3.26, 10.4.16, 10.5.7
Component/s: Server, Storage Engine - InnoDB
Labels:
None

Description

--source include/have_innodb.inc

CREATE TABLE t1 (i INT DEFAULT 0, c VARCHAR(8192) DEFAULT '') ENGINE=InnoDB;

INSERT INTO t1 () VALUES (),(),(),(),();

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

CREATE TABLE t2 (f VARCHAR(8192) DEFAULT '') ENGINE=InnoDB;

INSERT INTO t2 VALUES ('foo'),('bar');

DELETE FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c WITH ROLLUP );

# Cleanup

DROP TABLE t1, t2;

5.5 064ba8cc9f
180824 14:55:09 [Note] /data/bld/5.5/bin/mysqld: Shutdown complete

Warning: 254542 bytes lost, allocated at 0x8d5520, 0x8d487d, 0x65bb3c, 0x659ae0, 0x6593ae, 0x637726, 0x84c06f, 0x8445da
Memory lost: 254542 bytes in 1 chunks

10.0 bcc677bb7
Warning: 254544 bytes lost at 0x7f8735d06070, allocated by T@3 at 0x972530, 0x971885, 0x6afcc5, 0x6adc0d, 0x6ad4e2, 0x68a141, 0x6875fe, 0x8e2875
Memory lost: 254544 bytes in 2823 chunks
mysqld: /data/src/10.0/sql/sql_class.cc:1547: virtual THD::~THD(): Assertion `status_var.memory_used == 0' failed.
180824 14:57:14 [ERROR] mysqld got signal 6 ;

#7 0x00007f8749f20ee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
#8 0x0000000000610b00 in THD::~THD (this=0x7f873eb3d070, __in_chrg=<optimized out>) at /data/src/10.0/sql/sql_class.cc:1547
#9 0x0000000000610c98 in THD::~THD (this=0x7f873eb3d070, __in_chrg=<optimized out>) at /data/src/10.0/sql/sql_class.cc:1552
#10 0x000000000058f922 in unlink_thd (thd=0x7f873eb3d070) at /data/src/10.0/sql/mysqld.cc:2705
#11 0x000000000058fc78 in one_thread_per_connection_end (thd=0x7f873eb3d070, put_in_cache=true) at /data/src/10.0/sql/mysqld.cc:2816
#12 0x000000000076ad7e in do_handle_one_connection (thd_arg=0x7f873eb3d070) at /data/src/10.0/sql/sql_connect.cc:1388
#13 0x000000000076aa4e in handle_one_connection (arg=0x7f873eb3d070) at /data/src/10.0/sql/sql_connect.cc:1292
#14 0x0000000000accef0 in pfs_spawn_thread (arg=0x7f873ea801f0) at /data/src/10.0/storage/perfschema/pfs.cc:1861
#15 0x00007f874bc24494 in start_thread (arg=0x7f874bfe7700) at pthread_create.c:333
#16 0x00007f8749fdd93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

10.4 5abc79dd7ab
***Warnings generated in error logs during shutdown after running tests: bug.t8a

mysqld: /data/src/10.4/sql/sql_class.cc:1662: virtual THD::~THD(): Assertion `status_var.local_memory_used == 0 \|\| !debug_assert_on_not_freed_memory' failed.
Attempting backtrace. You can use the following information to find out

The effect seems to be sporadic, sometimes the failure doesn't happen. Run several times if it doesn't fail right away.

Attachments

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2018-08-24 11:59

Marko Mäkelä added a comment - 2019-03-26 16:03

The bug ought to be outside InnoDB. InnoDB should not be allocating any memory via that interface.

Marko Mäkelä added a comment - 2019-03-26 16:03 The bug ought to be outside InnoDB. InnoDB should not be allocating any memory via that interface.

Marko Mäkelä made changes - 2019-03-26 16:03

Field	Original Value	New Value
Assignee	Marko Mäkelä [ marko ]	Alexander Barkov [ bar ]

Alexander Barkov made changes - 2019-07-05 02:47

Priority

Major [ 3 ]

Critical [ 2 ]

Alexander Barkov added a comment - 2019-08-30 13:21 - edited

Also repeatable with MyISAM if I put this code into a new *.test file (not repeatable from command line):

CREATE TABLE t1 (i INT DEFAULT 0, c VARCHAR(8192) DEFAULT '') ENGINE=MyISAM;

INSERT INTO t1 () VALUES (),(),(),(),();

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

SELECT COUNT(*) FROM t1;

CREATE TABLE t2 (f VARCHAR(8192) DEFAULT '') ENGINE=MyISAM;

INSERT INTO t2 VALUES ('foo'),('bar');

DELETE FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c WITH ROLLUP );

# Cleanup

DROP TABLE t1, t2;

gdb shows this amount of memory used at the end:

(gdb) p status_var.local_memory_used

$1 = 254544

Alexander Barkov added a comment - 2019-08-30 13:21 - edited Also repeatable with MyISAM if I put this code into a new *.test file (not repeatable from command line): CREATE TABLE t1 (i INT DEFAULT 0, c VARCHAR (8192) DEFAULT '' ) ENGINE=MyISAM; INSERT INTO t1 () VALUES (),(),(),(),(); INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; SELECT COUNT (*) FROM t1; CREATE TABLE t2 (f VARCHAR (8192) DEFAULT '' ) ENGINE=MyISAM; INSERT INTO t2 VALUES ( 'foo' ),( 'bar' ); DELETE FROM t2 WHERE f IN ( SELECT MAX (c) FROM t1 GROUP BY c WITH ROLLUP ); # Cleanup DROP TABLE t1, t2; gdb shows this amount of memory used at the end: (gdb) p status_var.local_memory_used $1 = 254544

Alexander Barkov made changes - 2019-08-30 13:51

Assignee

Alexander Barkov [ bar ]

Oleksandr Byelkin [ sanja ]

Oleksandr Byelkin made changes - 2019-11-06 11:54

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2020-07-21 08:47

Assignee

Oleksandr Byelkin [ sanja ]

Varun Gupta [ varun ]

Varun Gupta (Inactive) added a comment - 2020-07-21 12:32 - edited

CREATE TABLE t1 (i INT DEFAULT 0, c VARCHAR(8192));

INSERT INTO t1  SELECT 0, 'a' FROM seq_1_to_1280;

CREATE TABLE t2 (f VARCHAR(8192) DEFAULT '');

INSERT INTO t2 VALUES ('foo'),('bar');

EXPLAIN

SELECT * FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c ORDER BY GROUP_CONCAT(i));

SELECT * FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c ORDER BY GROUP_CONCAT(i));

DROP TABLE t1, t2;

Test case with SELECT fails without ROLLUP

Varun Gupta (Inactive) added a comment - 2020-07-21 12:32 - edited CREATE TABLE t1 (i INT DEFAULT 0, c VARCHAR (8192)); INSERT INTO t1 SELECT 0, 'a' FROM seq_1_to_1280; CREATE TABLE t2 (f VARCHAR (8192) DEFAULT '' ); INSERT INTO t2 VALUES ( 'foo' ),( 'bar' ); EXPLAIN SELECT * FROM t2 WHERE f IN ( SELECT MAX (c) FROM t1 GROUP BY c ORDER BY GROUP_CONCAT(i)); SELECT * FROM t2 WHERE f IN ( SELECT MAX (c) FROM t1 GROUP BY c ORDER BY GROUP_CONCAT(i)); DROP TABLE t1, t2; Test case with SELECT fails without ROLLUP

Varun Gupta (Inactive) added a comment - 2020-07-22 10:13

The test case with innodb is not deterministic because the stats.records is used while deciding if we want to use a cache to read the records from the temp table. So the test case above with MYISAM fails reliably.

Also the problem here was that the cache used to read the records from the temp table (rr_from_cache) was allocated at each execution of the subquery but was only deallocated after the last execution of the subquery.

Varun Gupta (Inactive) added a comment - 2020-07-22 10:13 The test case with innodb is not deterministic because the stats.records is used while deciding if we want to use a cache to read the records from the temp table. So the test case above with MYISAM fails reliably. Also the problem here was that the cache used to read the records from the temp table (rr_from_cache) was allocated at each execution of the subquery but was only deallocated after the last execution of the subquery.

Varun Gupta (Inactive) made changes - 2020-07-22 10:33

Assignee	Varun Gupta [ varun ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Oleksandr Byelkin added a comment - 2020-07-27 13:50

https://github.com/MariaDB/server/commit/65cd129f1f7bf4a14ebe5c22a89646e986af99d9 OK to push. Thank you!

Oleksandr Byelkin added a comment - 2020-07-27 13:50 https://github.com/MariaDB/server/commit/65cd129f1f7bf4a14ebe5c22a89646e986af99d9 OK to push. Thank you!

Oleksandr Byelkin made changes - 2020-08-05 06:01

Assignee	Oleksandr Byelkin [ sanja ]	Varun Gupta [ varun ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Varun Gupta (Inactive) made changes - 2020-08-05 06:20

Fix Version/s		10.2.33 [ 24307 ]
Fix Version/s		10.3.24 [ 24306 ]
Fix Version/s		10.4.14 [ 24305 ]
Fix Version/s		10.5.5 [ 24423 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Daniel Bartholomew made changes - 2020-08-10 19:57

Fix Version/s		10.2.34 [ 24505 ]
Fix Version/s		10.3.25 [ 24506 ]
Fix Version/s		10.4.15 [ 24507 ]
Fix Version/s		10.5.6 [ 24508 ]
Fix Version/s	10.4.14 [ 24305 ]
Fix Version/s	10.3.24 [ 24306 ]
Fix Version/s	10.2.33 [ 24307 ]
Fix Version/s	10.5.5 [ 24423 ]

Ralf Gebhardt made changes - 2020-10-06 18:35

Fix Version/s

10.5.7 [ 25019 ]

Ralf Gebhardt made changes - 2020-10-06 18:38

Fix Version/s

10.5.6 [ 24508 ]

Ralf Gebhardt made changes - 2020-10-06 18:40

Fix Version/s

10.4.16 [ 25020 ]

Ralf Gebhardt made changes - 2020-10-06 18:42

Fix Version/s

10.4.15 [ 24507 ]

Ralf Gebhardt made changes - 2020-10-06 18:44

Fix Version/s

10.3.26 [ 25021 ]

Ralf Gebhardt made changes - 2020-10-06 18:45

Fix Version/s

10.3.25 [ 24506 ]

Ralf Gebhardt made changes - 2020-10-06 18:47

Fix Version/s

10.2.35 [ 25022 ]

Ralf Gebhardt made changes - 2020-10-06 18:48

Fix Version/s

10.2.34 [ 24505 ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 89092 ]

MariaDB v4 [ 154855 ]

People

Assignee:: Varun Gupta (Inactive)

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2018-08-24 11:59

Updated:: 2020-10-06 18:48

Resolved:: 2020-08-05 06:20

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration