Bug #33986 Cannot ALTER a MERGE table that is differently defined from its children
Submitted: 22 Jan 2008 17:49
Reporter: Ingo Strüwing Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S2 (Serious)
Version:5.0, 5.1, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: alter, merge
Triage: Triaged: D3 (Medium) / R4 (High) / E3 (Medium)

[22 Jan 2008 17:49] Ingo Strüwing
Description:
If a MERGE table is differently defined from its children, one must either DROP and recreate it or alter all children to match the parent, then alter the parent to what it should be and alter all children to the new definition.

How to repeat:
CREATE TABLE t1 (c1 INT NOT NULL) ENGINE=MYISAM;
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
INSERT INTO t1 VALUES (1);
SELECT * FROM m1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
ALTER TABLE m1 MODIFY c1 INT NOT NULL;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
ALTER TABLE t1 MODIFY c1 INT;
ALTER TABLE m1 MODIFY c1 INT NOT NULL;
ALTER TABLE t1 MODIFY c1 INT NOT NULL;
SELECT * FROM m1;
c1
1