Any

eimix, thanks a lot for the report with the test case.

Reproducible as described on MariaDB 10.1-10.5 and MySQL 5.7 (I didn't try 5.6 or 8.0).
Not reproducible on MariaDB 5.5.
Same with InnoDB and MyISAM at least, so not engine-dependent.
I couldn't find right away an obvious reason for the memory allocation, hopefully deeper debugging will show more.

sanja (or whoever ends up debugging it), be careful with dSTAR64, it can indeed render your machine non-responsive, depending on how much memory you have. I think dSTAR16 is enough for debugging.

"Tested" on free hosting server for longer time.

Script took DB down for few hours (Jan 10th day uptime fall to 88%).
With "bad" intentions - it is possible to keep them down for loooong time.

Consider to make db structure atachements "not public".

It looks like table definition cache problem.

OK, I also checked 5.5 it also return end of resources error.

and it is kind of expected, because trigger update tables and uses function which update tables and triger triggers which... and so on.

So could someone explain me why such recursion should not eat all resources?

CURRENT_TEST: main.test
mysqltest: At line 697: query 'UPDATE jj001 SET vj001 = 0 WHERE 'TEJA' = 'FEJA'' failed: 23: Out of resources when opening file './dt/jj004.TRG' (Errcode: 24)

1. This is artificial test case - recursion is not needed to achieve high RAM usage
Do not know internals how these stored routines are parsed and cached,
It looks like every one stored procedure/function/trigger is "compiled" into executable code multiple times.

If procedure AAA is used in procedures BBB and CCC, memory for AAA procedure's code is allocated 3 times.
If add procedure BBB use into CCC - then it makes:
4pcs AAA (same SQL code but multiple "execution code)
2pcs BBB
1pcs CCC

2. On shared hosting servers - any user with similar script can "kill" server, and use resources without limit, and no way to configure limits.
Every "cache" (thread, connections, tables, open files etc) has configurable limits, but this one - does not.

MariaDB have to open and lock tables used in one query at once (including triggers) and each time table used it add the table to the list, so no surprise that opening all tables which TRIGGERS/functions needs require a lot of resources, what is wrong?

Also in my case memory quite controlled, it return EOM error I tested 5.5 10.1 and 10.5.

Maybe problem in allowing mysql using more resources than computer really has?

How this could be normal?
64 emty tables with triggers 10 rows each - can take 32GB RAM?

mysql uses ram accordingly to my.ini, and there is no docomented variable to control this.

OK, I got the problem (in my case it was limited by number of file). So the problem is that do not work one of memory limits (of we have such).

I have impression that SP/TRIGGERS here only allow easy open thouthands of tables for one query. if one make the same with one query it will be the same.

I can try to make query with subqueries and several thouthands of tables to open, but have not an idea how to make it automatically...

ah and difference with 5.5 is table cache overhead...

MySQL 5.6, 5.7, 8.0 has same problem,
8.0 has memory usage statistics: all memory is used by "memory/sql/sp_head::main_mem_root" (don't know if it could help)
They confirmed this problem too, but delisted bug from public list.

Last message was some debug info:

Leak of 6910660480 bytes in 151130 objects allocated from:
@ 00f82c18 unknown
@ 0000000000f7e8e3 alloc_root /export/home/pb2/build/sb_0-37309218-1576675069.34/mysqlcom-5.7.29/mysys/my_alloc.c:291
@ 0000000000d48b25 st_select_lex::add_table_to_list /export/home/pb2/build/sb_0-37309218-1576675069.34/mysqlcom-5.7.29/sql/sql_parse.cc:5901
@ 0000000000e53241 PT_table_factor_table_ident::contextualize /export/home/pb2/build/sb_0-37309218-1576675069.34/mysqlcom-5.7.29/sql/parse_tree_nodes.h:281
@ 0000000000e5496c PT_derived_table_list::contextualize /export/home/pb2/build/sb_0-37309218-1576675069.34/mysqlcom-5.7.29/sql/parse_tree_nodes.h:866
@ 0000000000e54943 PT_derived_table_list::contextualize /export/home/pb2/build/sb_0-37309218-1576675069.34/mysqlcom-5.7.29/sql/parse_tree_nodes.h:866
@ 0000000000e54943 PT_derived_table_list::contextualize /export/home/pb2/build/sb_0-37309218-1576675069.34/mysqlcom-5.7.29/sql/parse_tree_nodes.h:866
@ 0000000000e54943 PT_derived_table_list::contextualize /export/home/pb2/build/sb_0-37309218-1576675069.34/mysqlcom-5.7.29/sql/parse_tree_nodes.h:866
.....

just for record the script with 16 actually opens something between 1024 and 2048 files (not 16*3) the script with 64 opens more that 4096 files before been killed for too big memory allocation.

play with vlimit shows that script 16 uses something between 4 and 2 GB of memory (1-4MB per file so 2-3 times more for table)
3-12Mb per table (far from calculation in the bugreport, because incorrect calculation of tables used by query (16 and 64, real number should be expomnent of that number)

if ((parser= sql_parse_prepare(&path, &table->mem_root, 1))) 

it is how triggers parsed, so for each table triggers goes to table memory.

Statistic of usage is following:
5.5:
1.5GB-1.6GB and 1064-1024 files
10.1:
2.8GB-2.9GB and 2368-2304 files

so 1.1 has even less ration memory per file, the probem is in many files opened by 10.1

according to internal statistics server uses for execution the UPDATE:

5.5

Opened_files	2321-352 = 1969

Opened_table_definitions	16

Opened_tables	1057

Opened_views	0

10.1

Opened_files	2429-347 = 2082

Opened_plugin_libraries	0

Opened_table_definitions	16

Opened_tables	1025

Opened_views	0

10.1 statistics is dubious because it needs higher limit by opened files in OS then it reported in statistics

Expected this to be related to complexity/size of triggers, procedures and functions.
Tested adding large amount "dummy" code to them - memory consumtion does not increase (significantly).

almost 2 times more files opened and incorrect statistics by opened files it is problems caused by WSREP.

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.