Bug #99382 Complex nested views take up a lot of memory
Submitted: 28 Apr 2020 7:34 Modified: 29 Apr 2020 7:55
Reporter: Fengchun Hua Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S1 (Critical)
Version:5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: out of memory

[28 Apr 2020 7:34] Fengchun Hua 
Description:
I have several complex views(see attachment). Even all of the table and view are empty, open these views takes up a lot of memory(5 tables and 7 views used up more than 11G memory). Some times leed to oom.

I tried debug with a debug-server, in sql_base.cc::open_tables, open_and_process_table seem called endless, table always has a next_global, and the same table name will repeated.

If not oom, memory will not return to system.

Test it on mysql8.0, seem alright on 8.0.

How to repeat:
1. create an empty database named test. 
2. Execute sql in attachment. 
3. logout and login.
4. select * from information_schema.tables.

Suggested fix:
No suggestion.
[28 Apr 2020 7:34] Fengchun Hua 
sql file

Attachment: sql.txt (text/plain), 43.85 KiB.
[28 Apr 2020 7:34] Fengchun Hua 
sql file

Attachment: sql.txt (text/plain), 43.85 KiB.
[29 Apr 2020 7:55] MySQL Verification Team 
Thanks for the report.  Verified on 5.7 and 8.0.20.  mysqld memory grew to 12GB which was unexpected.
[29 Apr 2020 8:06] MySQL Verification Team 
8.0.20 heap profile picture

Attachment: mybin.hprof.0383.heap.pdf (application/pdf, text), 18.47 KiB.
[29 Apr 2020 8:13] MySQL Verification Team 
Workaround is to avoid such complex queries that consume parser memory.
See:
  https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_parser_max_mem...

After that, memory is stable and you get errors instead:

ERROR 3170 (HY000): Memory capacity of 10000000 bytes for 'parser_max_mem_size' exceeded. Parser bailed out for this query.
ERROR 3170 (HY000): Memory capacity of 10000000 bytes for 'parser_max_mem_size' exceeded. Parser bailed out for this query.
ERROR 3170 (HY000): Memory capacity of 10000000 bytes for 'parser_max_mem_size' exceeded. Parser bailed out for this query.

So this bug might be about decreasing memory usage, if it is possible..
In mean time use workaround of lowering parser_max_mem_size