DML on temporary table tries to append keys for provider

[Seppo Jaakola]

any update/delete/insert for temporary innodb table, will cause call for provider library append_key() call. The appended key values show as memory leak, and will be released only at server shutdown.

[Mark Rose (markrose)]

The bug is easy to reproduce. Here is a simplified version:

-- create source table and sample data
CREATE TABLE source (id BIGINT, data BIGINT, PRIMARY KEY(id, data), INDEX (data));
INSERT INTO source (id, data) VALUES (1, 1), (2, 2), (3, 2), (4, 2), (5, 3);

-- create temporary table and insert-select into it
CREATE TEMPORARY TABLE temp (id BIGINT, data BIGINT, PRIMARY KEY (id, data));
-- this leaks
INSERT INTO temp (id, data) SELECT id, data FROM source WHERE data = 2;

[Seppo Jaakola (seppo-jaakola)]

Fix was pushed in wsrep-23 in revision: http://bazaar.launchpad.net/~codership/codership-mysql/5.5-23/revision/3845
The fix avoids provider key append if affected table is temporary.

However, when testing with the fix build, the mysqld memory image keeps on growing after DML operations on temporary table, and these allocations do not seem to be released when the temporary table is dropped. This behavior is only with InnoDB temporary tables, MyISAM temporary tables behave as expected. It may be that InnoDB temporary table implementation has a memory leak problem, here is one potential bug tracker to follow: http://bugs.mysql.com/bug.php?id=67259

[Raghavendra D Prabhu (raghavendra-prabhu)]

@Seppo,

During this leak that you noticed with InnoDB temporary tables, did you see 'FLUSH TABLES' freeing any memory

I came across a similar bug here https://mariadb.atlassian.net/browse/MDEV-4016 where file handles were being leaked. Was this the case there as well?

[Raghavendra D Prabhu (raghavendra-prabhu)]

@Seppo,

 I have reported a separate XtraDB bug here - https://bugs.launchpad.net/percona-server/+bug/1124242 to investigate this from that angle.

[Alex Yurchenko (ayurchen)]

fix released in 23.7.3

[Raghavendra D Prabhu (raghavendra-prabhu)]

The fix for this issue is causing https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1240098 (check last few comments).