MySQL Bugs: #16231: MERGE tables do not check index compliance between tables

Bug #16231	MERGE tables do not check index compliance between tables
Submitted:	5 Jan 2006 16:46	Modified:	23 Aug 2006 16:05
Reporter:	Mark Leith	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	All	OS:	Any (Any)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When using MERGE tables there is no checking on whether indexes or columns conform to the MERGE tables structure. Whilst this is documented behaviour it would be nice to have a server option, such as "full_merge_check", which states whether all CREATE or ALTER operations that are performed against tables that are within MERGE tables are fully checked for conformance.

This will then stop unnecessary instances of errors such as "Got error 124 from storage engine" (wrong/missing index).

How to repeat:
drop table t1;
drop table t2;
drop table merge_t;

create table t1 ( x int, y varchar(10), key (x), key (y) ) engine = MyISAM;
create table t2 ( x int, y varchar(10), key (x) ) engine = MyISAM;

create table merge_t ( x int, y varchar(10), key (x), key(y) ) 
engine = MERGE UNION (t1, t2) INSERT_METHOD LAST;

insert into t1 values (1, 'foo'), (2, 'bar');
insert into t2 values (3, 'baz'), (4, 'waz');

select * from merge_t where y = 'foo';

Suggested fix:
Add a new server variable that enforces checking for MERGE tables for all table maintenance operations

Testcase crashed my mysql-max-5.0.24-linux-i686-glibc23.

(gdb) bt
#0  0x0841dce5 in queue_insert ()
#1  0x0841019d in myrg_rkey ()
#2  0x08247c83 in ha_myisammrg::index_read ()
#3  0x081f81c9 in join_read_always_key ()
#4  0x081ea427 in sub_select ()
#5  0x081f02ac in do_select ()
#6  0x081e62f5 in JOIN::exec ()
#7  0x081e6e35 in mysql_select ()
#8  0x081e2e16 in handle_select ()
#9  0x081b79fe in mysql_execute_command ()
#10 0x081be912 in mysql_parse ()
#11 0x081b6296 in dispatch_command ()
#12 0x081b5dbd in do_command ()
#13 0x081b54bc in handle_one_connection ()
#14 0x0045fbd4 in start_thread () from /lib/libpthread.so.0
#15 0x003b74fe in clone () from /lib/libc.so.6
(gdb)

This bug is a duplicate of bug#457.