Bug #44626 Tables are reopened multiple times if used in same query, buffers allocated too

• Submitted: 3 May 2009 10:24
• Reporter: Domas Mituzas
• Status: Verified
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0, 5.1
• OS: Any
• CPU Architecture: Any

[3 May 2009 10:24] Domas Mituzas

Description:
In self joins, unions, etc - tables are re-opened for each mention.

If a UNION (or self-joins) have e.g. 1000 mentions of same table, then 1000 internal structures will be created, with additional file descriptors and buffers per each.

This leads to a simple 100k-sized query to allocate 500MB of memory just by mentioning same (even empty) table in it (e.g. SELECT * FROM table UNION SELECT * FROM table ...). 

Example of problematic repeated buffers:
a) InnoDB: upd_buff, key_val_buff
b) General: share->record
c) Trigger information (sp_head, etc)

How to repeat:
SELECT * FROM table ... UNION
SELECT * FROM table ... UNION
SELECT * FROM table ... UNION
SELECT * FROM table ... UNION
SELECT * FROM table ... UNION
SELECT * FROM table ... UNION
...
SELECT * FROM table ...

Suggested fix:
Reuse table objects (or buffers), especially once done dealing with a sub-SELECT in any query that is part of the union 

-- and/or --

Allow to limit amount of tables used per SELECT

[15 Jul 2010 13:27] MySQL Verification Team

Here's a testcase for the abuse of file descriptors when too many unions are allowed.  related: bug #50674

---------
drop table if exists t1;
create table t1(a blob)engine=myisam;
insert into t1 values ('a');
set @b='select a from t1';
set @a='explain extended ';
set @a=concat(@a,repeat(concat(@b,' union '),2100));
set @a=concat(@a,@b);
prepare stmt from @a;
execute stmt;
deallocate prepare stmt;
---------

on a server started with --open-files-limit=2048, it will consume all the available fd's, thus causing a major outage for all users.

mysql> prepare stmt from @a;
ERROR 23 (HY000): Out of resources when opening file '.\test\t1.MYD' (Errcode: 24)