Bug #45777 check table doesn't show all problems for merge table compliance in 5.1

• Submitted: 26 Jun 2009 7:24
• Modified: 27 Jul 2011 17:26
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.1.37
• OS: Any
• CPU Architecture: Any
• Tags: regression

[26 Jun 2009 7:24] Shane Bester

Description:
In 5.0.84, check table on an incorrectly defined merge table set shows more details than in 5.1.37 (cut output to fit):

5.0.84:

mysql> select * from m1;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
mysql> check table m1;
+------------------------------------------------------------------------
| Msg_text
+------------------------------------------------------------------------
| Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
| Table 'test.t3' is differently defined or of non-MyISAM type or doesn't exist
| Unable to open underlying table which is differently defined or of non-MyISAM
| Corrupt
+------------------------------------------------------------------------

5.1.37 doesn't show all the problems:

mysql> select * from m1;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
mysql> check table m1;
+-------------------------------------------------------------------------
| Msg_text
+------------------------------------------------------------------------
| Table 'test.t2' doesn't exist
| Unable to open underlying table which is differently defined or of non-MyISAM
| Corrupt
+------------------------------------------------------------------------

The testcase purposely contains two problems:
1) t2 is missing
2) t3 is defined as memory table instead of myisam.

How to repeat:
flush tables;
drop table if exists m1,t1,t2,t3;
create table t1(id int)engine=myisam;
create table t3(id int)engine=memory;
create table m1(id int)engine=merge union=(t1,t2,t3);
select * from m1;
check table m1;

Suggested fix:
1) list each table that has problems in the CHECK TABLE output.
2) be specific on the problem:  Is it wrong engine, missing, lack of permissions, or differently defined ?

[26 Jun 2009 7:31] MySQL Verification Team

Then, if a base table is defined as non-myisam, the check table in 5.1.37 doesn't show *which* table is wrong, but 5.0.84 does show the table name:

flush tables;
drop table if exists m1,t1;
create table t1(id int)engine=memory;
create table m1(id int)engine=merge union=(t1);
check table m1;

[13 Nov 2009 17:17] MySQL Verification Team

see also bug #48756 which is a feature request to even extended the level of details provided by check table.

[27 Jul 2011 17:26] Paul DuBois

Noted in 5.5.16, 5.6.3 changelogs.

CHECK TABLE and REPAIR TABLE failed to find problems with a MERGE
table with underlying tables missing or with the wrong storage
engine. Issues were reported only for the first underlying table.