Bug #26976 Missing table in merge not noted in related error msg + SHOW CREATE TABLE fails
Submitted: 8 Mar 2007 23:54 Modified: 20 Jun 2007 0:52
Reporter: Arjen Lentz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S2 (Serious)
Version:5.1.14/5.0 OS:Any (any)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: bfsm_2007_03_15, merge, myisam

[8 Mar 2007 23:54] Arjen Lentz 
Description:
When a MERGE table is opened, server checks for existence of underlying tables.
The resulting error message is useless, since it does not specify *which* table is missing.

In addition, SHOW CREATE TABLE and SHOW TABLE STATUS on the MERGE table also fail, so you can't even look back at the definition to see where the problem might be.

5.0 in some cases reports the error differently (see http://bugs.mysql.com/22716) however the testcase below was performed on 5.1.14

How to repeat:
mysql> use test
Database changed
mysql> create table fm1 (i int) engine=merge union(nmt1);
Query OK, 0 rows affected (0.34 sec)

mysql> select * from fm1;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

mysql> show create table fm1;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

mysql> show table status like "fm1"\G
*************************** 1. row ***************************
           Name: fm1
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: Unable to open underlying table which is differently defined or of non-MyISAM ty

(the comment was truncated by the server, not me)

Suggested fix:
This bug is a side-effect of not fixing http://bugs.mysql.com/22716

While I can agree with Monty that it can -in theory- be useful to be able to define a merge table and then later create the underlying tables, the above behaviour makes it very difficult to manage and so it's pretty much useless.

So please either make it behave (make show table, show table status and other relevant commands work at least), or fix the underlying issue (22716).
Thanks
[9 Mar 2007 0:44] MySQL Verification Team 
Thank you for the bug report. Verified as described.
[12 Mar 2007 22:32] Arjen Lentz 
Sinisa reckons this one is effectively a *REGRESSION* since earlier known behaviour has deteriorated in a GA version.
[25 Apr 2007 12:11] 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/25393

ChangeSet@1.2443, 2007-04-25 16:14:43+05:00, svoj@mysql.com +6 -0
  BUG#26976 - Missing table in merge not noted in related error msg +
              SHOW CREATE TABLE fails
  Improved error message in case we fail to open underlying table.
  Now we report table name.
  No test case needed - error message covered by current tests.
[22 May 2007 8:58] 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/27114

ChangeSet@1.2443, 2007-05-22 13:00:50+05:00, svoj@mysql.com +6 -0
  BUG#26976 - Missing table in merge not noted in related error msg +
              SHOW CREATE TABLE fails
  Improved error message in case we fail to open underlying table.
  Now we report table name.
  No test case needed - error message covered by current tests.
[28 May 2007 13:22] 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/27465

ChangeSet@1.2500, 2007-05-28 17:25:17+05:00, svoj@mysql.com +10 -0
  BUG#26976 - Missing table in merge not noted in related error msg +
              SHOW CREATE TABLE fails
  
  Underlying table names, that merge engine fails to open were not
  reported.
  
  With this fix CHECK TABLE issued against merge table reports all
  underlying table names that it fails to open. Other statements
  are unaffected.
  
  This fix doesn't solve SHOW CREATE TABLE issue.
[1 Jun 2007 19:58] 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/27960

ChangeSet@1.2504, 2007-06-02 04:51:37+05:00, svoj@mysql.com +18 -0
  BUG#26976 - Missing table in merge not noted in related error msg +
              SHOW CREATE TABLE fails
  
  Underlying table names, that merge engine fails to open were not
  reported.
  
  With this fix CHECK TABLE issued against merge table reports all
  underlying table names that it fails to open. Other statements
  are unaffected, that is underlying table names are not included
  into error message.
  
  This fix doesn't solve SHOW CREATE TABLE issue.
[5 Jun 2007 19:52] 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/28149

ChangeSet@1.2504, 2007-06-06 04:42:41+05:00, svoj@mysql.com +20 -0
  BUG#26976 - Missing table in merge not noted in related error msg +
              SHOW CREATE TABLE fails
  
  Underlying table names, that merge engine fails to open were not
  reported.
  
  With this fix CHECK TABLE issued against merge table reports all
  underlying table names that it fails to open. Other statements
  are unaffected, that is underlying table names are not included
  into error message.
  
  This fix doesn't solve SHOW CREATE TABLE issue.
[8 Jun 2007 7:17] 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/28379

ChangeSet@1.2548, 2007-06-08 11:20:50+05:00, svoj@mysql.com +6 -0
  BUG#26976 - Missing table in merge not noted in related error msg +
              SHOW CREATE TABLE fails
  
  After merge fixes.
[14 Jun 2007 12:16] 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/28742

ChangeSet@1.2520, 2007-06-14 16:14:47+05:00, svoj@mysql.com +2 -0
  BUG#26976 - Missing table in merge not noted in related error msg +
              SHOW CREATE TABLE fails
  Addition to the fix: report db name + table name instead of table path.
  This solves embedded merge test failure.
[14 Jun 2007 12:16] 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/28743

ChangeSet@1.2520, 2007-06-14 16:18:01+05:00, svoj@mysql.com +2 -0
  BUG#26976 - Missing table in merge not noted in related error msg +
              SHOW CREATE TABLE fails
  Addition to the fix: report db name + table name instead of table path.
  This solves embedded merge test failure.
[18 Jun 2007 7:48] Bugs System 
Pushed into 5.1.20-beta
[18 Jun 2007 7:50] Bugs System 
Pushed into 5.0.44
[18 Jun 2007 19:36] Paul DuBois 
Noted in 5.0.44, 5.1.20 changelogs.

If a MERGE table cannot be opened or used because of a problem with
an underlying table, CHECK TABLE now displays information about which
table caused the problem.
[19 Jun 2007 10:26] Daniel Fischer 
Didn't make it into 5.0.44, will be in 5.0.46.
[20 Jun 2007 0:52] Paul DuBois 
Moved 5.0.44 changelog entry to 5.0.46.
[6 May 2009 20:22] Bugs System 
Pushed into 5.0.82 (revid:chad@mysql.com-20090506130632-s1cl4ygdj9rt2rrz) (version source revid:chad@mysql.com-20090506130632-s1cl4ygdj9rt2rrz) (merge vers: 5.0.82) (pib:6)
[28 May 2009 8:18] Bugs System 
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:jimw@mysql.com-20090515174051-ndjvfd1e9hc9k9c3) (merge vers: 5.1.36) (pib:6)
[17 Jun 2009 19:25] Bugs System 
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:joro@sun.com-20090515134506-5mq3a8fafgbkx6u1) (merge vers: 6.0.12-alpha) (pib:11)
[26 Aug 2009 13:46] Bugs System 
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System 
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System 
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:33] Bugs System 
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)