Anyone know how can I find the names of these stale temporary tables? It would be very useful for debugging...

We finally found a workaround to bug MDEV-17490 and managed to get a MariaDB 10.3.10 slave to replicate from our 10.2.18 master.

Then, we can confirm this bug is still there on 10.3.10. The slave keeps leaking temporary tables.

If yo need any more detail , please let me know

Confirming that _After stopping the server, also does not remove the files. _ The temporary files
are removed though "lazily" at the following server start. And I agree there's little sense of keeping them
in while they are destined to removal.

Andrei, I can confirm that the *.MY? files in our tmpdir are NOT removed at the following server start. As a matter of fact, files are named #sqlXXXX_YY_ZZZ.MY? where XXXX is the process PID number. What files would the following server remove at start? The new instance doesn't know the previous server PID..., and if deletes all temp files, maybe could be deleting temporary files of other MariaDB running instances.

Maybe this is another problem on the master, because it started after we have upgraded the master from MySQL 5.6 to MariaDB 10.2. I can confirm that previously (with MySQL 5.6 master) this problem doesn't existed. And could be somewhat related to the 17490 bug, because of misplaced "DROP TEMPORARY TABLES" statements in the binary log?

Finally, I agree that those files are destined to removal but should be removed as soon as posible as long as they are not needed any more for subsequent queries because the actual behavior ends up exhausting the operating system file descriptor limit for the MariaDB process and after that the only solution is thu shutdown server process.

Please let me know if I can help you debug this further

Kind regards

I suspect MDEV-15584 would make temporary files not appear on the filesystem in 10.4+

I agree with danblack that the temporary files wouldn't appear on the file system, though I don't necessarily know if that hides it away from the OS's open file descriptor statistics; or if the space of those tmp files files is ever reclaimed (while mariadbd is running). If danblack can answer that off the top of his head, we can close this; otherwise, there's still probably some cleanup work to do.

Seems i was wrong. The tmp files used by the replication thread which would create aria or myisam tables that neither use create_tenp_file or use the O_TMPFILE flag.

$ git grep create_temp_file storage/maria/ storage/myisam

$ git grep O_TMPFILE storage/maria/ storage/myisam

On the O_TMPFILES, its like opening a file and then having it removed, once the open file descriptor is close or the process is killed, the OS frees it up.

The problem description is the increasing of file descriptors which using O_TMPFILES won't solve, but it will cleanup nicely.

Added MDEV-35860 as an option to resolve the cleanup efforts with O_TMPFILE.

On the general leak that caused this problem I guess validating the code paths around the flags HA_CREATE_TMP_TABLE (SQL inplicit) HA_CREATE_GLOBAL_TMP_TABLE (replication thread SQL implicit).

As a replication statement to test create table t as (SELECT * FROM test) UNION (SELECT * FROM test);

serg I'm trying to repro the problem locally.

I couldn't build/compile 10.2.17 (using tag mariadb-10.2.17) on my Ubuntu 24.04.1 LTS. I got the following error,

-- Found GnuTLS: /usr/lib/x86_64-linux-gnu/libgnutls.so (found suitable version "3.8.3", minimum required is "3.3.24") 

-- TLS library/version: GnuTLS 3.8.3

-- SYSTEM_LIBS m;m;/usr/lib/x86_64-linux-gnu/libgnutls.so

-- Dynamic column API support: ON

-- GSSAPI: DYNAMIC

SYSTEM processor: x86_64

CMake Error at libmariadb/cmake/ConnectorName.cmake:30 (ENDMACRO):

  Flow control statements are not properly nested.

Call Stack (most recent call first):

  libmariadb/CMakeLists.txt:406 (INCLUDE)

So I tested on 10.2.44. I performed the following steps on a 1->2 replication setup,

--slave

SET GLOBAL default_tmp_storage_engine = 'MyISAM';

--master

SET GLOBAL binlog_format = MIXED;

--master

CREATE TABLE t1 (c1 TEXT, c2 TEXT);

INSERT INTO t1 VALUES (REPEAT('x', 1000), REPEAT('y', 1000));

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;

INSERT INTO t1 SELECT * FROM t1;

CREATE TABLE t2 AS (SELECT * FROM t1) UNION (SELECT * FROM t1);

--slave

10.2.44-opt>SHOW STATUS LIKE 'Slave_open_temp_tables';

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

| Variable_name          | Value |

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

| Slave_open_temp_tables | 0     |

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

1 row in set (0.00 sec)

I couldn't hit the problem with the above steps on 1->2 replication setup. I'm trying to perform the same on a single master and 4 slave setup.

If you want to check 10.2.17, you can try a release bintar. I don't know if it works on Ubuntu 24.04, but at least mariadb-10.2.15-linux-glibc_214-x86_64.tar.gz works on my Debian 12.

10.2 is EOL and the bug isn't present in any still supported version.