[MDEV-17066] Bytes lost or Assertion `status_var.local_memory_used == 0 after DELETE with subquery with ROLLUP Created: 2018-08-24  Updated: 2020-10-06  Resolved: 2020-08-05

Status: Closed
Project: MariaDB Server
Component/s: Server, Storage Engine - InnoDB
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.2.35, 10.3.26, 10.4.16, 10.5.7

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
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.

 Comments   
Comment by Marko Mäkelä [ 2019-03-26 ]

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

Comment by Alexander Barkov [ 2019-08-30 ]

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

Comment by Varun Gupta (Inactive) [ 2020-07-21 ] 

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

Comment by Varun Gupta (Inactive) [ 2020-07-22 ]

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.

Comment by Oleksandr Byelkin [ 2020-07-27 ]

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

Generated at Thu Feb 08 08:33:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.