[MDEV-5582] Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 5.3.12, 5.5.35, 10.0.7
Fix Version/s: 5.5.36, 10.0.9, 5.3.13
Component/s: None
Labels:
None

Description

SET optimizer_switch = 'materialization=on,semijoin=on';

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (8),(9);

CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (2),(3);

CREATE TABLE t3 (c INT, INDEX(c));
INSERT INTO t2 VALUES (4),(5);

SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );

In 5.3 and 5.5, it produces the error on shutdown:

140128 19:10:55 [Warning] Plugin 'MEMORY' will be forced to shutdown
140128 19:10:55 [ERROR] Plugin 'MEMORY' has ref_count=1 after shutdown.

In 10.0, it causes assertion failure:

sql/sql_class.cc:1634: virtual THD::~THD(): Assertion `status_var.memory_used == 0' failed.
140128 19:13:28 [ERROR] mysqld got signal 6 ;

#6  0x00007f4e893ea621 in *__GI___assert_fail (assertion=0xfd028f "status_var.memory_used == 0", file=<optimized out>, line=1634, function=0xfd2850 "virtual THD::~THD()") at assert.c:81
#7  0x00000000006295a2 in THD::~THD (this=0x7f4e8333d070, __in_chrg=<optimized out>) at 10.0/sql/sql_class.cc:1634
#8  0x000000000062972a in THD::~THD (this=0x7f4e8333d070, __in_chrg=<optimized out>) at 10.0/sql/sql_class.cc:1639
#9  0x00000000005ab8f2 in unlink_thd (thd=0x7f4e8333d070) at 10.0/sql/mysqld.cc:2715
#10 0x00000000005abc52 in one_thread_per_connection_end (thd=0x7f4e8333d070, put_in_cache=true) at 10.0/sql/mysqld.cc:2826
#11 0x000000000077a563 in do_handle_one_connection (thd_arg=0x7f4e8333d070) at 10.0/sql/sql_connect.cc:1390
#12 0x000000000077a210 in handle_one_connection (arg=0x7f4e8333d070) at 10.0/sql/sql_connect.cc:1293
#13 0x0000000000a9d1f1 in pfs_spawn_thread (arg=0x7f4e83b75df0) at 10.0/storage/perfschema/pfs.cc:1853
#14 0x00007f4e8a94ab50 in start_thread (arg=<optimized out>) at pthread_create.c:304
#15 0x00007f4e89499a7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Stack trace from:

revision-id: psergey@askmonty.org-20140121100700-9g8hl8vx8nmk1e2w
revno: 3965
branch-nick: 10.0

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Sergei Petrunia added a comment - 2014-02-07 15:09

The leak is because materialization table for non-merged semi-join is created and not freed.

The query plan is:
mysql> explain SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
----------------------------------------------------------------------------------+

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

----------------------------------------------------------------------------------+

1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
3	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row

----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Show

Sergei Petrunia added a comment - 2014-02-07 15:09 The leak is because materialization table for non-merged semi-join is created and not freed. The query plan is: mysql> explain SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); ----- ------------ ----- ---- ------------- ---- ------- ---- ---- ------------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ------------ ----- ---- ------------- ---- ------- ---- ---- ------------------------+ 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL No matching min/max row ----- ------------ ----- ---- ------------- ---- ------- ---- ---- ------------------------+ 2 rows in set (0.00 sec)

Hide

Permalink

Sergei Petrunia added a comment - 2014-02-07 16:06

The following happens:

> JOIN::optimize()

convert_join_subqueries_to_semijoins()

3rd-level subquery is converted to non-merged semi-join
2nd-level subquery is converted into a semijoin.

setup_jtbm_semi_joins()

finds that subquery with id=3 is degenerate, still creates a dummy temporary
table $T for it.

constant table code finds that non-merged semi-join is an empty
constant table, based on that, runs:
tables=NULL;
goto setup_subq_exit;

<JOIN::optimize()

>JOIN::cleanup(full=true)
if (table)

{ ... }

^^ the branch is not taken, bevause JOIN::table=NULL (and
JOIN::join_tab=NULL, too).

As a consequence, the temp.table is never freed.

Show

Sergei Petrunia added a comment - 2014-02-07 16:06 The following happens: > JOIN::optimize() convert_join_subqueries_to_semijoins() 3rd-level subquery is converted to non-merged semi-join 2nd-level subquery is converted into a semijoin. setup_jtbm_semi_joins() finds that subquery with id=3 is degenerate, still creates a dummy temporary table $T for it. constant table code finds that non-merged semi-join is an empty constant table, based on that, runs: tables=NULL; goto setup_subq_exit; <JOIN::optimize() >JOIN::cleanup(full=true) if (table) { ... } ^^ the branch is not taken, bevause JOIN::table=NULL (and JOIN::join_tab=NULL, too). As a consequence, the temp.table is never freed.

Hide

Permalink

Sergei Petrunia added a comment - 2014-02-07 16:29

Upon closer examination: JOIN::cleanup( full=true) actually does call cleanup_empty_jtbm_semi_joins(JOIN *join). The problem is that that function doesn't recurse down into semi-join nests.

Show

Sergei Petrunia added a comment - 2014-02-07 16:29 Upon closer examination: JOIN::cleanup( full=true) actually does call cleanup_empty_jtbm_semi_joins(JOIN *join). The problem is that that function doesn't recurse down into semi-join nests.

Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile