Bug #457 No error message on MERGE definition type mismatch
Submitted: 20 May 2003 6:30 Modified: 6 Feb 2007 12:06
Reporter: Carsten Pedersen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S3 (Non-critical)
Version:4.0.12,5.0.7-beta-debug OS:Microsoft Windows (WinNT,SuSE 9)
Assigned to: Sergey Vojtovich CPU Architecture:Any

[20 May 2003 6:30] Carsten Pedersen
Description:
When creating a MERGE table, a check should be performed that the table definiton matches that of the underlying tables (and that they all correspond to each other). An error message should be returned if not.

How to repeat:
mysql> create table t1 (c int);
Query OK, 0 rows affected (0.09 sec)

mysql> create table t2 (c int);
Query OK, 0 rows affected (0.02 sec)

/* Next line should produce error message */
mysql> create table t (c char(2)) type=merge union=(t1,t2);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
ERROR 1016: Can't open file: 't.MRG'. (errno: 143)

Suggested fix:
Have CREATE TABLE ... TYPE=MERGE go through all the underlying tables and check that the structure matches at creation time.
[25 May 2003 12:29] Meng Wang
Please changer http://www.mysql.com/doc/en/MERGE.html

Posted by Fred Stiening on Wednesday December 18 2002, @5:27pm  

In the example of 
ALTER TABLE table_name UNION(...)

it would be clearer to include the =
ALTER TABLE table_name UNION=(...) 

Posted date is 2002,12,18,now is 2003,05,26 @@"
[25 May 2003 12:30] Meng Wang
and use ALTER TABLE table_name UNION=(...) can't work too.
[30 Jun 2003 5:13] Sergei Golubchik
unrelated to the original report, manual typo - fixed.
[1 Jul 2003 5:30] Michael Widenius
The intention has always beeen that MERGE tables shouldn't check if the underlaying tables exists until the MERGE table is used.
(I agree that the error message is far from perfect and we shall change that ASAP)

A reason for this is that we don't want to check if a table is used by some MERGE table when doing a DROP table on it, as this would cause a lot of problems when doing backup/restore of tables.
[14 Aug 2006 16:31] Sergey Vojtovich
bug#9463 was marked as a duplicate of this bug.
[23 Aug 2006 16:04] Sergey Vojtovich
bug#16231 and bug#21617 were marked as duplicates of this bug.
[23 Aug 2006 17:10] Trudy Pelzer
When fixing this bug, please double-check
that this test case from bug#16231 doesn't
crash anymore:

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';
[24 Aug 2006 16:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10846

ChangeSet@1.2545, 2006-08-24 21:19:26+05:00, svoj@may.pils.ru +7 -0
  BUG#457 - No error message on MERGE definition type mismatch
  a. Fixed confusing error message from the storage engine when
     it fails to open underlying table. The error message is issued
     when a table is _opened_ (not when it is created).
  b. Fixed a crash when selecting from a merge table that has underlying
     tables with defferent index definition.
  c. (internal) Replaced annoying ifndef DBUG_OFF with DBUG_ASSERT in
     queue_insert() and queue_remove() functions.
[30 Aug 2006 14:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11077

ChangeSet@1.2545, 2006-08-30 19:41:25+05:00, svoj@may.pils.ru +8 -0
  BUG#457 - No error message on MERGE definition type mismatch
  a. Fixed confusing error message from the storage engine when
     it fails to open underlying table. The error message is issued
     when a table is _opened_ (not when it is created).
  b. Fixed a crash when selecting from a merge table that has underlying
     tables with defferent index definition.
  c. (internal) Replaced annoying ifndef DBUG_OFF with DBUG_ASSERT in
     queue_insert() and queue_remove() functions.
[19 Sep 2006 18:19] Sergey Vojtovich
To deliver fixes faster this problem will be split into three:
1. bug#10974 will be reopened to address confusing error message.
2. bug#21617 will be reopened to address a crash when there are less keys in underlying table than in merge table.
3. a check for underlying table structure conformance will be addressed as bug#457.
[21 Sep 2006 13:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12342

ChangeSet@1.2549, 2006-09-21 18:09:31+05:00, svoj@mysql.com +1 -0
  BUG#457 - No error message on MERGE definition type mismatch
  
  This is a preview patch. Will not be pushed.
[26 Sep 2006 23:57] Trudy Pelzer
When fixing this bug, please see related bug#22716.
Thanks.
[13 Oct 2006 12:32] Sergey Vojtovich
Per discussion with Serg closing this bug as To be fixed later. We will not add a check for underlying table structure, instead we will try to find other possible crashes/memory corruptions and report these as separate bugs.
[6 Feb 2007 12:06] Sergey Vojtovich
This bug is fixed in 4.1.23, 5.0.36, 5.1.15. No changelog entry needed,
since it will be documented as WL#3567. The following related bugs are also
fixed by the WL task: bug#457, bug#23750, bug#23751, bug#23752, bug#23753.