Bug #41217 SELECT from MERGE table which unions many tables gives wrong error

• Submitted: 4 Dec 2008 8:39
• Modified: 4 Dec 2008 17:30
• Reporter: Sveta Smirnova
• Status: Verified
• Category: MySQL Server: Merge storage engine
• Severity: S3 (Non-critical)
• Version: 4.1, 5.0, 5.1, 6.0 bzr
• OS: Any
• CPU Architecture: Any

[4 Dec 2008 8:39] Sveta Smirnova

Description:
If I create merge table which unions many others (quantity near open_files_limit), then select from it I get error "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist" instead one from which I clearly can understand problem exists, because too many files needed to be open.

Looks similar tp bug #26881, thought they are not same.

How to repeat:
Test case will follow.

[4 Dec 2008 17:30] Sveta Smirnova

Test case:

show variables like 'open_files_limit';

--disable_query_log

create table names(f1 char(10));

let $i = 250;
while ($i)
{
--eval CREATE TABLE t$i (   f1 int )
--eval insert into names values('t$i')
dec $i;
}

--let $union1=`select group_concat(f1) from names`

truncate names;
let $i = 250;
while ($i)
{
--eval CREATE TABLE tt$i (   f1 int )
--eval insert into names values('tt$i')
dec $i;
}

--let $union2=`select group_concat(f1) from names`

truncate names;
let $i = 250;
while ($i)
{
--eval CREATE TABLE ttt$i (   f1 int )
--eval insert into names values('ttt$i')
dec $i;
}

--let $union3=`select group_concat(f1) from names`

truncate names;

--let $union4=`select group_concat(f1) from names`

--eval CREATE TABLE m1 (   f1 int ) engine=MRG_MyISAM union=($union1, $union2, $union3)
--enable_query_log

select * from m1;

Results depends from value of open_files_limit. In my case it was 1024. If it is larger on your system either add option file or increase $unionN.